Home > Forum Home > Automating Data Analysis Tasks > Send variables to MSQuery via entry in Excel cell range? Share

Send variables to MSQuery via entry in Excel cell range?

Excel Help for Send Variables To Msquery Via Entry In Excel Cell Range? in Automating Data Analysis Tasks


Forum TopicPost Reply Login

Send Variables To Msquery Via Entry In Excel Cell Range?

Rate this:
(3/5 from 1 vote)
ShockedI do a lot of data query with Excel, and am running into projects where it is impractical to continue because of query variables that change.  Currently this requires that I open the query, manually chance the variable/criteria, refresh, return to Excel, print, then start over again!

Specifically I'm pulling information for sales usage by a specific salesperson and date range.  If there are 40 salespersons, this requires editing the MSQuery 40 times to get the data!

What I'd like to do is build the Excel report to include an input cell or two (today's date (via formula) and sales ID), have that data pass directly to MSQuery and return the updated data - without having to open MSQuery.

Possible?

Thanks!
 Posted by on
 
Replies - Displaying 1 to 10 of 12Order Replies By: Most Recent | Chronological | Highest Rated
Happy
Build Database Query with Parameters using Excel VBA
Rate this:
(3/5 from 6 votes)
You can dynamically build the query used by MSQuery using a VBA macro. If, for example, you had an Excel spreadsheet with the following information entered.
Excel Spreadsheet:
 ABCD
1    
2  Sales Rep: 3 
3  Sales Date: 01/01/2006 
4    
5    


You can then create a VBA macro which pulls the values in cells C2 and C3 to return sales starting in cell B5. The following example uses the Northwind DNS to get sales from the Order table, so you will have to change the connection, query and other options to suit your own purposes.

VBA Code:
Sub Sales_Query()
Dim salesrep As Variant
Dim salesdate As Date
salesrep = Range("C2").Text
salesdate = Range("C3").Value
    With ActiveSheet.QueryTables.Add(Connection:=Array( _
        "ODBC;DSN=Northwind;Description=Northwind;APP=Microsoft Office XP;DATABASE=Northwind;Trusted_Connection=YES"), Destination:=Range("B5"))
        .CommandText = Array("SELECT * FROM Orders WHERE EmployeeID=" & salesrep & " AND OrderDate > '" & salesdate & "' ORDER BY OrderDate")
        .Name = "Sales Query from Northwind"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

To insert the macro, go to Tools >> Macro >> Visual Basic Editor and Insert Module.  Once the code has been copied and modified, you can add a button or a graphic to assign the macro to.
 Excel Business Forums Administrator
 Posted by on
Surprised
Please help!
Rate this:
(3.5/5 from 2 votes)
I get the following error message when using the code above as a template.

     
Run-time error '1004'
General ODBC Error

With the following line highlighted

.Refresh BackgroundQuery:=False

From google, looks like everyone is looking for a solution to this problem.

Can someone please help!?

Thanks!


 Posted by on
Confused
Send variables to MSQuery via entry in Excel
Rate this:
(3/5 from 1 vote)
I noticed this post from back in June and I am having the same problem. I do not see any resolution posted. Did you figure it out? I am new with VBA and have a deadline on this and would like to keep going as soon as possible. Thought someone with more experience could help!! Thanks!!
 Posted by on
Shocked
Set-up Northwind DSN
Rate this:
(3/5 from 1 vote)
The VBA code in the example assumes that there is a DSN set up for the Northwind database.  Northwind is an example database that ships with Microsoft SQL Server.

To DSN must be set up (named "Northwind") to connect to an SQL Server with the default database being Northwind.  To do this go to Control Panel > Administration Tools > Data Sources (ODBC) and Add an SQL server DSN accordingly.
 Excel Business Forums Administrator
 Posted by on
Confused
Send variables to MSQuery via entry in Excel cell range?
Rate this:
(3/5 from 1 vote)
Actually, I'm using my own code, very similar though. I've posted it below. If I run it as it's own module, it runs fine and creates the rows I want. If I move it to a sub-procedure and use a true data value, it also works fine, if I use what is below where it is in a sub-procedure and uses a variable, it fails with that odbc error. The value is getting populated, I can tell from debugging, but it fails with the same error you were getting. Any ideas??

VBA Code:
Sub cboProjTypNm_Change()
'
' SearchProjNm Macro
' Macro recorded 11/10/2006 by LPasseretti
'
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DBQ=D:\Work\2-Luxottica\ResourceMgmt\Testing\ResourcePlanning\IT 2006 Resource Planning.xls;DefaultDir=D:\Work\2-Luxottica\Reso" _
        ), Array( _
        "urceMgmt\Testing\ResourcePlanning;Driver={Microsoft Excel Driver (*.xls)};DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;MaxScan" _
        ), Array( _
        "Rows=8;PageTimeout=5;ReadOnly=0;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;" _
        )), Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT ProjPlan.Category, ProjPlan.`Proj vs Act`, ProjPlan.`Proj Type/Name`, ProjPlan.Team, ProjPlan.`Team Resp`, ProjPlan.Rsce, ProjPlan.Role, ProjPlan.`Assoc Type`, ProjPlan.Month, ProjPlan.`Wk/Mo`," _
        , _
        " ProjPlan.Hours" & Chr(13) & "" & Chr(10) & "FROM ProjPlan ProjPlan" & Chr(13) & "" & Chr(10) & "WHERE (ProjPlan.`Proj Type/Name`=" & cboProjTypNm.Value & ")" _
        )
        .Name = "Query from RescPlan"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

 Posted by on
Sad
SQL query text
Rate this:
(3/5 from 1 vote)
It could be something to do with the way that the query is built with the variable.

I notice that you have Chr(10) in the SQL query and that the variable is not wrapped in single quotes.  Have you tried to output the query text to a cell and then tried this directly within the database?
 Excel Business Forums Administrator
 Posted by on
Confused
Easy solution
Rate this:
(3/5 from 1 vote)
Set up a parameter query. See "Create a parameter query" in the help file for MS Query.

If you setup the query like the above help file says it will prompt you with a message box for the parameters. If you do that once to return results to Excel you can then right click anywhere in the query results and select PARAMETERS then you can select to "Get the value from the following cell."  You can also have the query run when that cell's value changes. I'm sure there is a better way of doing all of this but this is what works for me and it will solve the problem you mentioned.

 Posted by on
Confused
sounds like something Firefly could do (maybe)
Rate this:
(3/5 from 1 vote)
you could put your parameters within the sheet config then reference them in your queries using the {cell} notation. The tool is mostly good for high level summary reports that require data from multiple sources: http://www.getfirefly.net/
 Posted by on
Confused
Passing cell references as Parameters to MS Query
Rate this:
(3/5 from 6 votes)
I had the same problem as the OP. See the posts by WillR in the link below for a description of how to set up parameters for your queries. I will try to give my explanation as well.

http://www.mrexcel.com/archive2/19400/22013.htm

Open your query in the MS Query editor. Change the hard-coded date (or whatever the data-type) to "[Parameter_Name]". When you press enter, you will be prompted to enter a value for the parameter.

Return to Excel, right click on your query and select "Parameters". Select the third radio button "Get the value from the following cell" and then enter the cell reference. You can check the box below that to have it auto-refresh when the value of the cell changes.

This should work, and is far simpler than coding it into VBA.
 Posted by on
Confused
this might be the problem, try it
Rate this:
(3/5 from 1 vote)
this might be possible due to the way you write queries in macro for example

"Select * from table where field = " & variable wont work because msquery expects single quotes for each value try

"Select * from table where field = " & "'" & variable & "'"
 ~
 Posted by on
 Displaying page 1 of 2 

Find relevant Excel templates and add-ins for Send variables to MSQuery via entry in Excel cell range? in the

© Business Spreadsheets   Legal   Follow us: