Programmatically pass query params
 

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.

Programmatically pass query params
 

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\FOREC AST\LargeCust\LargeCust.mdb")
Set MyDB = OpenDatabase("F:\FCST\FORECAST\LargeCust\LargeCust Info.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" wrote in message
...
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/2...-data-queries/
Daily Dose of Excel » Parameters in Excel external data queries
by Nick Hodge

--
Regards,
Tom Ogilvy


"G" wrote in message
...
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.

Programmatically pass query params
 

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" wrote in message
...


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\FOREC AST\LargeCust\LargeCust.md
b")
Set MyDB = OpenDatabase("F:\FCST\FORECAST\LargeCust\LargeCust Info.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" wrote in message
...
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/2...-data-queries/
Daily Dose of Excel » Parameters in Excel external data queries
by Nick Hodge

--
Regards,
Tom Ogilvy


"G" wrote in message
...
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.