[AccessD] Linking to an excel spreadsheet

Hi Mark

A client had a similar need. I followed John's advice and used this
method which is great because it separates the handling of data from the
formatting and design of the workbooks.

The solution has been running for a statistics database publishing each
month via e-mail neat spreadsheet reports for over two years without any
error.

/gustav

>>> jwcolby at colbyconsulting.com 23-11-2004 13:15:17 >>>
Mark,

I did the same kinds of thing down in Mexico.  A client ran a
maquilladora
making clothing.  They were simply accustomed to Excel and able to
manipulate data in it for analysis so we always exported our data out
to
Excel using similar concepts to what you are discussing.  Rarely poking
a
cell at a time but often dropping the entire table out there.  Then my
friend would go in and format the data, manipulate it for summations
etc.
all with the macro recorder on.  We would then in the end dump the data
and
have Access run the macros that he created.  It worked very well!

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/ 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark Breen
Sent: Tuesday, November 23, 2004 3:18 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Linking to an excel spreadsheet


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