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

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 tweedledum on 07 Jan 2009

	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