Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2011

    Unanswered: Automate Getting to MS Query Wizard


    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!

    Thanks for your help!

  2. #2
    Join Date
    Sep 2009

    There are two ways to automate a query in vba.

    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
    Select Connections
    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts