Importing Excel with "&" in worksheet tab

[Guest]

I'm having trouble importing a excel spreadsheet with my VBA code. I'm
currently importing a file that has several worksheet tabs on the bottom of
the spreadsheet. I'm extracting data from a specific tab called
"SigOpt&SigInd_Data". When I write the code to extrac the data, I place the
range, but keep getting an error because of the "&" sign. How would I write
the code for this, to extract that tab with the "&" in the name. Currently my
code looks like this.....

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "Inventory_B",
"C:\Data\Access\32305UCLSOR_PM", True, "SigOpt&SigInd_Data!"

 

[Douglas J. Steele]

Not sure, but see whether using two ampersands in a row works.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97,
"Inventory_B",
"C:\Data\Access\32305UCLSOR_PM", True, "SigOpt&&SigInd_Data!"

 

[Guest]

Thanks Douglas...this didn't work either though.

Not sure, but see whether using two ampersands in a row works.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97,
"Inventory_B",
"C:\Data\Access\32305UCLSOR_PM", True, "SigOpt&&SigInd_Data!"

 

[Dirk Goldgar]

Thanks Douglas...this didn't work either though.

What exact error are you getting, and what version of Access are you
using? I've tried this with Access 2002, and I'm not having any trouble
importing a worksheet with an ampersand in the name. I take it that
"SigOpt&SigInd_Data" really is the name of the worksheet, and not a
named range -- I didn't test the latter.