TransferSpreadSheet Method

[aziz rasul]

I have a file called "ENT Figures.xls", which contains a worksheet called "Pivot Calculation". The following is the code I'm using to import the worksheet where the filename is in a recordset. I have NO CONTROL over the filename or worksheet names.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTemp", _
"C:\Theatres Database\Specialty\" & rstTheatreSpecialities!Spreadsheetname, _
False, "'Pivot Calculation!'"

I obtain error 3011 - Could not find the object 'Pivot Calculation$'.

 

[gemma-the-husky]

try relabelling the spreadsheet tab to remove the space, just to see if this works

i think the range parameter should just be PivotCalculation!, without any delimiters

see if that works then try to deal with ther space

maybe

[Pivot Calculation]!

will accommodate the space

 

[Rabbie]

I have a file called "ENT Figures.xls", which contains a worksheet called "Pivot Calculation". The following is the code I'm using to import the worksheet where the filename is in a recordset. I have NO CONTROL over the filename or worksheet names.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTemp", _
"C:\Theatres Database\Specialty\" & rstTheatreSpecialities!Spreadsheetname, _
False, "'Pivot Calculation!'"

I obtain error 3011 - Could not find the object 'Pivot Calculation$'.

Just a small query. Are you certain that rstTheatreSpecialities!Spreadsheetname is holding the correct name including the extension? That could be worth checking.

 

[aziz rasul]

I've tried

DoCmd.TransferSpreadsheet acImport, 8, "tblTemp", "C:\Theatres Database\Specialty\entfigures.xls", False, "PivotCalculation"

so that I got rid of the space within both the filename and worksheet (even though I will have to put the spaces back in afterwards). I've also placed the filename in full without using the recordset.

But it still doesn't work and comes up with the same error.

Even

DoCmd.TransferSpreadsheet acImport, 8, "tblTemp", "C:\Theatres Database\Specialty\entfigures.xls", False, "PivotCalculation!"

didn't work. I'm stumped.

 

[aziz rasul]

I solved the problem.

The reason the code wouldn't work was that I had opened the Excel object thru code and created the 'Pivot Calculation' worksheet. I had to SAVE the file before I could import the data in.

Yeeehaaaaaaaaaaaaaaaaaaaaaaaaaaaaa.

 

[gemma-the-husky]

was your syntax correct then, originally?

 

[aziz rasul]

Yes it was.