[AccessD] Linking to an excel spreadsheet

Colby, John JColby at dispec.com
Mon Nov 22 12:38:52 CST 2004


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

-- 

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list