[AccessD] Linking to an excel spreadsheet

Mark Breen mark.breen at gmail.com
Tue Nov 23 02:18:08 CST 2004


Hello John,

It is quite easy to talk to Excel, but I guess that it depends on what
you need to achieve and which way you prefer to go about it.

I have just completed five days coding, on-site for a large European
retailer that are very, very security conscious.  Unfortunately for
you, I am not allowed to take work off site, believe it or not, I am
not even allowed on the Internet or email.  If it was not for that, I
would just give you the mdb.

In a nutshell, it prepares some data using normal queries.  Once
ready, I open an instance of Excel, add a sheet and then start poking
the values into excel.  The first two or three cells require that you
get used to it, but once they are done, you can then fill the whole
sheet.  I am writing about 35000 cells of data from within nested
loops that work down and across the page.

>From that point, you can then programatically format the entire excel
sheet, including setting page setups etc.

I use the Macro Recorder to get the bones of the VBA that you need and
then bring it back into Access.

One thing that you have to be aware of is the object model in Excel.  

For example, the following code stumped me for a while yesterday

I have three variables 
objXLApp as Excel. Application
objXLBook as Excel.Workbook
objXLSht as Excel.WorkSheet

The following lines are how they are written in Access
objXLSht.Range("A1", "C3").Select
objXLApp.Selection.Font.Bold = True

I would have presumed that it would have been objXLSht on the second
line of code.

Anyway, it is not an Excel table, but I may suit you to just
programatically pull the data in.

Best of luck,

Mark




On Mon, 22 Nov 2004 12:52:47 -0500, Colby, John <jcolby at dispec.com> wrote:
> 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