Import a specific worksheet from Mulitple Workbooks in a folder

[dgaller]

I have the code below to import Data into a master access table which works for the most part but I still have a few issues.

1. I get a bunch of type conversion errors evertime it is run.
2. Can I limit the range of the imported sheets? It imports a lot more lines below what is needed every sheet that is imported only has data in range A1:BF51.

Function import()
 
Dim strPathFile As String, strFile As String, strPath As String
Dim blnHasFieldNames As Boolean
Dim intWorksheets As Integer
 
Dim strWorksheets(1 To 1) As String
Dim strTables(1 To 1) As String
strTables(1) = "State List"
strWorksheets(1) = "State List"
blnHasFieldNames = True
strPath = "C:\files\Test\"
For intWorksheets = 1 To 1
      strFile = Dir(strPath & "*.xls")
      Do While Len(strFile) > 0
            strPathFile = strPath & strFile
            DoCmd.TransferSpreadsheet acImport, _
                  acSpreadsheetTypeExcel9, strTables(intWorksheets), _
                  strPathFile, blnHasFieldNames, _
                  strWorksheets(intWorksheets) & "$"
            strFile = Dir()
      Loop
Next intWorksheets
End Function

 

[Zaz]

If i recall correctly the method can include the range, something like this:


DoCmd.TransferSpreadsheet acImport, 8, "Yourtablename", "filepath of spreadsheet", True, "Sheet2!A1:E273"


not sure if the exact syntax is still correct, didnt use it in a long time


Rgds
Z