Append tables via code.

[Guest]

So having failed down the macro route, I'm now looking at the VBA route.

I'm trying to append a sheet in an Excel workbook, which has 12 sheets in it.

The code ...


---------------------
Dim tbl2 As DAO.TableDef
Dim dbMe As DAO.Database

Set dbMe = CurrentDb

Set tbl2 = dbMe.CreateTableDef("Test")

tbl2.Connect = "Excel 8.0; DATABASE={PATH}"
tbl2.SourceTableName = "Committed & Invoices"

dbMe.TableDefs.Append tbl2

-----------------------

It's erroring on the name "Committed & Invoices". I know that this is the
sheet name as I copied the name directly into the sheet, but not sure why it
can't find this sheet name.

Any help I'd be grateful of.

TIA.

 

[Stefan Hoffmann]

hi Dave,

I'm trying to append a sheet in an Excel workbook, which has 12 sheets in it.
The code ...
Set tbl2 = dbMe.CreateTableDef("Test")
tbl2.Connect = "Excel 8.0; DATABASE={PATH}"
tbl2.SourceTableName = "Committed & Invoices"

Try "[Committed & Invoices$]" as sheet name.

It's erroring on the name "Committed & Invoices". I know that this is the
sheet name as I copied the name directly into the sheet, but not sure why it
can't find this sheet name.

Also try a query:

SELECT *
FROM [Excel 8.0;Database={Path}].[Committed & Invoices$]


mfG
--> stefan <--