[AccessD] Code for linking Excel Spreadsheet

Thanks Max and Joe,
I had tried to append the "$" to the sourcetable name with 
no luck; adding the ";" after the Database= value didn't 
help either...
But the docmd.TransferSpreadsheet did the trick

The Codebit that loops the local table holding data on what 
tables (and now also spreadheets) to link now reads:

    Do Until rst.EOF
        '20080206 added capability to link xls spreadsheets -
 must be referred to in tlkpLinkTablesJetSQL_xxx
        If rst!Type = "Excel" Then  
            varDirectory = rst!Directory & rst!Database
            varName = rst![Name]
            DoCmd.TransferSpreadsheet acLink, , varName, 
varDirectory, True
        Else  ' access tables
            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
    rst.MoveNext

Regards
Borge

---- Original message ----
>Date: Wed, 6 Feb 2008 10:01:45 -0500
>From: "Joe O'Connell" <joeo at appoli.com>  
>Subject: Re: [AccessD] Code for linking Excel Spreadsheet  
>To: "Access Developers discussion and problem solving" 
<accessd at databaseadvisors.com>
>
>
>
>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
>
>-- 
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com