I use MS Query Wizard in excel to manipulate data based out of a MS Access File. The process is currently manual, I go to the Excel >> Data Ribbion > Get External Data >> From Other Sources >> From Microsoft Query > Check the "Use the Query Wizard to create/edit queries" >> Select MS Access Database* (in the Databases tab) >> find the file located in a specific path >> hit OK and then the wizard pops up!
Is there anyway to AUTOMATE this whole process? I would like to click a button on the Ribbon which does all this in the background and when it done pops up with the Wizard so i can build my query!
after setting up your query just add the following code to the macro you are using.
It runs the query and puts the data where you told it to go. If you really want to automate the process you will need to do the following
Select the data tab
In Workbook connections highlight each data file you are importing in turn
and click the properties button.
Select the usage tab and deselect "Prompt for File name on refresh".
The only thing to remember is that you have to be disciplined about the names of the data files you are using and where you locate them or the macro will crash. You will only be able to use the file names and locations already in the query.
The 2nd method is to turn on the macro recorder and set up your query. Turn off the macro recorder when finished and just run the macro.
I have used both methods and both work without any problems.