TransferSpreadsheet Method - Import 5 separate sheets into 5 separate tables 1

[MattGreer]

I recently found out about the TransferSpreadsheet method so I am using this to replace five saved imports. Each of the saved imports would transfer a worksheet from the workbook into Access (2010). The first would import Sheet1 into tblSheet1, the second would import Sheet2 into tblSheet2, and so forth. From there I'd pull the data into the database as appropriate.

Here's my code. Why isn't this working? I get the error "The Microsoft Access database engine could not find the object 'Sheet1'. Make sure the object exists and that you spell its name and the path name correctly. If 'Sheet1' is not a local object, check your network connection or contact the server administrator."

I'm guessing that "Sheet1" in the spreadsheet is not a named range, but I'm not sure what to do with it then.

For x = 1 To 5
     strSheetName = "Sheet" & x
     DoCmd.TransferSpreadsheet acImport, _
          Spreadsheettype:=acSpreadsheetTypeExcel12, _
          TableName:=strImportTable, _
          FileName:=strFileName, _
          HasFieldNames:=True, _
          Range:=strSheetName
Next x

I've tried using different Excel spreadsheet types, from acSpreadsheetTypeExcel12, 12xml, and 9. The file is in .xls format, 97-2003, so I'm using "9" as the type. I'm positive the path to the spreadsheet is correct, as well as the filename.

Thanks!!


Matt

 

[MattGreer]

Not sure if I made it clear, but these sheets are coming from the same workbook. Also, the loop renames the import sheet each time it loops around: strImportTable = "tblSheet" & x

Thanks!!


Matt

 

[PHV]

Try this:
strSheetName = "Sheet" & x & "$"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MattGreer]

HOW THE CRAP DO YOU KNOW THAT?? I wish I had half your knowledge.

Thank you! Worked like a champ!

Thanks!!


Matt