[AccessD] Linking to an excel spreadsheet

Hi John

That is what I mean. You open the workbook as a database, then browse
the TableDefs collection and pick the table(s) you wish to link, be it
by item or name:

<code>

Function CheckXls()

  Dim dbs As DAO.Database
  Dim tdf As DAO.TableDef
  
  Set dbs = DBEngine(0).OpenDatabase("d:\temp\yourworkbook.xls", False,
True, "Excel 5.0;HDR=YES;IMEX=2;")
  Debug.Print dbs.TableDefs.Count & " table(s):"
  For Each tdf In dbs.TableDefs
    Debug.Print , tdf.Name
  Next
  dbs.Close

  Set tdf = Nothing
  Set dbs = Nothing
  
End Function

</code>

Note that worksheet names carries a trailing "$" while Named Ranges
don't.
See my recent posting on "Search for linked documents...."

/gustav

>>> JColby at dispec.com 22-11-2004 19:38:52 >>>
Gustav,

You can link directly to a sheet in a .xls file, whereupon that sheet
looks
like a table in Access.  The tdf is what is used to manipulate the
linked
table, at least in terms of discovering the name of the sheet, the name
of
the file it links to etc.

I receive spreadsheets from the client.  I link them in to the
database,
then write queries to append all the data from the spreadsheet into a
raw
data table.  However, the spreadsheet is created by hand so the sheet
name
internal to the spreadsheet is not necessarily consistent from file to
file.
I am attempting to get the name of the sheet but have had instances
where
that failed.  I have also had instances where I deleted the sheet and
simply
need to "relink" the spreadsheet from scratch.

It is this process to which I am referring.  Given a spreadsheet with
a
single sheet inside, with the data in a format such that the first line
has
field names, how do I "link" the spreadsheet to Access
programmatically?

John W. Colby
The DIS Database Guy


-----Original Message-----
From: Gustav Brock [mailto:Gustav at cactus.dk] 
Sent: Monday, November 22, 2004 1:21 PM
To: accessd at databaseadvisors.com 
Subject: Re: [AccessD] Linking to an excel spreadsheet


Hi John

Are you looking at the xls file as a database? It doesn't seem to be
the case.
The database has tables - worksheets or Named Ranges - it is those
that
are to be linked.

/gustav

>>> JColby at dispec.com 22-11-2004 18:52:47 >>>
Does anyone have code for linking to an excel spreadsheet?

If I manually create a link, the wizard finds all the fields, sets up
with
the "first line has field names" etc.  Everything works fine.  However
if
the table is not linked already, I need to create a tabledef, set the
SourceTableName and Connect properties.  If I then try to append the
tabledef to db.tabledefs I get an error that no fields are defined.  It
is
obvious that a LOT more is involved here.  It appears that I have to
open
the spreadsheet, get the first line, create fields with the data in
the
first line, somehow figure out the datatype of each column etc.  Is
there a
prebuilt code I can call to do all this stuff?  If the wizard can do
it,
then the code behind the wizard could do it for me.

Has anyone ever done this?

John W. Colby
The DIS Database Guy