Exporting MS Access to Ms Excel with Excel formatting

[TBC]

I’ve read a lot of different article on the internet about being able to export a table from MS Access to Ms Excel.

Problem I’m having is that I don’t know which one would work best for me, so I thought since I’ve gotten such great advice from this form I will ask.

I have a query that updates a table in access.
What I would like to have happen in the export from access to excel

Currently I have a number of queries that updates number of table daily.
Then I manual export the tables to a excel files
Then I spend 15 -20 minutes formatting the excel fine. “I format all the table to excel files that same way.
Once this is done I email it out in excel format to different managers that need to make commits and then email the spread sheet back to me then I import the spread sheets and record there comments.

If at all possible I would like all the files to import into one spread sheet just into different tabs. I have a bout 4 different group.

Below are some of the articles I’ve read about exporting from access to excel.

Looking for any advice example ect

http://www.databasejournal.com/features/msaccess/article.php/3563671/Export-Data-To-Excel.htm

http://www.vb123.com/toolshed/98docs/excelbe.htm

http://www.tek-tips.com/viewthread.cfm?qid=1201057

http://zmey.1977.ru/Access_To_Excel.htm

http://www.databaselessons.com/access-data-to-excel-2.php


Thanks for all your help and advice
TCB

 

[hbrems]

The way I used to do it:

- prepare your data in a query
- export the data to an Excel file (you can also do this with vba - check out Docmd.OutputTo)
- open the Excel file with VBA using Excel objects (in VBA, set a reference to Microsoft Excel 12.0 Object Library).

You can browse these objects by pressing F2 in the VBA editor. Select the Excel library there and you'll find objects such a Workbook, Sheet, Range, ... etc. with methods and properties such as Open, Save, BackgroundColor, ...

Something like:

dim wb as Excel.Workbook
set wb = Workbooks.Open("Test.xls")
Range("A1").BackgroundColor = "#345345"
wb.save

Code isn't correct, just trying to give you an idea of how it works.

 

[TBC]

Thanks hbrems, that’s a good start. Hopefully we hear other ways also and find the best one that works

Thanks all
TCB