title: Importing thousands of spreadsheets into Access Database table using VBA
body: <p>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... <em>I am novice when it come to VBA</em> and having trouble finding appropriate solution so thought I will ask for help. </p>
  
  <p><strong>Use Case</strong><br/>
  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. <br/>
  <strong>What I did</strong><br/>
  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. <br/>
  <strong>What Broke</strong><br/>
  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. <br/>
  <strong>What I would like to be able to do</strong>
  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!</p>
  
  <pre><code>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 &amp; "*.xlsm")
        Do While Len(strFile) &gt; 0
              strPathFile = strPath &amp; strFile
              'MsgBox strPathFile
              DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, strTables(intWorksheets), strPathFile, blnHasFieldNames, strWorksheets(intWorksheets) &amp; "$"
  
              strFile = Dir()
        Loop
  
  Next intWorksheets
  
  
  End Function
  </code></pre>