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

Send Variables To Msquery Via Entry In Excel Cell Range?

Rate this: (3/5 from 1 vote)

	I 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 jwhitwell on 09 May 2006

Replies - Displaying 1 to 10 of 12

Order Replies By: Most Recent | Chronological | Highest Rated

	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:   A B C D 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 Excel Helper on 10 May 2006

	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 StartingToLoveExcel on 27 Jun 2006

	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 lauriepass on 11 Nov 2006

	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 Excel Helper on 11 Nov 2006

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 lauriepass on 11 Nov 2006

	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 Excel Helper on 13 Nov 2006

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 dasaltyone on 18 Jul 2007

	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 jajknight on 05 Jan 2008

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 KhabaLox on 10 Apr 2008

	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 xluser on 09 Sep 2008

Displaying page 1 of 2

Next