Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing Params to Embedded query programmatically | Excel Programming | |||
How to pass a date in Excel to ms query | Excel Programming | |||
How to pass a date in Excel to ms query | Excel Programming | |||
Pass Parameter to Access Query | Excel Programming | |||
Pass a filename to a query | Excel Programming |