Can't link programmatically Excel sheet as table in mdb

[Balex]

Hi,

I am trying to link an Excel sheet as a table in Access through code, and I
stumble whatever I do on the database.TableDefs.Append <LinkedObject>
statement.

Situation is the following:
- there are a number of Excel files in a given directory
- the user gets a list of these in a combo box and clicks (chooses) one
- the code tries to add this Excel sheet as a linked table, and fails...

For the code, I have used an example in the Access help nearly "as is". It
is given as an example of the "Connect and SourceTableName Properties".
Basically what it does is calling a subroutine that has 4 arguments:
- database where the table is going (is = CurrentDB()); called dbsTemp in
the code
- a table name which is used in an OpenRecordset (which I don't reach
anyway, fails before); called strTable; I've put the name I'd like for the
table in the DB in that variable
- a strConnect string, which in my case is a db type (Excel 5.0) and pretty
long path and file name labelled as DATABASE:
Excel 5.0;DATABASE=S:\Securities\Domes&Bonds\WA8R\Projekte\EQ
Investor\makro\KIGOAL optimisation\VBA - Access -
Excel\Trades\2006-05-22_EQISecTrades.xls
- a strSourceTable string, which role I don't quite understand, but which
seems to be the problem; I've put various values in there, to no avail. The
last one is the sheet name within the Excel workbook, "Sheet0"

The routine does this:
Set tdfLinked = dbsTemp.CreateTableDef(strTable)
tdfLinked.Connect = strConnect
tdfLinked.SourceTableName = strSourceTable
dbsTemp.TableDefs.Append tdfLinked

the last statement is the one that is failing, with a runtime error 3011,
the DB engine "could not find the object 'Sheet0'. Make sure the object
exists and that you spell its name and the path name correctly". In the
Access Help example, this variable is jus a plain name which seems to be just
something that one can choose freely. As I said, whatever I set in there, it
doesn't work.

Can anyone tell me where the whole thing is going pear-shaped ?

I'd be grateful for any help, hints, and alternative methods to link by code
an Excel table into an mdb.

Regards
Balex

 

[Klatuu]

For Excel spreadsheets, you should use the TranserSpreadsheet method. Look
in VBA Help for details. You want to use the acLink option.

 

[Balex]

You mean, all this code I poached from the Access help doesn't work ??

 

[Klatuu]

I don't know. I never have tried it that way. I do know the
TransferSpreadsheet with acLink works well. I use it frequently.

 

[Balex]

I wish I had known about this TransferSpreadsheet before ! Works like a
breeze. Thanks a lot, that helped me greatly.

Is it possible to add programatically a column to the linked table ? I'd
like to put a processing status on each of the line of the spreadsheet that
I've read and processed, and to store that back in the spready. How do I do
that ?

Balex

 

[Klatuu]

Because of a law suit against Microsoft, they have had to disable the ability
to write directly to an Excel sheet linked as a table; however, there is a
way around this. The only difficulty is that while you have the spreadsheet
linked you will not be able to replace it using the same name.

The way I would do it would be to create a table with the same structure as
the spreadsheet plus the additional column you want. The import the
spreadsheet into the table, add the data for the additional column, and use
the TransferSpreadsheet to export the table data back to the spread sheet.
So,here is the sequence:

Delete all the data in the table.
CurrentDb.Execute("DELETE * FROM MyTableName;"), dbFailOnError
Link to the Spreadsheet.
Run an append query to move the data to the table.
Delete the link to the spreadsheet.
Add the data to the new column.
Export the data in the table to the spreadsheet.