TransferSpreadsheet

[apank]

Is there a way to differenciate between multiple Excel Spreasheets in an Excel Workbook when writing the code to TransferSpreads heet?

When I do the command now, I only have one worksheet.

Example:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Test", "c:/test.xls", True

Now suppose the workbook had 3 worksheets: "test1", "test2" and "test3". How would I name the file in the code if I only want to pull "test2"?

Thank you in advance for your input.

[NeoPa]

I've moved this into the Answers forum for you. It had been posted in Insights.

On to your question :
Can you please clarify what you are trying to do. At one point you indicate you're reading FROM the spreadsheet, yet at another you indicate you're writing TO it :S

[NeoPa]

For importing a specific worksheet from your workbook file use the Range parameter :

DoCmd.TransferSpreadsheet TransferType:=acImport, _
                          SpreadsheetType:=acSpreadsheetTypeExcel9, _
                          TableName:="Test", _
                          FileName:="c:/test.xls", _
                          HasFieldNames:=True, _
                          Range:="Test2!A1:Z999"

The Range parameter is in the standard Excel format for a range, but I don't know how to say simply load the available range within the sheet.

[apank]

Sorry......I am tring to do a transferspreads heet from a workbook that has multiple worksheets. How do specify which worksheet I would like the code to pull?

[NeoPa]

When writing TO a range in a workbook, you need to ensure that the name of the table (or query) matches the name you choose for the worksheet.

Not always straightforward . Quite a limiting interface IMHO.

[apank]

Thank you very much. This helped me out a lot.

[NeoPa]

No worries. Very pleased to have helped :)