Exporting MS Access to Ms Excel with Excel formatting

[Dave Smith]

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

[pod]

I sometimes use an excel template, meaning a file that has all the formatting I want, charts and all. In my script I make a copy of this Excel template and save it as a different name and location, then start filling it with data. No formatting required since it is already formatted.

 
    Dim reportfolder As String
    reportfolder  = "C:\tmp"
    Dim fichier As String
    fichier  = "newreport.xls"
    Dim xlObj As Object    
    Set xlObj = CreateObject("Excel.application")
    Dim fso As Object    
    Set fso = CreateObject("Scripting.FileSystemObject")
    fso.CopyFile Application.CurrentProject.Path & "\templates\report_template.xls", reportfolder & fichier, True
    xlObj.Workbooks.Open reportfolder & fichier
 
    '...script to fill in data
    xlObj.Sheets("Data").Range("a1").Value = " ... "
...
 

[Dave Smith]

Thats a good idea, let me give that a try

[Dave Smith]

Thanks POD

[CD Tom]

Dave, did you get this to work? I've tried using it but when I go to fill in the data I get an subscript out of range. I've posted a question with my code but haven't received an answer.

** Edit - NeoPa **
While it is ok to request help and link to another thread with a similar question, it is not ok to divert an existing thread away from the needs of the original question. As such, I felt this could be left as is, as long as I add a link to CD Tom's other thread (Export data from Access to Excel) and make it clear that any responses to this new question in this thread are not welcome.