DoCmd.TransferSpreadsheet for specific worksheet in .xls file

[teddyv]

I have been asked to modify the code in a 2007 Access database that was simply importing a monthly single sheet Excel file. Now the user should be able to select the sheet to import from multiple sheets that are now in the file.

The tab names are consistant from monthly file to monthly file so I have added a drop down list with the available tab names and store the selection in "txt_defaulttab".

The user is also able to browse to the monthly file and then the path is stored in txt_defaultimport.

I've been searching for the code to to represent the sheet to import. Access help says "If you are importing from or linking to an Excel version 5.0 or later spreadsheet, you can prefix the range with the name of the worksheet and an exclamation point; for example, Budget!A1:C7. ", but it doesn't work the way I am doing it.



Private Sub Command0_Click()
DoCmd.SetWarnings False


DoCmd.OpenQuery "Delete Monthly Excel Import", acViewNormal

' "txt_defaultimport" is the path to the .xls file
' "txt_defaulttab" is the name of the worksheet tab to import

Dim def As String
def = txt_defaultimport

DoCmd.TransferSpreadsheet acImport, 8, "Monthly Import", txt_defaultimport, True, txt_defaulttab & "!"

DoCmd.OpenQuery "Delete Blank Lines", acViewNormal
def = DCount("[ID]", "Monthly Import")
MsgBox def & " items Imported", vbInformation + vbOKOnly, "Count Items Imported"

DoCmd.SetWarnings True
End Sub


Any help would be greatly appreciated!

[June7]

Are txt_defaultimport and txt_defaulttab controls on the form? Have you step debugged? Try using qualifer prefix for the controls:
DoCmd.TransferSpreadsheet acImport, 8, "Monthly Import", Me.txt_defaultimport, True, Me.txt_defaulttab & "!"

[teddyv]

Are txt_defaultimport and txt_defaulttab controls on the form? Have you step debugged? Try using qualifer prefix for the controls:
DoCmd.TransferSpreadsheet acImport, 8, "Monthly Import", Me.txt_defaultimport, True, Me.txt_defaulttab & "!"

Yes, the two variables are controls on the form and qualifying the variables does not help.

The attached file shows the form and the error I am getting. It is replacing the "!" in the code with a "$". Is that what it's supposed to do?

Do I need to add a range like A1:Z5000 to the worksheet name? When I do, I get a error 3011, could not find the object "Bank$A1:Z5000".

[June7]

I tested code and cannot replicate the issue. The import runs fine with the concatenated "!". Try without the "!". The sheet name might be sufficient, especially since not referencing range. I didn't think to try before deleting my test file. Let me know.

You could also test by hard coding those values. That's what I did instead of referencing textboxes. If that works then know something is wrong with the textbox inputs.

[teddyv]

Thanks for your help.

The requestor has just decided to cancel the change request.