I found this code online (forgot where) and it works amazing when I need to combine multiple excel files. The only problem is, it only combines the first tab of each file. Many of the file groups I'd like to combine might have 2, 3 or more worksheets (tabs). How can I modify this code to combine Excel files that contain multiple worksheets. Note, each worksheet should be a separate table. Thank you in advance!
Code:
Private Sub btnImportSpreadsheet_Click()
On Error Resume Next
Dim strDir As String, strSQL As String
Dim strFile As String
Dim I As Long
Dim Directory As String, TableName As String
Directory = Me.txtFileName
TableName = "MyExcelImport"
I = 0
strDir = Directory
strFile = Dir(strDir & "*.XLSX*")
While strFile <> ""
I = I + 1
strFile = strDir & strFile
SysCmd acSysCmdInitMeter, "importing " & strFile
DoCmd.TransferSpreadsheet acImport, , TableName, strFile, True 'has columnheaders
' Add column MyFileName for the source of data
If DoesFieldExist("MyFileName", TableName) = False Then
Dim strField As String
Dim curDatabase As Object
Dim tblTest As Object
Dim fldNew As Object
Set curDatabase = CurrentDb
Set tblTest = curDatabase.TableDefs("MyExcelImport")
strField = "MyFileName"
Set fldNew = tblTest.CreateField(strField, dbText)
tblTest.Fields.Append fldNew
End If
' Run update query
DoCmd.SetWarnings False
strSQL = "UPDATE MyExcelImport SET MyExcelImport.MyFileName=" & Chr(34) & strFile & Chr(34) & "WHERE MyExcelImport.MyFileName IS NULL"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
SysCmd acSysCmdRemoveMeter
strFile = Dir()
Wend
'importExcelSheets = I
MsgBox I & " File(s) imported "
End Sub