My thoughts:
Please review this link on why crossposting is frowned upon.
http://www.excelguru.ca/content.php?184
Despite several requests for clarification of your problem, I am still confused. Yes, I know there is a "big" Excel SS that you import and you want to break it into 60 smaller tables. But breaking the big table into 60 smaller tables does nothing for security. I can look at 60 small tables just as easily as looking at one big table. You need to limit records available to be viewed by good programing.
As far as the code, I saw several problems:
The top two lines in EVERY module (form, report, standard) should be
Code:
Option Compare Database
Option Explicit
There are two major errors in this code snippet:
Code:
' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "C:\Users\dsegedin\Desktop\Test\Folder"
' Replace 3 with the number of worksheets to be imported
' from each EXCEL file
For intWorksheets = 1 To 2
strFile = Dir(strPath & "*.xls") 'this will always return "" . see next line
Debug.Print strPath & "*.xls" 'I added this to see what strFile should be
' this is what is returned -> C:\Users\dsegedin\Desktop\Test\Folder*.xls
' back slash is missing between the last folder name and the file name.
strFile = Dir(strPath & "\*.xls") "<<== this is what the line should be
' Debug.Print strWorksheets(intWorksheets) & "$"
Do While Len(strFile) > 0
strPathFile = strPath & strFile "<<== this line is also missing the backslash
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, strTables(intWorksheets), _
strPathFile, blnHasFieldNames, _
strWorksheets(intWorksheets) & "$" '<-- the range is Sheet1$ ?????
strFile = Dir()
Loop
Next intWorksheets
The other thing I don't like is the logic of having the "DO...While" inside the "For... Next"
I would have it the other way
Code:
Do While Len(strFile) > 0
For intWorksheets = 1 To 2
Next
Loop
You've found a valid file, now load all of the sheets.
BTW, from the code, it looks like if there were 5 spreadsheets, all of the data from "Sheet1" in all if the spreadsheets are loaded into the same table and all of the data from the Sheet2 sheets are loaded into the another table??