Need Help Sending an Excel File to Access Table via VBA

[jamesdeuce]

I am relatively new to programming. What I am trying to do is download a text file from the internet every hour, then get that into Excel, format it, save the file with the "date-time" stamp of when it downloaded, then send that sheet into an Access table. I have been able to get the Task Scheduler to kick off a generic Excel Workbook containing a macro, which then downloads the text file, and formats the data and saves the new "date-time" Excel file. The Excel VBA continues to run to get this new Excel data into Access but my problem lies in getting that data into Access. The process crashes there. The Task Scheduler gets hung up and won't run any of the process thereafter. The Excel Workbook name that Access is calling is constantly changing as it's "date-time".xlsm although the sheet with the data is the same name as I build that sheet in the formatting process. Here is what I have thus far...I am close...but not really. Perhaps there is an Access setting that needs to be changed. Perhaps the code is bad. Perhaps there is a better way to do this automatically. Any help is greatly appreciated. Thank you very much.

'So in Excel VBA...by this point in the process I have an Excel file saved automatically...the code continues below...I used double quotes to make things stand out to you...in my real code it doesn't have the double quotes except where necessary.

'This part is in Excel VBA
strPath = "FilePathOfAccessFileIncludingTheAccessFile.accdb"
strModule = "ModuleNameInAccessContainingMacroInAccess"
strMacro = "MacroNameInAccess"
strMacroArgument = "DateOfExcelFileBeingPassed"

'Function Call In Excel
Call AccessImport(strPath, strModule, strMacro, strMacroArgument)

'Function in Excel calling Access
Public Function AccessImport(strPath as string, strModule as string, strMacro as string, strMacroArgument as string)
Dim appAccess as Access.Application
Set appAccess = New Access.Application
appAccess.OpenCurrentDatabase strPath
appAccess.DoCmd.Open Module strModule, strMacro
appAccess.Run strMacro, strMacroArgument
appAccess.CloseCurrentDatabase
End Function

'At this point in the process in Excel is done and now we go to Access via VBA

Sub "MacroNameInAccess" ("DateOfExcelFileBeingPassed" as string)
Dim strPathAccess as String
Dim XLSFile as String
Let XLSFile = "DateOfExcelFileBeingPassed" & ".xlsm"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "Access Table", strPathAccess & XLSFile, True, "ExcelSheetName!"
End Sub

[ItsMe]

Why are you formatting the .txt file in Excel?

Access has ways to import text files and place (format the data) directly into a table. You can start by researching delimited text files.

Even if a file is .CSV, access will treat it as .TXT.

Look at your text file and see how it is delimited. Some times it is with a character like a comma. Sometimes it is with a TAB space. Other times the text file will be a "Fixed Width". Knowing this first and then using the wizard to import the text file into Access is where I would start.

[jamesdeuce]

The data that comes in isn't in an ideal format as is...so I bring it into Sheet1, space delimit it, delete a worthless column, then I move the "ugly" data around and ultimately put the newly formatted data into Sheet2...which is how I want it to look in Access...then I want to bring Sheet2 into Access from the organized Excel Sheet. From that point I am calling Access to import Sheet2 into an Access table. Plus as a "backup" per se I want all the Excel Files for each "date-time".

[ItsMe]

OK, sounds like a good approach. Let me take a closer look at your post and see what I can see.....

[ItsMe]

I have a few questions but I would like to start with...

Why are you using "acSpreadsheetTypeExcel12Xml"

What is the extension on the spreadsheet you are importing? Is it .XML or .XLS or .XLSX or .XLSM or .PRN ?????

Are you importing the entire worksheet within the workbook or do you have a named range that the Macro is handling?

[jamesdeuce]

I am importing an .XLSM file. And that's what Google told me to use for that "acSpreadsheetTypeExcel12Xml". I am importing an entire worksheet into Access.

[ItsMe]

the code seems good. It must be something to do with the macros. You mentioned that everything get's sorted out inside the workbook to format the text file proper. So I suppose that is why .XLSM. I guess the Function or Macro within Access is what is managing the import for you.

You mention the dynamic filename is the hiccup. I believe this to be true. It seems you have tested all with the name coded as a static name. Have you tested with a static name?

I don't know that I would have gone down this road to get where you are now. Now that you are here, I guess you could add more code in Access to determine the file name. Another approach may be to have Excel make a separate, standalone worksheet with a static predetermined name that Access will recognize. You could place it in a folder dedicated to temp files. Probably easiest for Access to just go in and export everything it finds within a folder.

I don't know how difficult it would be to get Excel to make a new separate worksheet or even if that fits into the business rules of assigning Keys to what is being imported.

A third option may be to take a few steps back and see if Access can't manage the jumbled mess of the original text file.

Can you upload a sample text file here? Does it contain sensitive data?

[jamesdeuce]

I was able to tweak some things in the macro in Excel that calls Access from various code I found on the internet. One of the many things I did was add DoCmd.SetWarnings False which may have been the original culprit. It automatically goes to Access now from each of the Excel Date-Time.xlsm files. Consider it solved. It even works when Access is left open and the Table is left open. Thank you for your replies and your ideas.

[ItsMe]

That is good to know. It took me a while to begin to understand what your code was doing.

Glad you got things working!