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

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

Thanks
thao-nhiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
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.
0
thao-nhiAuthor Commented:
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.
0
Andy CownieImplementation SpecialistCommented:
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()
    SpreadsheetExport
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
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.
0
Jeffrey CoachmanMIS LiasonCommented:
thao-nhi,
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, ...it 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.

Thanks

JeffCoachman
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

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.