[AccessD] Code for linking Excel Spreadsheet

Borge,

Try changing 2 lines to be:
  tdf.Connect = "Excel 5.0;HDR=YES;IMEX=2;Database=" & varDirectory &
";"
  tdf.SourceTableName = varName & "$"

Joe O'Connell


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
pcs at azizaz.com
Sent: Tuesday, February 05, 2008 9:16 PM
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