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 11 to 12 of 12

Order Replies By: Most Recent | Chronological | Highest Rated

	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

	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 frazrana on 04 Feb 2009

Back

Displaying page 2 of 2