link multiple excel sheets with code 1

[LHWC]

Is it possible to link some but not all excel sheets from a workbook to access using the DoCmd.TransferSpreadsheet method. I use Access 2002 DAO.
Thanks in advance.

 

[traingamer]

Yes.

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, _
    "Employees","C:\temp\Newemps.xls", True, "sheetName!"

where sheetName is the name of the worksheet put in the range parameter. The ! is required. You will need to know or otherwise determine the names of the worksheets within the workbook.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[CautionMP]

LHWC,
Building on traingamer's response, the following thread has a concept of how to inspect an Excel WOrkbook for specific Worksheets.

[tab]thread702-1342596 : Importing Excel table into Access via button

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[LHWC]

Thanks to both!
This solution works well, but i would like to have the sheet specified by the value in a combo box. When i try this i get the message tblTable2$ is not a valid name. error 3125. Here's my code:
Private Sub cmdLink_Click()

Dim tblTable1 As String
Dim tblTable2 As String

If (IsNull(txtJobName) Or IsNull(cbxType)) Then
MsgBox "You must choose a valid Job Name and Door Type"
Exit Sub
Else
On Error GoTo cmdLink_Err
tblTable1 = txtJobName
tblTable2 = cbxType
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, tblTable1 & " " & tblTable2, "W:\LHW_JOBS\" & tblTable1 & "\Design\" & tblTable1 & " Doors" & ".xls", True, "tblTable2!"
MsgBox "The linked table '" & tblTable1 & "_" & tblTable2 & "' has been successfully created."
End If
DoCmd.Close
DoCmd.OpenForm "frmSwitchboard", acNormal

cmdLink_Exit:

Exit Sub

 

[CautionMP]

LHWC,
Is your worksheet named [tt]tblTable2[/tt]?

...
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, tblTable1 & " " & tblTable2, "W:\LHW_JOBS\" & tblTable1 & "\Design\" & tblTable1 & " Doors" & ".xls", True, [b]"tblTable2!"[/b]
...

CMP



[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT+08:00) Singapore

 

[LHWC]

No, but tblTable2 = cbxType, which has a value list of sheet names.

 

[CautionMP]

...
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, tblTable1 & " " & tblTable2, "W:\LHW_JOBS\" & tblTable1 & "\Design\" & tblTable1 & " Doors" & ".xls", True, [b]tblTable2 & "!"[/b]
...

CMP?

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT+08:00) Singapore

 

[LHWC]

Works lika charm, tricky syntax. I never would have thought to try it. Thanks!