Re: Programmatically pass query params

[Tom Ogilvy]

in the code in the Where statement, make the hard coded value a variable.

or use a parameter query
See this presentation:
http://www.dicks-blog.com/archives/2004/12/13/parameters-in-excel-external-data-queries/
Daily Dose of Excel » Parameters in Excel external data queries
by Nick Hodge

--
Regards,
Tom Ogilvy

"G" <the...@yahoo.com> wrote in message
news:%23WYfSMO...@TK2MSFTNGP14.phx.gbl...
> Trying to run a query several times, each time with a new parameter. The
> query can be easily refresh by selecting the cell and background
refreshing
> the query, as I found by recording a new macro. The macro record does not
> capture the change to the cell that contains the param.
>
> Any ideas?
>
> THanx.
>
>

[G]

[G]

Tom,

Thanks for the response. I read thru the link and the examples. I copied and
pasted a revelant code snippet below. Are you saying that it's easier just
to write a DAO/ADO procedure and do the basic: create connection, open
connection, build sql statement with desired variables, then dump results
strategy.

I have done this kind of thing several times in ASP pages, but I am not sure
how to get the results into excel once the recordset is filled. I am more
familiar with iterating thru each record and dumping the results to and html
table.

ANy ideas?

Thanx

G

Sub Load_Cus_Data()

Dim i As Integer, x As Integer, yearmo As Integer
Dim dtMonth As Integer
Dim dtYear As Integer
' Dim dtDivision As String
Dim MyColumn As Integer
Dim MyDB As Database
Dim MyQueryDef As QueryDef
Dim rsRecSet As Recordset
' Dim strYear As String
Dim strDivision As String
' Dim strRec As String

Dim dtBUnit As Integer
Dim xKWH, xCUS, xREV As Integer
Dim OpFlag As String

'Get Month and Year data from worksheet MACRO tab
dtMonth = Sheets("Macros").Range("d5?)
dtYear = Sheets("Macros").Range("d6?)
' calculate row offset for selected year and month
yearmo = (dtYear - 1993) * 12 + 13 + dtMonth
' strYear = CStr(dtYear)
' Prevent recalculation every time a new data point is added, until all the
data is loaded
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
'Open the database
' Set MyDB =
OpenDatabase("\\NYSKGOSHARED01\KIRKWOOD\FCST\FORECAST\LargeCust\LargeCust.mdb")
Set MyDB = OpenDatabase("F:\FCST\FORECAST\LargeCust\LargeCustInfo.mdb")

'Assign MyDB to QryLrgCust
For i = 0 To MyDB.querydefs.Count - 1
If MyDB.querydefs(i).Name = "QryLrgCust3? Then
Exit For
End If
Next
Set MyQueryDef = MyDB.querydefs(i)
MyQueryDef.Parameters(0).Value = dtYear
MyQueryDef.Parameters(1).Value = dtMonth
Set rsRecSet = MyQueryDef.OpenRecordset
While Not rsRecSet.EOF
'select WORKSHEET based on cost area name in database record(2)
strDivision = rsRecSet(2)
OpFlag = Right(rsRecSet(8), 3)
If OpFlag = "O ?" Then
OpFlag = "BRQ"
End If
Worksheets(strDivision).Activate
' Let the user know where you are
Application.StatusBar = strDivision
For MyColumn = 4 To 81
'When cell matches for RevCl, Perm_Ref and RateCode, enter MWh data
If rsRecSet(3) = ActiveSheet.Cells(6, MyColumn) And _
rsRecSet(4) = ActiveSheet.Cells(3, MyColumn) And _
rsRecSet(5) = ActiveSheet.Cells(7, MyColumn) Then
If ActiveSheet.Cells(yearmo, MyColumn).Formula = "=NA()" Then
ActiveSheet.Cells(yearmo, MyColumn).Formula = "=" & _
(rsRecSet(6) / 1000) & "*" & OpFlag
Else
If rsRecSet(6) > 0 Then
ActiveSheet.Cells(yearmo, MyColumn).Formula = _
ActiveSheet.Cells(yearmo, MyColumn).Formula & "+" & _
(rsRecSet(6) / 1000) & "*" & OpFlag
End If
If rsRecSet(6)

"Tom Ogilvy" <twog...@msn.com> wrote in message
news:uIL$zmOHFH...@TK2MSFTNGP14.phx.gbl...

[Tom Ogilvy]

No, I never mentioned ADO/DAO, but that is certainly an option. You said
you had recorded a macro.

Anyway, for getting your recordset into excel:
in xl2000 and later, look at copyfromrecordset in excel vba help.

--
Regards,
Tom Ogilvy

"G" <the...@yahoo.com> wrote in message

news:uFIeNfRH...@TK2MSFTNGP10.phx.gbl...