Solved Access 2010 exporting

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

stusher

Thread Starter
Joined
Jul 6, 2016
Messages
9
I created a database that runs 6 separate macros, and those macros are 2 different platforms. Platform A runs first creates a folder with the yyyymm then yyyymmdd and then within that there is a table that exports into it as excel. The Platform A folder creation and export are both RunCode options in a macro. I am trying to get platform B to export into that made folder, however I cannot get the code or a macro to complete with the process. This is the code I use to get the export to work when I am within the module, however this code does not work as RunCode in the macro because the folder is already created. I don't want to create an additional folder so I put the ' in front so it wouldn't create a folder, but I thought it would help for it to know where to go.

Public Function Export()
strRoot = "\\folder\folder\folder\folder\Folder\Fallout\Export\"
strMonth = Format(Date, "YYYYMM") & "\"
strDay = Format(Date, "YYYYMMDD") & "\"
strPath = strRoot & strMonth & strDay

DoCmd.TransferSpreadsheet acExport, , "table_export", strPath & "LS Fallout_" & Format(Date, "YYYYMMDD") & ".xlsx", True

Is there a wild card I can use for it to find the folder? I tried adding Currentproject.Path\export\*yyyymm* & Format(Date, "YYYYMMDD") & ".xlsx" within the macro and that doesn't work either.

Thanks
 
Joined
Jun 8, 2001
Messages
2,583
Not real familiar with using the imbedded macros. Do you have and option of saying something like?:
Code:
'Add MARKUP folder
    strMU = objFolder &"\" & "MARKUP"
    If objFSO.FolderExists(strMU) = False Then
            Set newFolder = objFSO.CreateFolder(strMU)
          ' Wscript.Echo "Made folder Here- " & strOCR
      Else
       Wscript.Echo "Folder Exist -" & strMU
    End If
 

stusher

Thread Starter
Joined
Jul 6, 2016
Messages
9
I tried putting that code where the make folder was, prior to the export location, and it did not work.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Does your code have error trapping?
If so what error did you get when trying to run draceplace's code?
As there are a couple of things it could be, like not having a Library Reference, or not dimensioning the variable.

I also notice your code does not "Set" any files, ie

Set xlApp = CreateObject("Excel.Application")

Set xlBook = xlApp.Workbooks.Open("C:\myfile.xls")
 

stusher

Thread Starter
Joined
Jul 6, 2016
Messages
9
When I run the code with in the module I get a run time error and it highlights this line If objFSO.FolderExists(strMU) = False Then

When I run this code within the module it exports
Public Function Export()
strRoot = "\\folder\folder\folder\folder\Folder\Fallout\Export\"
strMonth = Format(Date, "YYYYMM") & "\"
strDay = Format(Date, "YYYYMMDD") & "\"
strPath = strRoot & strMonth & strDay

DoCmd.TransferSpreadsheet acExport, , "table_export", strPath & "LS Fallout_" & Format(Date, "YYYYMMDD") & ".xlsx", True

When I run the Macro with the above code I get this error:
The expression you entered has a function name that Microsoft Access can't find.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Yes that is because the objFSO has to be both dimensioned (dim as) and you then have to set it, unfortunately draceplace's code is not complete.

If
"LS Fallout_" & Format(Date, "YYYYMMDD") & ".xlsx"
is the name of the Excel sheet that you want to look for to see if it exists you could dim a variable called myfile
Dim myfile as string

and then Allen Browne's "fileexists" function.

Place this in a Module
Function FileExists(ByVal strFile As String, Optional bFindFolders As Boolean) As Boolean
'Purpose: Return True if the file exists, even if it is hidden.
'Arguments: strFile: File name to look for. Current directory searched if no path included.
' bFindFolders. If strFile is a folder, FileExists() returns False unless this argument is True.
'Note: Does not look inside subdirectories for the file.
'Author: Allen Browne. http://allenbrowne.com June, 2006.
Dim lngAttributes As Long

'Include read-only files, hidden files, system files.
lngAttributes = (vbReadOnly Or vbHidden Or vbSystem)

If bFindFolders Then
lngAttributes = (lngAttributes Or vbDirectory) 'Include folders as well.
Else
'Strip any trailing slash, so Dir does not look inside the folder.
Do While Right$(strFile, 1) = "\"
strFile = Left$(strFile, Len(strFile) - 1)
Loop
End If

'If Dir() returns something, the file exists.
On Error Resume Next
FileExists = (Len(Dir(strFile, lngAttributes)) > 0)


End Function

You can then call it using this code in a form Command Button etc.
Dim myfile As String

strFile = "LS Fallout_" & Format(Date, "YYYYMMDD") & ".xlsx"

myfile = FileExists(strFile)

'If IsNothing(myfile) Then Exit Sub
If myfile = "" Then Exit Sub ' didn't find it

'assigned path and file name
If myfile Then MsgBox "found"

The last line is just to let you know it found it.
 

stusher

Thread Starter
Joined
Jul 6, 2016
Messages
9
I'm sorry maybe I didn't explain the code correctly? I'm not a programmer I am capable of some code but mostly use queries and macros for minor databases. This LS Fallout_" & Format(Date, "YYYYMMDD") & ".xlsx is the table I am exporting as excel into a folder that I am creating with another macro. That folder is 2016mm with 2016mmdd inside that where the above table exports into. I have a macro that I run prior this one that creates the above folder with its own table to excel export.
 
Joined
Jun 8, 2001
Messages
2,583
Stusher the code snippit I placed above was part of a question to understand what you were doing. Not intended to work in your application. Be careful with vba.

Follow OBPs instructions he will guide you.
 

stusher

Thread Starter
Joined
Jul 6, 2016
Messages
9
Draceplace - I'm not sure what you mean by be careful with VBA? I don't use it often but with exports in 2010 it seems to be the easiest avenue. I guess I should have mentioned in my initial post that I'm not an avid programmer so maybe things would be more explained for me. I was trying to say to OBP that I wasn't trying to find LS Fallout_" & Format(Date, "YYYYMMDD") & ".xlsx but that is my export table. I didn't realize that what you had provided was only a partial code. Thanks for your help.
 
Joined
Jun 8, 2001
Messages
2,583
Just saying vba is very powerfull it can delete every file on a file share without giving a warning if you aren't careful.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
We were responding to this comment that you made at the end "Is there a wild card I can use for it to find the folder?".

However it appears that you do not need to "find" the folder you just need to refer to it in your code.

The Variable called strPath is supposed to give you the complete directions to the folder that the Excel file will go in to.
The code assumes that the folders have been created.
So what I don't understand is why your code is not working.
Can you explain what actually happens when you run the code?
Does it contain error trapping?
 

stusher

Thread Starter
Joined
Jul 6, 2016
Messages
9
When I run the Macro after the folder has been created I get this error:
The expression you entered has a function name that Microsoft Access can't find.
 
Joined
Jun 8, 2001
Messages
2,583
Try changing it to look like this, it may not fix it but its cleaner and not executing a function during the export.

Public Function Export()
strRoot = "\\folder\folder\folder\folder\Folder\Fallout\Export\"
strMonth = Format(Date, "YYYYMM") & "\"
strDay = Format(Date, "YYYYMMDD") & "\"
strPath = strRoot & strMonth & strDay

strFileName = "LS Fallout_" & strDay & ".xlsx"
strExportPath = strPath & strFileName

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12 , "table_export", strExportPath, True
 
Joined
Jun 8, 2001
Messages
2,583
Just noted that strDay will result in a "\" in the strFileName I proposed above. this would be better .

Public Function Export()
strRoot = "\\folder\folder\folder\folder\Folder\Fallout\Export\"
strMonth = Format(Date, "YYYYMM") & "\"
strDay = Format(Date, "YYYYMMDD")
strPath = strRoot & strMonth & strDay & "\"

strFileName = "LS Fallout_" & strDay & ".xlsx"
strExportPath = strPath & strFileName

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12 , "table_export", strExportPath, True
 

stusher

Thread Starter
Joined
Jul 6, 2016
Messages
9
The above code works within the module but when I try to run it via the Macro I still get this error:
The expression you entered has a function name that Microsoft Access can't find.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top