jeff06
07-30-2007, 06:34 AM
I have a module
Private Sub Query()
' Declare the QueryTable object
Dim qt As QueryTable
' Set up the SQL Statement
sqlstring = " Select "
sqlstring = sqlstring & " C.ContractNbr,C.MerchantId,M.BusinessName,C.FundingAmount,"
sqlstring = sqlstring & " C.FundedAmount,FirstFundingDate as FirstFundingDate,"
sqlstring = sqlstring & " C.CreationDate as CreationDate,GenT.Name as ContractType,"
sqlstring = sqlstring & " CSC.Name as ScorecardName,SC3.ScoreValue as SC3ScoreValue ,"
sqlstring = sqlstring & " SC3.Grade as SC3Grade,CSCT.Name as AcceptanceName,"
sqlstring = sqlstring & " SC1.ScoreValue as SC1ScoreValue,sc3.insertdate as Scoredate,"
sqlstring = sqlstring & " ADCT.Value as CreditTrend,"
sqlstring = sqlstring & " ADBS.Value as BeaconScore,"
sqlstring = sqlstring & " ADAT.Value as AverageTicket,"
sqlstring = sqlstring & " ADFA.Value as FundingAmt,"
sqlstring = sqlstring & " ADMC.Value as MCCV,"
sqlstring = sqlstring & " ADNH.Value as NegativeHistory,"
sqlstring = sqlstring & " ADOB.Value as OpenBankruptcies,"
sqlstring = sqlstring & " ADPFC.Value as PublicFilings,"
sqlstring = sqlstring & " ADPR.Value as Processor,"
sqlstring = sqlstring & " ADRD.Value as RevolvingDebt,"
sqlstring = sqlstring & " ADTIB.Value as TIB,"
sqlstring = sqlstring & " ADRS.Value as TransactionPct,"
sqlstring = sqlstring & " ADTl.Value as TotalTaxLiensAmount,"
sqlstring = sqlstring & " ADSR.Value as SalesRep,"
sqlstring = sqlstring & " ADSC.Value as SICCode,"
sqlstring = sqlstring & " ADTU.Value as Turn,"
sqlstring = sqlstring & " ADRA.Value as RentRatio,"
sqlstring = sqlstring & " ADCP.Value as ContractsPurchased,"
sqlstring = sqlstring & " ADSalT.Value as SalesRepType,"
sqlstring = sqlstring & " ADRawData.TransRisk as TransRisk,"
sqlstring = sqlstring & " ADMCCVVar.Value as MCCVVariance"
sqlstring = sqlstring & " From Cnt_Contracts C"
sqlstring = sqlstring & " join CNT_v_ContractsReports rep on c.contractid = rep.contractid"
sqlstring = sqlstring & " join gen_types GenT On C.contracttypeid = gent.typeid"
sqlstring = sqlstring & " join Mrc_merchants M On C.MerchantId = M.MerchantId"
sqlstring = sqlstring & " JOIN CSC_v_AllMrcScoreCardUsed_by_Contract sc3 on c.contractid = sc3.contractid and"
sqlstring = sqlstring & " sc3.creditscorecardid in (8,9,10,11)"
sqlstring = sqlstring & " Join CSC_Scorecards CSC On sc3.creditscorecardid = csc.creditscorecardid"
sqlstring = sqlstring & " left Join Gen_Types CSCT On Sc3.AcceptanceTypeId = CSCT.TypeId"
sqlstring = sqlstring & " left join CSC_v_AllMrcScoreCardUsed_by_Contract SC1 On C.Contractid = SC1.Contractid and"
sqlstring = sqlstring & " SC1.creditscorecardid = 4"
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADTIB On ADTIB.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADTIB.FieldId = 17 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADPFC On ADPFC.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADPFC.FieldId = 18 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADRD On ADRD.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADRD.FieldId = 19 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADRS On ADRS.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADRS.FieldId = 20 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADMC On ADMC.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADMC.FieldId = 21 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADCT On ADCT.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADCT.FieldId = 22 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADBS On ADBS.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADBS.FieldId = 23 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADTl On ADTl.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADTl.FieldId = 24 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADOB On ADOB.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADOB.FieldId = 25 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADNH On ADNH.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADNH.FieldId = 26 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADZC On ADZC.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADZC.FieldId = 28 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADSR On ADSR.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADSR.FieldId = 29 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADSC On ADSC.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADSC.FieldId = 30"
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADST On ADST.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADST.FieldId = 31 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADFA On ADFA.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADFA.FieldId = 32 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADFM On ADFM.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADFM.FieldId = 33 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADAT On ADAT.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADAT.FieldId = 34 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADTU On ADTU.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADTU.FieldId = 35 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADPR On ADPR.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADPR.FieldId = 36 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADRA On ADRA.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADRA.FieldId = 37 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADCP On ADCP.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADCP.FieldId = 38 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADSalT On ADSalT .AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADSalT.FieldId = 39 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADMCCVVar On ADMCCVVar .AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADMCCVVar.FieldId = 40 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetRawData ADRawData On ADRawData.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADRawData.RawDataTypeID = 277200 And "
sqlstring = sqlstring & " ADRawData.CreditBureauID = 3"
sqlstring = sqlstring & " Where sc3.ContractId Is Not Null"
sqlstring = sqlstring & " and c.CreationDate >='7/1/2007' and c.CreationDate <= '7/27/2007'"
' Set up the connection string, reference an ODBC connection
' There are several ways to do this
' Leave the name and password blank for NT authentication
connstring = _
"ODBC;DRIVER=SQL Server;SERVER=SGA007;UID=; APP=Microsoft Office XP;WSID=GA0236B;DATABASE=mydb"
' Now implement the connection, run the query, and add
' the results to the spreadsheet starting at row A1
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=ActiveCell, Sql:=sqlstring)
.Refresh
End With
End Sub
I want to run let windowxp to run this model automatically every day. How can I do that?
Thanks for any suggestions or hints.
Jeff
Private Sub Query()
' Declare the QueryTable object
Dim qt As QueryTable
' Set up the SQL Statement
sqlstring = " Select "
sqlstring = sqlstring & " C.ContractNbr,C.MerchantId,M.BusinessName,C.FundingAmount,"
sqlstring = sqlstring & " C.FundedAmount,FirstFundingDate as FirstFundingDate,"
sqlstring = sqlstring & " C.CreationDate as CreationDate,GenT.Name as ContractType,"
sqlstring = sqlstring & " CSC.Name as ScorecardName,SC3.ScoreValue as SC3ScoreValue ,"
sqlstring = sqlstring & " SC3.Grade as SC3Grade,CSCT.Name as AcceptanceName,"
sqlstring = sqlstring & " SC1.ScoreValue as SC1ScoreValue,sc3.insertdate as Scoredate,"
sqlstring = sqlstring & " ADCT.Value as CreditTrend,"
sqlstring = sqlstring & " ADBS.Value as BeaconScore,"
sqlstring = sqlstring & " ADAT.Value as AverageTicket,"
sqlstring = sqlstring & " ADFA.Value as FundingAmt,"
sqlstring = sqlstring & " ADMC.Value as MCCV,"
sqlstring = sqlstring & " ADNH.Value as NegativeHistory,"
sqlstring = sqlstring & " ADOB.Value as OpenBankruptcies,"
sqlstring = sqlstring & " ADPFC.Value as PublicFilings,"
sqlstring = sqlstring & " ADPR.Value as Processor,"
sqlstring = sqlstring & " ADRD.Value as RevolvingDebt,"
sqlstring = sqlstring & " ADTIB.Value as TIB,"
sqlstring = sqlstring & " ADRS.Value as TransactionPct,"
sqlstring = sqlstring & " ADTl.Value as TotalTaxLiensAmount,"
sqlstring = sqlstring & " ADSR.Value as SalesRep,"
sqlstring = sqlstring & " ADSC.Value as SICCode,"
sqlstring = sqlstring & " ADTU.Value as Turn,"
sqlstring = sqlstring & " ADRA.Value as RentRatio,"
sqlstring = sqlstring & " ADCP.Value as ContractsPurchased,"
sqlstring = sqlstring & " ADSalT.Value as SalesRepType,"
sqlstring = sqlstring & " ADRawData.TransRisk as TransRisk,"
sqlstring = sqlstring & " ADMCCVVar.Value as MCCVVariance"
sqlstring = sqlstring & " From Cnt_Contracts C"
sqlstring = sqlstring & " join CNT_v_ContractsReports rep on c.contractid = rep.contractid"
sqlstring = sqlstring & " join gen_types GenT On C.contracttypeid = gent.typeid"
sqlstring = sqlstring & " join Mrc_merchants M On C.MerchantId = M.MerchantId"
sqlstring = sqlstring & " JOIN CSC_v_AllMrcScoreCardUsed_by_Contract sc3 on c.contractid = sc3.contractid and"
sqlstring = sqlstring & " sc3.creditscorecardid in (8,9,10,11)"
sqlstring = sqlstring & " Join CSC_Scorecards CSC On sc3.creditscorecardid = csc.creditscorecardid"
sqlstring = sqlstring & " left Join Gen_Types CSCT On Sc3.AcceptanceTypeId = CSCT.TypeId"
sqlstring = sqlstring & " left join CSC_v_AllMrcScoreCardUsed_by_Contract SC1 On C.Contractid = SC1.Contractid and"
sqlstring = sqlstring & " SC1.creditscorecardid = 4"
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADTIB On ADTIB.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADTIB.FieldId = 17 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADPFC On ADPFC.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADPFC.FieldId = 18 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADRD On ADRD.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADRD.FieldId = 19 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADRS On ADRS.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADRS.FieldId = 20 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADMC On ADMC.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADMC.FieldId = 21 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADCT On ADCT.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADCT.FieldId = 22 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADBS On ADBS.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADBS.FieldId = 23 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADTl On ADTl.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADTl.FieldId = 24 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADOB On ADOB.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADOB.FieldId = 25 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADNH On ADNH.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADNH.FieldId = 26 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADZC On ADZC.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADZC.FieldId = 28 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADSR On ADSR.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADSR.FieldId = 29 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADSC On ADSC.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADSC.FieldId = 30"
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADST On ADST.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADST.FieldId = 31 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADFA On ADFA.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADFA.FieldId = 32 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADFM On ADFM.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADFM.FieldId = 33 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADAT On ADAT.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADAT.FieldId = 34 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADTU On ADTU.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADTU.FieldId = 35 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADPR On ADPR.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADPR.FieldId = 36 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADRA On ADRA.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADRA.FieldId = 37 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADCP On ADCP.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADCP.FieldId = 38 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADSalT On ADSalT .AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADSalT.FieldId = 39 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetFields ADMCCVVar On ADMCCVVar .AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADMCCVVar.FieldId = 40 "
sqlstring = sqlstring & " LEFT JOIN MRC_AdditionalDataSetRawData ADRawData On ADRawData.AdditionalDataSetID = SC3.AdditionalDataSetID and"
sqlstring = sqlstring & " ADRawData.RawDataTypeID = 277200 And "
sqlstring = sqlstring & " ADRawData.CreditBureauID = 3"
sqlstring = sqlstring & " Where sc3.ContractId Is Not Null"
sqlstring = sqlstring & " and c.CreationDate >='7/1/2007' and c.CreationDate <= '7/27/2007'"
' Set up the connection string, reference an ODBC connection
' There are several ways to do this
' Leave the name and password blank for NT authentication
connstring = _
"ODBC;DRIVER=SQL Server;SERVER=SGA007;UID=; APP=Microsoft Office XP;WSID=GA0236B;DATABASE=mydb"
' Now implement the connection, run the query, and add
' the results to the spreadsheet starting at row A1
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=ActiveCell, Sql:=sqlstring)
.Refresh
End With
End Sub
I want to run let windowxp to run this model automatically every day. How can I do that?
Thanks for any suggestions or hints.
Jeff