CODEWITHSUNDEEP
excelvbams-access
sigil
sigil

Reputation: 9546

DoCmd.TransferSpreadsheet is not recognizing worksheet name

I'm trying to import a sheet from an Excel workbook using DoCmd.TransferSpreadsheet. The sheet's name is XYZ Priority.

I'm calling the method as follows:

DoCmd.TransferSpreadsheet FileName:=filePath, tablename:="XYZ", _
                          hasfieldnames:=True, range:="[XYZ Priority]!"

I get this error message:

'[XYZ Priority]$' is not a valid name. Make sure that it does not 
include invalid characters or punctuation and that it is not too long.

It appears that the space is what's causing the problem. I've tried several different ways to identify the range, with and without exclamation points, and all have failed:

"XYZ Priority"
"XYZ_Priority"
"'XYZ Priority'"

How can I correctly pass the sheet to TransferSpreadsheet?

Upvotes: 3

Views: 6559

Answers (1)

HansUp
HansUp

Reputation: 97100

Use just the sheet name followed by the $ sign. 

DoCmd.TransferSpreadsheet FileName:=filePath, TableName:="XYZ", _
    HasFieldNames:=True, Range:="XYZ Priority$"

Upvotes: 3

Related Questions