[AccessD] Code for linking Excel Spreadsheet

Hi Borge

As Joe mentions, your original code will work with two minor modifications.

1. For a WorkSheet as the table to link, append "$" to the WorkSheet name, like "Sheet1$"
Don't do that for a Named Area to link.

2. Use the right connect header. A space is missing in your code:
   
    strFilename = "d:\path\excelfilename.xls"
    tdf.Connect = "Excel 5.0;HDR=YES;IMEX=2;PWD=yourpassword;DATABASE=" & strFilename

/gustav

>>> pcs at azizaz.com 07-02-2008 06:23 >>>
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