I
info
I use Excel Macros to search databases using ODBC. I have a macro I
wrote that performs queries on 4 different databases with the same
search criteria. Currently, the macro runs each query separately and
prompts the user 4 times to enter the same start and end date and
salesman number. How can I save the values and pass them from the
first query to the next 3 without the user having to enter them 4
times? This is the SQL code from the firat query. The 3 question
marks are the prompted criteria.
SELECT CMASCP.CPCUS, CMASTR.CU_NME, CMASCP.CPDIV, CMASCP.CP_CID,
CMASCP.CPCDTE, CMASCP.CPCSMN, CMASCP.CPTYP, CMASCP.CPCDET,
CMASCP.CPSEQ
FROM S10BB89B.QS36F.CMASCP CMASCP, S10BB89B.QS36F.CMASTR CMASTR
WHERE CMASCP.CPCUS = CMASTR.CU_CUS AND CMASCP.CPDIV = CMASTR.CU_DIV
AND CMASCP.CPCO = CMASTR.CU_CO AND ((CMASCP.CPCDTE Between ? And ?)
AND (CMASCP.CPDIV=?))
This is the macro code that calls the query.
Workbooks.Add
'insert blank worksheet
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Documents and Settings\gvarianides\Application Data
\Microsoft\Queries\calldetail.dqy" _
, Destination:=Range("A1"))
.Name = "calldetail"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
'run call query
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
I know I can prompt for the criteria in my macro and save them in 3
different variable names. What I don't know how to do is pass these
variables to the SQL code in the queries.
Thanks
wrote that performs queries on 4 different databases with the same
search criteria. Currently, the macro runs each query separately and
prompts the user 4 times to enter the same start and end date and
salesman number. How can I save the values and pass them from the
first query to the next 3 without the user having to enter them 4
times? This is the SQL code from the firat query. The 3 question
marks are the prompted criteria.
SELECT CMASCP.CPCUS, CMASTR.CU_NME, CMASCP.CPDIV, CMASCP.CP_CID,
CMASCP.CPCDTE, CMASCP.CPCSMN, CMASCP.CPTYP, CMASCP.CPCDET,
CMASCP.CPSEQ
FROM S10BB89B.QS36F.CMASCP CMASCP, S10BB89B.QS36F.CMASTR CMASTR
WHERE CMASCP.CPCUS = CMASTR.CU_CUS AND CMASCP.CPDIV = CMASTR.CU_DIV
AND CMASCP.CPCO = CMASTR.CU_CO AND ((CMASCP.CPCDTE Between ? And ?)
AND (CMASCP.CPDIV=?))
This is the macro code that calls the query.
Workbooks.Add
'insert blank worksheet
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Documents and Settings\gvarianides\Application Data
\Microsoft\Queries\calldetail.dqy" _
, Destination:=Range("A1"))
.Name = "calldetail"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
'run call query
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
I know I can prompt for the criteria in my macro and save them in 3
different variable names. What I don't know how to do is pass these
variables to the SQL code in the queries.
Thanks