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

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
Feb 15 '11 #1
4 8541
pod
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
  1.  
  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
  12.  
  13.     '...script to fill in data
  14.     xlObj.Sheets("Data").Range("a1").Value = " ... "
  15. ...
  16.  
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.