Using DoCmd.TransferSpreadsheet

[Guest]

Having a problem programmatically linking to Worksheet2 in an Excel file.
The link works but it links to Worksheet1.

I'm using simple code something like this:

Public Sub GetLink()
Dim MyPath As String, MyFile As String, MyFullPath As String
MyPath = CurrentProject.Path
MyFile = MyPath & "\" & "MyExcelFile.xls"
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel8, "tblMyTable",
MyFile, True

End Sub

'I thought I could modify the MyFile variable to something like:
MyFile = MyPath & "\" & "MyExcelFile.xls\Worksheet2"
'but it did not work.

Any help would be appreciated.

Thanks,
Dave

 

[Ken Snell [MVP]]

Try using the Range argument of the action to specify the worksheet:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tblMyTable",
MyFile, True, "Worksheet2!"

 

[Nikos Yannacopoulos]

Dave,

That's what the Range argument is for! Try:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8,
"tblMyTable", MyFile, True, "Worksheet2"
(watch out for wrapping in your newsreader; it was an import, right?)

HTH,
Nikos