Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Automate Getting to MS Query Wizard

  1. #1

    Automate Getting to MS Query Wizard



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,

    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
    Acolyte gsnidow's Avatar
    Join Date
    Aug 2011
    Location
    Virginia
    Posts
    38
    Articles
    0
    Pravesh, check out this link. A little VBA will make light work of this sort of thing. http://www.exceltip.com/st/Import_da...Excel/428.html

    Good luck.

    Greg

  3. #3
    thank you for this but i am not sure how i should tweak this for my need. the path for my file is: C:\Documents and Settings\pgoyal\Desktop\HFR_Access.mdb.

    Also, from what I can tell, this macro will dump my data into excel, but what i would like to do is have excel connect to the access database and pop up the MS Query Wizard so i can manipulate my data and then have my results shown in a worksheet.

  4. #4
    Acolyte gsnidow's Avatar
    Join Date
    Aug 2011
    Location
    Virginia
    Posts
    38
    Articles
    0
    Pravesh
    I have not heard of being able to use the wizard with DAO. However, you can execute your SQL statement(INSERT, UPDATE, DELETE), then return your results to your spreadsheet. If you are certain you want/need to use the wizard, probably the best thing to get started would be for you to record a macro doing it manually, then look at the VBA generated.

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    gsnidow, recording a macro when opening MS Query unfortunatley doesn't recturn anything.

    Pravesh, I've taken a look, but I can't find the method to call up MSQuery via VBA. (There must be one, but I can't see it in the Excel Object Model, and Google isn't helping me much either.)
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #6
    Acolyte gsnidow's Avatar
    Join Date
    Aug 2011
    Location
    Virginia
    Posts
    38
    Articles
    0
    Just as I suspected Ken (without trying it myself). Sounds like a great opportunity for Pravesh to learn some VBA and SQL. Who needs those pesky wizards anyway?

    Greg

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    Yes, Greg, maybe. I guess my feeling though, is that if the MS Query wizard will do the job for him, why shouldn't Excel just let us open it. I mean, should an Excel jockey really HAVE to learn SQL to do their stuff?

    Personally, I use Access's query design mode to generate my SQL when I can't work it out myself, but I've never really been afraid to roll up my sleeves and get a little dirty with this stuff. But at the end of the day, I wouldn't count myself as normal so much... I like to know how this stuff works. There's a lot of people (probably the majority) who just want to get the job done, and really don't want to have to learn all the different languages we work with daily.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    170
    Articles
    0
    MSQuery is a separate program (MSQRY32.Exe) and doesn't appear to expose its objects to VBA. You can communicate with it using DDE (see here for examples), but in all honesty I think it's easier just to start it yourself.
    Note: if you do try those examples, I think you will need to shell the executable before you attempt to initiate the DDE channel or you will get an error.

  9. #9
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    I figured that I'd give that a go to see if I could even open it by shelling it out using the following code:

    Code:
    Private Declare Function ShellExecute _
                              Lib "shell32.dll" _
                                  Alias "ShellExecuteA" ( _
                                  ByVal hwnd As Long, _
                                  ByVal lpOperation As String, _
                                  ByVal lpFile As String, _
                                  ByVal lpParameters As String, _
                                  ByVal lpDirectory As String, _
                                  ByVal nShowCmd As Long) _
                                  As Long
    Sub OpenQuery()
        ShellExecute 0, "Open", "C:\Program Files (x86)\Microsoft Office\Office12\MSQRY32.EXE", "", "", 0
    End Sub
    Unfortunatley a no-go. I use this exact format of the ShellExecute command in other projects, but usually to open a process completely separate from Excel. Query seems like it's still tied in, so I'm guessing that the DDE route would be necessary.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  10. #10
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    170
    Articles
    0
    I just used:
    Code:
    Shell "MSQRY32.exe"
    which worked fine for me.

Page 1 of 2 1 2 LastLast

Posting Permissions

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