Export from Access to Excel

[naiku]

I have set up a macro to export data from an Access query into an Excel spreadsheet. I want the query to be run once a month, and for it to append the data in the spreadsheet. I saved the query as an append query, which appends the table that is then exported into Excel.

However when I export to Excel I lose all the formatting from the spreadsheet, is there a way that I can export and append directly from the Access query into Excel? thereby saving the formatting.

 

[aicatech]

What formatting do you lose?, if it is date/time it could be your PCs regional settings. Have you tried linking the spreadsheet and including it as a table in the database. Use file>get external data>link tables.

 

[naiku]

It's all formatting, text sizes, font style etc. Can't link the spreadsheet as a table in the database as the people who the report is for want it in Excel. Access is just used to manipulate data downloaded from an AS400 database.

 

[WildHare]

Are you doing this with EXPORT, TransferSpreadsheet, or some kind of automated Cut and Paste guy?

There is an option to retain formatting when you send a query or table to Excel using the EXPORT process off the FILE menu. I just exported a cross tab table with Currencies, Date, Number and Text guys, and they all went out fine.

Jim



Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[naiku]

It's using a Macro "Output To" command. Will take a look at it today and see about those options you mention Jim.