Hi,
I'm trying to design a spreadsheet where th user will enter a few parameters in a sheet and then press a button for a query to pull data.
I have the first thing done, which is the macro for the data. But don't know how to use the parameters for it.
My code is:
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=Warehouse Elite Database;UID=SYSTEM;Trusted_Connection=Yes;APP=2007 Microsoft Office system;WSID=BO17LR9CWMDH;DATABASE=son_" _
), Array("db")), Destination:=Range("$A$1")).QueryTable
.CommandText = Array( _
"WITH X AS(" & Chr(13) & "" & Chr(10) & "SELECT tindex," & Chr(13) & "" & Chr(10) & "STUFF((SELECT ' ' + tddesc FROM son_db.dbo.timedesc WHERE tindex = t.tindex ORDER BY tdline FOR XML PATH('')),1,1,'') AS [tddesc]" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "FROM (SELECT DISTINCT tindex FROM son_d" _
, _
"b.dbo.timedesc)t" & Chr(13) & "" & Chr(10) & ")" & Chr(13) & "" & Chr(10) & "SELECT " & Chr(13) & "" & Chr(10) & " client.clnum AS 'Client'" & Chr(13) & "" & Chr(10) & ", matter.mmatter AS 'Matter'" & Chr(13) & "" & Chr(10) & ", timecard.ttk AS 'TTKID'" & Chr(13) & "" & Chr(10) & ", timekeep.tklast + ', ' + timekeep.tkfirst AS 'Timekeeper'" & Chr(13) & "" & Chr(10) & ", timekeep.tktitle AS 'T" _
, _
"itle'" & Chr(13) & "" & Chr(10) & ", x.tindex AS 'Index'" & Chr(13) & "" & Chr(10) & ", timecard.tworkdt AS 'WorkedDate'" & Chr(13) & "" & Chr(10) & ", timecard.tworkhrs AS 'Hours'" & Chr(13) & "" & Chr(10) & ", timecard.tworkdol AS 'WorkedAmount'" & Chr(13) & "" & Chr(10) & ", timecard.tstatus AS 'Status'" & Chr(13) & "" & Chr(10) & ", timecard.tinvoice AS 'Invoice" _
, _
"'" & Chr(13) & "" & Chr(10) & ", matter.mcurrency AS 'Currency'" & Chr(13) & "" & Chr(10) & ", x.tddesc AS 'Description'" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "FROM " & Chr(13) & "" & Chr(10) & " X" & Chr(13) & "" & Chr(10) & ", son_db.dbo.client client" & Chr(13) & "" & Chr(10) & ", son_db.dbo.timecard timecard" & Chr(13) & "" & Chr(10) & ", son_db.dbo.matter matter" & Chr(13) & "" & Chr(10) & ", son_db.dbo.timekeep timekeep" & Chr(13) & "" _
, _
"" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "WHERE X.TINDEX = timecard.tindex" & Chr(13) & "" & Chr(10) & "AND matter.mmatter = timecard.tmatter " & Chr(13) & "" & Chr(10) & "AND client.clnum = matter.mclient " & Chr(13) & "" & Chr(10) & "AND timecard.ttk = timekeep.tkinit " & Chr(13) & "" & Chr(10) & "AND (timecard.tprntfl Not In ('R','X'))" & Chr(13) & "" & Chr(10) & "AND (time" _
, _
"card.tstatus<>'ADE, D, E, NB, NBP')" & Chr(13) & "" & Chr(10) & "AND (matter.mmatter Like 'TT-001') " & Chr(13) & "" & Chr(10) & "AND (timecard.ttk Like '%')" & Chr(13) & "" & Chr(10) & "AND (timecard.tworkdt>={ts '2012-01-01 00:00:00'}) " & Chr(13) & "" & Chr(10) & "AND (timecard.tworkdt<={ts '2012-01-31" _
, " 00:00:00'})")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query_from_Warehouse_Elite_Database4"
.Refresh BackgroundQuery:=False
End With
Range("A2").Select
So where it says:
AND (matter.mmatter Like 'TT-001')
I want to be able to reference a cell in a sheet called "Parameters", for example cell D4,
the same with:
AND (timecard.tworkdt>={ts '2012-01-01 00:00:00'}) " & Chr(13) & "" & Chr(10) & "AND (timecard.tworkdt<={ts '2012-01-31" _
, " 00:00:00'})")
I don't want the dates to be fixed but to reference two cells in the "parameter" sheet.
I tried to do the parameters through the Data section, but because this query can't be shown graphically, microsoft query will not let me use the parameter option in excel.
Any ideas?
Any help will be appreciated.
Thanks.
Bookmarks