Programmatically export query results to Excel

Posted on 2008-06-10
Medium Priority
Last Modified: 2012-06-27
I'm trying to figure out how to programmatically export data from Access to Excel, create a chart in Excel and then copy that to Word.  I've been performing this operation manually and It's no big deal, but the users want to be able to push a button.  Here's the scenario:
At month end, five chart reports are generated from a large Access customer service database.  Upper management wants to copy and paste these charts to their PDAs from Word.  I haven't been able to copy Access charts to Excel or Word.  So, I run the Access queries, copy the results table to Excel, generate an Excel chart and copy that chart to Word.  I'm sure this process could be automated, but I haven't a clue where to start.
Any ideas?  Anybody done anything like this before or seen it done?
Question by:NaughtyZute
  • 2
  • 2
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 1500 total points
ID: 21751233
to export data to excel

   docmd.transferSpreadsheet acexport, acSpreadsheetTypeExcel9, _
        "QueryNameOrTablename", "c:\myExcel.xls", true, "nameOfSheet"

Author Comment

ID: 21769493
Thanks for the speedy reply Capricorn and I apologize for my tardy response.  As happens daily in software development, I've gotten pulled off this non-critical project to other things.  I'll try your suggestion as soon as I get a little tiime.  By what you've said then, I'll need to save the spreadsheet and then need a separate macro in Excel to generate the chart?  The user would actually be ok with pasting the charts into Word on his own.  Anyway, thanks again.
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21909797
why a grade of B?

Author Comment

ID: 21912665
I'm sorry capricorn.  I guess because you only provided me with step 1 and because I never got to try the solution or take the thing any farther.  I guess I should have done an A.  But there was a long way to go to the Excel Chart, Word Doc. etc.  Didn't mean to offend you.  Your answer was right on.  I'll change the grade if I can figure out how.

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
If you need to implement application level security in an Access database application or other VBA code, I strongly encourage you to take advantage of Active Directory groups.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question