[AccessD] Export to Excel

Gustav Brock gustav at cactus.dk
Mon Feb 10 10:35:00 CST 2003


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.




More information about the AccessD mailing list