I have an excel spreadsheet that has 5 different tabs. I would like to create this spreadsheet in access represented by 5 different tables.
My first attempt at this is to import in the files. I have the following code
Function ImportFiles()
Dim myfile
Dim myDir
mypath = "C:\Data\Dashboard\"
myDir = Directory
myfile = Dir(mypath & "*.xlsx")
While myfile <> ""
Debug.Print "importing" & myfile
DoCmd.TransferSpreadsheet acImport, , "Data", myfile, True
myfile = Dir()
Wend
End Function
I have five tables in my access database. Do I need to write some excel vba to create 5 files that strips out each of the 5 tabs and then import those 5 tabs into the corresponding tables within my access database? Or is there any way to adapt my above code to put the 1st tab of data within each sheet into the first table, the second tab into the 2nd table etc ?
