Importing thousands of spreadsheets into Access Database table using VBA

Question

I have created this process by lifting code from this (and some other) sites, and it worked like a charm when I tested it, but when I deployed it, it crashed hard... I am novice when it come to VBA and having trouble finding appropriate solution so thought I will ask for help.

Use Case
Accountant receives 100+ spreadsheets per day(!) from employees in the field as a form of required reporting. Before I got involved, 3 accountants would open each spreadsheet received via e-mail and copy/paste certain cell contents into a "master" spreadsheet that will be used for reconciliation at the end of the month. Needless to say, this has become, let's say, inefficient.
What I did
I created an Access DB and used TransferSpreadsheet method to import data. There are only 11 cells that we need imported from each spreadsheet, so I modified spreadsheets that field employees use to pull all this data into a hidden tab, where all data is in one row and all row goes into one table in Access. As I mentioned, when I and accountants tested the solution, it worked beautifully.
What Broke
First issue was that people in a field did not have same versions MS Office, and some used OpenOffice instead and we would get errors when trying to import some spreadsheets. However, since my simple solution was built for "prefect path" only, it was impossible to identify which spreadsheets failed, especially, when there are 2000+ of them sitting in a folder.
What I would like to be able to do Short-term, until I have time to master VBA, I would love to add some sort of error handler. Or even if after import, "good" spreadsheets would be sent to one folder, and "bad" ones to another. From my experience,about 80% of reports import fine. Once it loops through the whole folder, accountants can check "failed imports" folder and enter these manually. My questions to you, Access VBA experts, is this doable and would be a reasonable solution? If so, can you please direct me to it? Below is my current code that I adapted from the internets. Thank you for all your help!

Function DoImport()

Dim strPathFile As String
Dim strFile As String
Dim strPath As String
Dim blnHasFieldNames As Boolean
Dim intWorksheets As Integer
Dim strWorksheets(1 To 1) As String


' the number of worksheets to be imported
' from each EXCEL file (this code assumes that each worksheet
' with the same name is being imported into a separate table
' for that specific worksheet name)
Dim strTables(1 To 1) As String

' worksheet names ;
' add / delete code lines so that there is one code line for
' each worksheet that is to be imported from each workbook file
strWorksheets(1) = "Data"

strTables(1) = "my_table"

' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True

'update the path to where the Excel files to be imported are
strPath = "\mypathhere\"

' the number of worksheets to be imported
' from each EXCEL file
For intWorksheets = 1 To 1

      strFile = Dir(strPath & "*.xlsm")
      Do While Len(strFile) > 0
            strPathFile = strPath & strFile
            'MsgBox strPathFile
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, strTables(intWorksheets), strPathFile, blnHasFieldNames, strWorksheets(intWorksheets) & "$"

            strFile = Dir()
      Loop

Next intWorksheets


End Function

If you're a novice with VBA might i suggest SSIS. It's a more visual system and it is good at doing things like looping through files and moving them into folders. http://stackoverflow.com/questions/6190578/how-to-import-excel-files-with-different-names-and-same-schema-into-database. You can use it to load into MS Access but I suggest you go all the way and load into SQL Server. Unfortunately with excel as a source you are always going to have data quality problems. People adding columns, rows, and typing nonsense in. — Nick.Mc, Oct 07 '15 at 23:08

score 0 · Accepted Answer · answered Oct 07 '15 at 23:28

This is off the top of my head (so there may be sytax errors) but the idea is to set the error handling to jump to code that saves the filename and then jump back into the process:

  ON ERROR GOTO IMPORT_ERROR 

For intWorksheets = 1 To 1

      strFile = Dir(strPath & "*.xlsm")
      Do While Len(strFile) > 0
            strPathFile = strPath & strFile
            'MsgBox strPathFile
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, strTables(intWorksheets), strPathFile, blnHasFieldNames, strWorksheets(intWorksheets) & "$"

            strFile = Dir()
GetNextFile:

      Loop

Next intWorksheets

EXIT FUNCTION

IMPORT_ERROR:
'ADD CODE HERE TO HANDLE ERROR

ON ERROR GOTO IMPORT_ERROR 

GOTO GetNextFile

End Function

Importing thousands of spreadsheets into Access Database table using VBA

1 Answers1