Excel TransferSpreadsheet question.

[DaveO]

I'm trying to link an Excel spreadsheet to the database I'm working on
(access 97).

There are 12 worksheets in the Excel workbook and I wish to reference a
sheet called Committed & Invoices, which is sheet12.

I cannot, for love nor money, get this to work.

Here are the things I've tried for the range value:

Committed & Invoices! - error os it think the ! is a $.
Committed & Invoices - object not found.
Committed & Invoices$ - object not found.

Can anyone help at all please?

 

[fredg]

I'm trying to link an Excel spreadsheet to the database I'm working on
(access 97).

There are 12 worksheets in the Excel workbook and I wish to reference a
sheet called Committed & Invoices, which is sheet12.

I cannot, for love nor money, get this to work.

Here are the things I've tried for the range value:

Committed & Invoices! - error os it think the ! is a $.
Committed & Invoices - object not found.
Committed & Invoices$ - object not found.

Can anyone help at all please?

Your syntax of "Committed & Invoices!" (WITH the bang) worked for me.
Are you sure you have the (FileName and Path) correct? i.e. "C:\My
Folder\FileName.xls".
Are you sure the worksheet name is exactly "Committed & Invoices"
(WITHOUT the bang)?

Try it with the path and file name specifically written out:
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel97, "test",
"C:\My Folder\FileName.xls", True, "Committed & Invoices!"

Also try it with a range of cells within the worksheet:
"Committed & Invoices!A2:B20"