[AccessD] Export to Excel

Arthur Fuller artful at rogers.com
Mon Feb 10 11:32:01 CST 2003


Thanks to Gustav's lead on this, I wrote a tiny app that everyone at ETS
used, that attaches an Excel sheet residing on a server and lets them do
price lookups for say weekend flights from Calgary to Denver. When contracts
with airlines change, one spreadsheet is updated and everyone everywhere is
updated. Cool solution! Thanks, Gustav!
In fact, I didn't even go to named ranges. I chose several worksheets in a
single file: Low season Northbound, Low Season Southbound, High Season North
and South, and Specials (discounted rates to cities where the airline in
question was trying to increase business on said flight). I just grabbed
stuff with parameters (i.e. origin = Calgary, destination = Phoenix, flight
date = 03/03/2003).
It was a no-brainer. Thanks to Gustav, I wrote it in a morning.
1. Take any populated spreadsheet at hand.
2. Create a test MDB. File/Get External Data/file type= XLS, grab the sheet,
point to stuff, click OK.
3. Create the forms to talk to said attached tables.

-----Original Message-----
From: accessd-admin at databaseadvisors.com
[mailto:accessd-admin at databaseadvisors.com] On Behalf Of John W. Colby
Sent: February 10, 2003 11:42 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Export to Excel

>An additional trick is to use named ranges in the Excel sheet. These can be
attached as tables to the Access app which means that you can write directly
to these from your query or code in Access.

Oooooohhhhhh.  Tell us more!

I am basically Excel ignorant.  Can you publish a little demo of how to do
this?

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-admin at databaseadvisors.com
[mailto:accessd-admin at databaseadvisors.com]On Behalf Of Gustav Brock
Sent: Monday, February 10, 2003 11:35 AM
To: Mark Boyd
Subject: Re: [AccessD] Export to Excel


Hi Mark

I can recommend this method too which I learned from JC and am him
forever grateful. It can run completely unattended if you like: Excel
may run hidden in the background.

The smart part is that the code (macro) to format the sheet runs in
Excel which make it very easy to debug and refine with a set of test
data. Then you concentrate your Access app on supplying the raw data.

An additional trick is to use named ranges in the Excel sheet. These
can be attached as tables to the Access app which means that you can
write directly to these from your query or code in Access.

/gustav


> John -

> Thanks for the reply.

> This may be something we can work with.

> I'll run it by my boss, but I'm guessing there will be complaints about
> too much user interaction.

> The thing that drives me crazy is there isn't that much interaction on
> the user's part.  They have to move a few fields around, or re-total a
> field here and there ... not a big deal if you ask me.


> -----Original Message-----
> From: John W. Colby [mailto:jcolby at colbyconsulting.com]
> Sent: Monday, February 10, 2003 10:28 AM
> To: accessd at databaseadvisors.com
> Subject: RE: [AccessD] Export to Excel

> I think you are taking the long way around the farm.  I did a lot of
> movement of data between Access and Excel down in Mexico for an app
> where the users used Excel Analysis on the resulting data.  What we did
> is to build queries that got the data that was desired, then exported
> that data directly into the spreadsheet.  You can then record a macro on
> the spreadsheet as you format the data the way you want it to run.  Save
> and name the macro.  Now, whenever you need to run this process, Copy a
> "template" spreadsheet that has this macro in it to a new name, export
> the data into the new spreadsheet, then run the macro from Access to
> format the data the way you want to see it.

_______________________________________________
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




More information about the AccessD mailing list