[AccessD] Code for linking Excel Spreadsheet

Borge,  changing the aclink to acimport will give you a copy of the s/sheet
as a local table if that helps?

I haven't tried Joe's suggestion of using the $ but will see if it works. -
Thanks Joe.

Max 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of pcs at azizaz.com
Sent: Thursday, February 07, 2008 5:23 AM
To: Access Developers discussion and problemsolving
Subject: Re: [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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com