469,323 Members | 1,575 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,323 developers. It's quick & easy.

Exporting MS Access to Ms Excel with Excel formatting

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


Thanks for all your help and advice
Feb 15 '11 #1
4 8541
298 100+
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.

Expand|Select|Wrap|Line Numbers
  2.     Dim reportfolder As String
  3.     reportfolder  = "C:\tmp"
  4.     Dim fichier As String
  5.     fichier  = "newreport.xls"
  6.     Dim xlObj As Object    
  7.     Set xlObj = CreateObject("Excel.application")
  8.     Dim fso As Object    
  9.     Set fso = CreateObject("Scripting.FileSystemObject")
  10.     fso.CopyFile Application.CurrentProject.Path & "\templates\report_template.xls", reportfolder & fichier, True
  11.     xlObj.Workbooks.Open reportfolder & fichier
  13.     '...script to fill in data
  14.     xlObj.Sheets("Data").Range("a1").Value = " ... "
  15. ...
Feb 17 '11 #2
Thats a good idea, let me give that a try
Feb 22 '11 #3
Thanks POD
Feb 22 '11 #4
CD Tom
470 256MB
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.
Sep 8 '11 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Hideyuki | last post: by
1 post views Thread by David | last post: by
1 post views Thread by setterst | last post: by
2 posts views Thread by Mike P | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Gurmeet2796 | last post: by
reply views Thread by mdpf | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.