Automate Getting to MS Query Wizard

PraveshG81

New member
Joined
Oct 6, 2011
Messages
22
Reaction score
0
Points
0
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!


 

PraveshG81

New member
Joined
Oct 6, 2011
Messages
22
Reaction score
0
Points
0
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.
 

gsnidow

New member
Joined
Aug 30, 2011
Messages
38
Reaction score
0
Points
0
Location
Virginia
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.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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.)
 

gsnidow

New member
Joined
Aug 30, 2011
Messages
38
Reaction score
0
Points
0
Location
Virginia
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
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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.
 

JoePublic

Super Moderator
Staff member
Joined
Sep 16, 2011
Messages
222
Reaction score
1
Points
18
Location
UK
Excel Version(s)
2016
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.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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. :(
 

JoePublic

Super Moderator
Staff member
Joined
Sep 16, 2011
Messages
222
Reaction score
1
Points
18
Location
UK
Excel Version(s)
2016
I just used:
Code:
Shell "MSQRY32.exe"
which worked fine for me. :)
 

JoePublic

Super Moderator
Staff member
Joined
Sep 16, 2011
Messages
222
Reaction score
1
Points
18
Location
UK
Excel Version(s)
2016
Have no fear - the ensuing DDE will more than make up the complexity levels...
 
Top