[AccessD] Export to Excel

Mark Boyd MarkBoyd at McBeeAssociates.com
Tue Feb 11 08:23:18 CST 2003


I would like to thank everyone for their very helpful replies to my
post.
Although, since yesterday my boss has pulled me off that project.  I
forwarded all of your responses to the programmer who has been elected
to proceed with the project.

Thanks again,
Mark

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

Arthur,

In fact I do this stuff all of the time.  One of my insurance clients
gets
data sent to them in a spreadsheet, usually on a floppy.  Since they
name it
crazy things like Ret65.Jul.Aug.Sept.Oct.Nov.Dec 2003.xls I use a
FileFind
to get the file name / path from the user, then use the file system
object
to move it to a fixed directory on the hard disk and rename it:
"X:\Bpdb\Retiree65.xls.  Since the file name is now the same thing every
month, the file can be simply linked  one time and the link always
works.

I then build a query that pulls the columns I need, aliased to a fixed
name
in my base (atomic I think you call it?) query.  And off we go.  I also
usually do cleanup in the base query - removing formatting from phone
numbers etc.

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 Arthur Fuller
Sent: Monday, February 10, 2003 12:31 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Export to Excel


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

_______________________________________________
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