TransferSpreadsheet method

[aziz rasul]

I have the following code which is supposed to import the second worksheet in an xls file. However it doesn't work because it tries to import the first worksheet in the same xls file into the table designed for the second worksheet.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "Training Cost Summary Actual", Me!Filename, True,""

How do I amend the code so that it reflects the second worksheet?

 

[Talismanic]

I was trying to put together a demo to work through this but I ran out of time. I found this buried in the help file and it may get you going in the right direction.

If you are importing from or linking to a Microsoft Excel version 5.0, 7.0, or Excel 97 spreadsheet, you can specify a particular worksheet by using the Range argument.

and this,

The range of cells to import or link. Leave this argument blank to import or link the entire spreadsheet. You can type the name of a range in the spreadsheet or specify the range of cells to import or link, such as A1:E25 (note that the A1..E25 syntax no longer works in Microsoft Access 97). If you are importing from or linking to a Microsoft Excel version 5.0, 7.0, or Excel 97 worksheet, you can prefix the range with the name of the worksheet and an exclamation point; for example, Budget!A1:C7.

HTH!

 

[aziz rasul]

If the worksheet is say "Totals for Year", how would the syntax change?

 

[kaspi]

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "Training Cost Summary Actual", Me!Filename, True,"Totals for Year"
If you still have problems try to use named ranges instead )if it is a viable alternative.

 

[aziz rasul]

Thanks for your input Kaspi. It seems logical to place it in quotes. However it didn't work.

After investigating other forums (www.mvps.org), I found the answer thanks to Terry Kreft. The syntax is for a complete worksheet:-

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "Training Cost Summary Plan", "C:\example.xls", True, "'Training Cost Summary Plan'!"

The syntax is for a specific range:-

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "Training Cost Summary Plan", "C:\example.xls", True, "'Training Cost Summary Plan'!A1:G10"

OR

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "Training Cost Summary Plan", "C:\example.xls", True, "'Training Cost Summary Plan'!A:G"

this imports the data from columns A to G.