Command to run  Access 2010  queries  and export results to Excel 2010 files

thao-nhi used Ask the Experts™
I'm running Access 2010. What I want to achieve is:

1. Build a command to run a number of queries in Access.
2. Save the query results in Excel files and store them into separate folders on a network directory. (File names will not change)
3. Having the first line in each of the Excel files stating the query name and the date and time stamp when it was done.
ex: Query name (three spaces)  File last updated on Aug 27., 15 at 3:00 PM

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

this isn't really a question.  It is a statement of work.  Are you looking for someone to do this for you?

What are you using to control where the export file is stored?  Is one file going multiple places or is each file going to a specific place.  You probably need a table to control this.  Adding the specified string in the first row of the spreadsheet means that you will need to automate Excel although if the first column of the output file is text, you could use a union query to simply make the string a row in the export file.


This is not an expert comment!!!
 I just simply state what I want to do. Essentially, I want to know how to built a macro that will export the query results in excel format and place the file in a designated location.
Implementation Specialist
The only difficult part of this is saving the query name, date and time into the file. The default spreadsheet export won't do this. If that's not a deal-breaker then this is pretty simple:

Public Function SpreadsheetExport()
    '**Repeat for each query
    Dim query1name as String
    query1name = "Query1"
    Dim query1location as String
    query1location = "\\serverlocation"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, query1name, query1location, True
End Function

Open in new window

Then all you need is a button that calls this function in it's _click function

Private Sub Button_click()
End Sub

Open in new window

Distinguished Expert 2017

This is not an expert comment!!!
Did you read the rest of my post?  I didn't say that I wouldn't help you.  I asked questions.  I got no answers.  Just another detail - you want a Macro.  Well, a macro won't do what you want to do.  You can't use a macro to automate Excel to insert that first line.  You would need to use VBA.  You might be able to use a union query to concatenate the string with the rest of the recordset and export the union query.  In any case, without additional details, you will not get any useful response.  If you don't know how to write VBA, you won't be able to use Andrew's suggestion.

So, you need to decide how important it is to embed the string as the first line.  That is the only thing that is preventing someone from creating a macro for you (that and knowing what kind of loop to construct since we have no idea how you intend to feed the query/name info into the loop).  I don't use macros, any solution I would propose would use VBA so if it must be a macro, then the union query is your only option or you'll have to give up on having that string inserted as the first row in the spreadsheet.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

I see that you are a new member here, and you may not be aware of the rules of this site.

To add to what Pat posted;
Please know that you have actually asked three distinct questions here, not one (as the rules of this site dictate).

Each one of your questions require a different methodology to solve it, so there is no "one" real answer here.

The issue with multiple questions in one post is that it is hard to determine what the (one) "solution", was, and what post(s) actually solved it.
Let alone the confusion that arises if certain parts of your questions are answered and others are not, ...add to this the confusion that can arises from multiple miscommunications when certain parts of your questions are not clear.

If you post each of your questions as a separate question/thread, would make it easier for you to get a targeted solution each of them.

Please click the "Request Attention" link in your initial post and ask for help in restructuring this question.



Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial