Import Excel Spreadsheets in folder to a table

[Douglas Packard]

I have an MS Access application that generates Excel spreadsheets (single
tab) for emailing to respondents. When the emails are returned, they are
detached into a single folder for uploading. I have a requirement to load
these into an Access table (named "Responses") on a regular basis. They are
always identical in format but the number of spreadsheets varies so I need
VBA code to import each spreadsheet in the folder until all the files are
processed. I do have an index file that lists all of the received file
names, called Index.txt that may be useful.

The number of spreadsheets can vary from 50 to more than a thousand so any
help with this would really be appreciated.

 

[Ken Snell \(MVP\)]

Here is some sample, generic code to loop through the EXCEL files and import
the first spreadsheet in each file:

Dim strPathFile as String, strFile as String, strPath as String
Dim strTable as String
Dim blnHasFieldNames as Boolean
' Change this next line to True if first row in EXCEL sheet has field names
blnHasFieldNames = False
strPath = "C:\Documents\"
strTable = "tablename"
strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, _
strPathFile, blnHasFieldNames
' Comment out the next code step if you do not want to delete the file
' after it's been imported
Kill strPathFile
strFile = Dir()
Loop

 

[Douglas Packard]

Sorry for not responding sooner. I have been business traveling for the past
month and just had the chance to implement the code you sent this week. You
were very helpful and I really appreciate it.