Import identical excel files into access with multiple worksheets

Question

So, I'm trying to find ways to facilitate recombining data in excel sheets using access. What I am trying to do is take multiple excel files that are all identically formatted and concatenate them into a contiguous table. Right now I have a VBA function that will allow me to target one excel worksheet across a directory of excel files and combine them into one access table. My question is, how can I go about doing that same thing, but for EVERY worksheet in the directory in one shot, instead of running and modifying the code for every worksheet.

TL;DR You have 100 excel files, each with 7 worksheets in them. The formatting is identical, but the data is different. How do I take all 100 files and combine their worksheets into 7 respective MS Access tables?

****** ISSUE HAS BEEN SOLVED. WORKING CODE AS FOLLOWS *******

Module 1 named SingleModule:

    Option Compare Database

    Public Function importExcelSheets(Directory As String, TableName As String, WkShtName As String) As Long
    On Error Resume Next
      Dim strDir As String
      Dim strFile As String
      Dim I As Long
      I = 0
      If Left(Directory, 1) <> "\" Then
          strDir = Directory & "\"
      Else
          strDir = Directory
      End If
          strFile = Dir(strDir & "*.XLSX")
      While strFile <> ""
          I = I + 1
          strFile = strDir & strFile
          Debug.Print "importing " & strFile
          DoCmd.TransferSpreadsheet acImport, , TableName, strFile, True, WkShtName
          strFile = Dir()
      Wend
      importExcelSheets = I
    End Function

Module 2 named MultipleModule:

Public Function importMultipleExcelFiles(Directory As String) As Long
For x = 1 To 7
Dim TableName As String
Dim WkShtName As String
    TableName = Choose(x, "Table1", "Table2", "Table3", "Table4")
    WkShtName = Choose(x, "Table1!", "Table2!", "Table3!", "Table4!")
    Call SingleModule.importExcelSheets(Directory, TableName, WkShtName)
Next x
End Function

Use the following command in the Immediate Window to execute (Make sure you change the filepath):

? importMultipleExcelFiles("C:\Excel File Directory")

SIDE NOTE:

You can target one worksheet using the following command on SingleModule in the Immediate Window:

? importExcelSheets("C:\FilePath", "TableName", "WkShtName!")

You can do this using SQL, google SQL Insert Into from Excel, something like ......FROM [Excel 12.0;HDR=Yes;Database='c:\excelsheet.xlsx'].[Sheet1$] then you can use Currentdb.execute or docmd.runsql to do it — Nathan_Sav

geeFlo geeFlo · Accepted Answer · 2016-10-04T15:57:52

Sorry, I didn't see how your TableName was going into the procedure. Use the FOR-NEXT with the CHOOSE function, when you're calling the importExcelSheets function.

for x= 1 to 7
   TableName = choose(x, "Table1", "Table2"...)
   WkShtName = choose(x, "ContactDetails!", ...)
   importExcelSheets  Dir, TableName, WkshtName
next x

Import identical excel files into access with multiple worksheets

1 Answers