automatic run module

[jeff06]

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

[Bob Phillips]

You could put it in the workbook open event, and closedown at the end, then schedule a OS job t run daily starting Excel with that workbook.

[jeff06]

XLd
Thank you for your advice.
Can you give me an example on how to put it in the workbook open event?
Thanks again.
Jeff

[Bob Phillips]

Private Sub Workbook_Open()
Call Query
End Sub


This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

[jeff06]

XLD,
I appreciate your help!
Jeff

[jeff06]

XLD,
Where shoud i put the code you provided in order to let it run when i open the workbook.
I put your code just in the module holing my original code, but it does not seem to work.
Thank you again,
Jeff

[Bob Phillips]

Look at my post, it tells you there.

[jeff06]

Oo,
Thanks.