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
Angry
VARIABLE CELL REFERENCE IN MACRO TO GET EXTERNAL DATA
Rate this:
(2.6/5 from 5 votes)
VBA Code: (FIXED CRITERIA WORKS FINE. 
Sub B08DATAGL_Query()
' MACRO TO RUN WITH FIXED CRITERIA WORKS JUST FINE
' B08DATAGL_Query Macro
' Rebuilds MSQuery for DATAGL Tab.
'
'
Columns("A:D").Select
    Selection.ClearContents
    With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=MABODBC;DB=TSCFIN;SRVR=SERVER1;UID=MABODBC;PWD=MABODBC", Destination _
        :=Range("A3"))
        .CommandText = Array( _
        "SELECT GLACC_PER.COM_CODE, GLACC_PER.PERIOD_NO, GLACC_PER.CLOSE_BAL, GLACC_PER.ACCOUNT" & Chr(13) & "" & Chr(10) & "FROM SYSADM.GLACC_PER GLACC_PER" & Chr(13) & "" & Chr(10) & "WHERE (GLACC_PER.COM_CODE= 'B08') AND (GLACC_PER.PERIOD_NO<=200812) AND (GLACC_P" _
        , "ER.CLOSE_BAL<>0)" & Chr(13) & "" & Chr(10) & "ORDER BY GLACC_PER.PERIOD_NO DESC")
        .Name = "DATAGLQUERY1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = True
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub





VBA Code: (vARIABLE CRITERIA GIVES COMPILE ERROR:  Expected: list seperator or )
Sub B08DATAGL_Query_VARIABLE()
' MACRO TO RUN WITH VARIABLE CRITERIA DOES NOT WORK
' B08DATAGL_Query Macro
' Rebuilds MSQuery for DATAGL Tab.
'
Dim ComCode As Variant
Dim Transdate As Date
ComCode = Range("E2").Text
Transdate = Range("F2").Value

Columns("A:D").Select
    Selection.ClearContents
    With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=MABODBC;DB=TSCFIN;SRVR=SERVER1;UID=MABODBC;PWD=MABODBC", Destination _
        :=Range("A3"))
        .CommandText = Array( _
        "SELECT GLACC_PER.COM_CODE, GLACC_PER.PERIOD_NO, GLACC_PER.CLOSE_BAL, GLACC_PER.ACCOUNT" & Chr(13) & "" & Chr(10) & "FROM SYSADM.GLACC_PER GLACC_PER" & Chr(13) & "" & Chr(10) & "WHERE (GLACC_PER.COM_CODE= "&ComCode&" ) AND (GLACC_PER.PERIOD_NO<="&Transdate&") AND (GLACC_P" _
        , "ER.CLOSE_BAL<>0)" & Chr(13) & "" & Chr(10) & "ORDER BY GLACC_PER.PERIOD_NO DESC")
        .Name = "DATAGLQUERY1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = True
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub


Hi All,
Using the code posted in this thread a few years ago I made some changes to my VBA code.
My purpose was to change the fixed criteria Macro to a variable criteria that is a Cell reference in excel sheet. However when I do that i get Compiling error:  Expected: list seperator or )
I get the error where I have typed the variable criteria formula  { &" ) AND (GLACC_PER.PERIOD_NO<=" }  
I am not good with VBA if you can help it would be appreciated.

Thanks heaps-  [email protected] 
 Fraz- [email protected]
 Posted by on
Confused
438 Error
Rate this:
(3/5 from 1 vote)
Hi
I tried running the code above with just the database definitions changed and i keep getting a 438 error : OBject doesnt support this property or method. I assume this means i'm using a method in a way i shouldn't, as i said i only changed the database definition. I have never used VB before i just had to use it with excel

it seems to be the line

With ActiveSheet.QueryTables.Add(Connection:=Array("ODBC;DSN=**************;Description=*****************;APP=Microsoft Office XP;DATABASE=*****************;Trusted_Connection=YES"), Destination:=Range("B5").CommandText = Array("SELECT * FROM *****.****.*************, ********.************ WHERE **************='val'"))

The *** are obviously datatbase stuff i cant post on the net. Other than that it seems to be the same, any ideas??
 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
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
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
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
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
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
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
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
 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: