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!
There are two main options to access the MS Query Wizard more efficiently.
Create a quick access menu item by choosing Office Button > Excel Options > Customize > Data Tab > add the Get External Data From Other Sources item > OK. You can also click Options when the Data Source dialogue appears to provide a path to the database in order to speed up access when opening this in the future.
Create a VBA macro that executes all of the steps required. You can record the steps in a blank workbook and then save the workbook as an Excel add-in. The add-in can then be made accessible at all times in Excel.