Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Table and Field of Query by Cell value
Hi All........
I have a macro that runs a Query and works fine for what it is. The problem is that the Table and Fields to be included in the query are hard coded into the macro and changing them means changing the macro. I don't want to do that.....I want to just change a list in Excel.....say in column X. I've tried to replace WOE.WONUM (WOE is the table name and WONUM is the fieldname) with Range ("X2").value where WOE.WONUM is entered in X2, but it does not work. Here's the complete macro.....any help would be much appreciated. Sub GetNewData() Dim DT1 As String, DT2 As String DT1 = Format(Range("e4").Value, _ "YYYY-MM-DD 00:00:00") DT2 = Format(Range("e5").Value, _ "YYYY-MM-DD 00:00:00") 'Call the prep macros Call ClearField 'Runs the macro to clear the old data field Call DeleteCFcolumnJ 'Runs the macro to delete the old Conditional Formatting from col J 'Run the Query With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DSN=MP2PROD;Description=DSN for MP2 MSSQL 6.0;UID=mp2reader;PWD=reader1;APP=Microsoft Office XP;WSID=CROBER-XP;DATABASE=MP2PROD" _ ), Array(";Network=DBMSSOCN")), Destination:=Range("A8")) .Sql = Array( _ "SELECT WOE.WONUM, WO.WOTYPE, WOE.WODATE, WOE.CRAFT, WOTYPE.DESCRIPTION, WO.ASSIGNEDTO, WOCRAFT.ESTLABORHOURS, WOE.REGHRS, WOE.OTHRS" & Chr(13) & "" & Chr(10) & "FROM MP2PROD.dbo.WO WO, MP2PROD.dbo.WOCRAFT WOCRAFT, MP2PROD.dbo.WOE WOE, MP2P" _ , _ "ROD.dbo.WOTYPE WOTYPE" & Chr(13) & "" & Chr(10) & "WHERE WO.CLOSEDATE = WOCRAFT.CLOSEDATE AND WO.CLOSEDATE = WOE.CLOSEDATE AND WO.SITEID = WOCRAFT.SITEID AND WO.SITEID = WOE.SITEID AND WO.WONUM = WOE.WONUM AND WO.WONUM = WOCRAFT" _ , _ ".WONUM AND WO.WOTYPE = WOTYPE.WOTYPE AND WOCRAFT.CLOSEDATE = WOE.CLOSEDATE AND WOCRAFT.CRAFT = WOE.CRAFT AND WOCRAFT.SITEID = WOE.SITEID AND WOCRAFT.WONUM = WOE.WONUM AND ((WOE.WODATE={ts '" & DT1 & "" _ , _ "'} And WOE.WODATE<={ts '" & DT2 & "'}))" & Chr(13) & "" & Chr(10) & "ORDER BY WOE.WODATE, WOE.WONUM" _ ) .FieldNames = False 'True .RefreshStyle = xlInsertDeleteCells .RowNumbers = False .FillAdjacentFormulas = False .RefreshOnFileOpen = False .HasAutoFormat = True .BackgroundQuery = True .TablesOnlyFromHTML = True .Refresh BackgroundQuery:=False .SavePassword = True .SaveData = True End With 'sort the new data field Range("a8").Select Selection.Sort Key1:=Range("C8"), Order1:=xlAscending, Key2:=Range("A8") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom Range("A3").Select 'Call the completion macros Call PrepOThrs 'Runs the macro to "prepare" the OTHRS field by replacing blanks with zeros Call AddFormulas 'Runs the macro to restore the formulas to columns I and J Call AddCFcolumnJ 'Runs the macro to restore Conditional Formatting to column J Call SetColumnWidths 'Runs the macro to establish proper column widths Range("A8").Select End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Query on Group field in Pivot Table | New Users to Excel | |||
Field Select in a Pivot Table | Excel Discussion (Misc queries) | |||
Pivot Table Data Field Query | Excel Worksheet Functions | |||
How do I select on a SQL Datetime field in Excel Query? | Excel Programming | |||
Workbook Open macro/& Pivot Table Calculated field query | Excel Programming |