[AccessD] Code for linking Excel Spreadsheet

The format I use is this:

>From a Recordset, viz;

DoCmd.TransferSpreadsheet rst!ActionType, , rst!Destination, rst!Source,
bHasFieldNames 

or directly viz; 

DoCmd.TransferSpreadsheet acLink, , "tblTestXLS", "c:\test.xls", True

rstActionType can be acLink or acImport

The rst!Source must be an .xls type file,

bHasFieldNames indicated whether the first line of the s/sheet has headers
or data.

HTH

Max 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of pcs at azizaz.com
Sent: Wednesday, February 06, 2008 2:16 AM
To: Access Developers discussion and problemsolving
Subject: [AccessD] Code for linking Excel Spreadsheet

I have problems extending existing code for programmatic linking of tables
to also include excel spreadsheet. These are simple spreadsheets with one
worksheet named the same as the excel file

I have the definitions of the tables / spreadsheets I want to link in a
separate tlkpLinkedTables.

Part of the code that loops this definition table looks like this:

       If rst!Type = "Excel" Then  ' dealing with a spreadsheet
            varDirectory = rst!Directory & rst!Database
            varName = rst![Name]
            Set tdf = dbs.CreateTableDef(varName)
            tdf.Connect = "Excel5.0;HDR=YES;IMEX=2;Database="
& varDirectory
            tdf.SourceTableName = varName
            dbs.TableDefs.Append tdf
        Else  'jet tables in .mdb
            varDirectory = rst!Directory & rst!Database
            varName = rst![Name]
            Set tdf = dbs.CreateTableDef(varName)
            tdf.Connect = ";Database=" & varDirectory
            tdf.SourceTableName = varName
            dbs.TableDefs.Append tdf
        End If
 
Works fine with the access tables but no excel spreadsheet is being linked
.... What am I missing here???

I can link ok through the user interface, and the values for the resulting
linked excel file in the MSysObjects table are the ones I am parsing in the
code as well:


?

Regards
borge
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com