title: Import Multiple Excel Files
body: <p>I have created the following to import 47 excel files into Access; all with the same naming convention REPORT01, REPORT02 etc.</p>
  
  <p>However, I now have a list of files with varying names.  Can someone suggest how I would tailor this to accomdate the change?  I was thinking that I could create a separate module with the excel file names in that the main code just references whilst it loops through the folder.  If it finds a match, import.</p>
  
  <pre><code>Sub ImportTables()
  ' Define base path
  Const cstrFolder As String = "F:\TCB_HR_KPI\Data View\"
  
  Dim strExt As String
  Dim strFile As String
  Dim strTable As String
  Dim i As Long
  Dim fileCount As Long
  
  ' Check all 47 files exist
  For i = 1 To 47
      strFile = cstrFolder &amp; "REPORT" &amp; Right("0" &amp; i, 2) &amp; ".xls"
      If Dir(strFile) &lt;&gt; "" Then fileCount = fileCount + 1
  Next i
  
  ' Partial results allowed, only exit when no matches found
  If fileCount = 0 Then
      MsgBox "Files not found"
      Exit Sub
  End If
  
  ' Second loop to import data
  fileCount = 0
  For i = 1 To 47
      strFile = cstrFolder &amp; "REPORT" &amp; Right("0" &amp; i, 2) &amp; ".xls"
      If Dir(strFile) &lt;&gt; "" Then
          Debug.Print "Found: " &amp; strFile
          DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "REPORT" &amp; Right("0" &amp; i, 2), strFile, True
          fileCount = fileCount + 1
      End If
  Next i
  
  MsgBox fileCount &amp; " files imported."
  End Sub
  </code></pre>