I have the following which when I run the macro returns the appropritate information. If I run the macro again, it returns the records, but I would like it to override the existing records, but what it does instead is put the new records at the end of the first set of records starting at the first empty column after the last column of the first run of the macro.
The idea of how this all will work is for the data to automatically refresh each time the workbook is opened. Not only that, it is to override the existing data. I am not sure how to this.
The idea of how this all will work is for the data to automatically refresh each time the workbook is opened. Not only that, it is to override the existing data. I am not sure how to this.
Code:
Sub mkQry()
'
' mkQry Macro
' Macro recorded 5/24/2007 by Administrator
'
'
Dim sSQL As String
sSQL = "SELECT "
sSQL = sSQL & "nyl_sfp_poolmast.pool,"
sSQL = sSQL & "nyl_sfp_poolmast.deal,"
sSQL = sSQL & "pool_prin_bal = isnull(b.pool_prin_bal,0),"
sSQL = sSQL & "pool_original_amt = isnull(b.pool_original_amt,0),"
sSQL = sSQL & "pool_funding_amt = isnull(b.pool_funding_amt,0),"
sSQL = sSQL & "loan_count = isnull(b.loan_count,0),"
sSQL = sSQL & "percentage = isnull(b.pool_prin_bal/p.all_pools_prin_bal,0),"
sSQL = sSQL & "cur_ltv = isnull(b.cur_ltv,0),"
sSQL = sSQL & "gross_rate = isnull(b.gross_rate,0),"
sSQL = sSQL & "service_fee = isnull(b.service_fee,0),"
sSQL = sSQL & "net_rate = isnull(b.net_rate,0),"
sSQL = sSQL & "orig_term = isnull(b.orig_term,0),"
sSQL = sSQL & "cur_rterm = isnull(b.cur_rterm,0),"
sSQL = sSQL & "cur_age = isnull(b.cur_age,0),"
sSQL = sSQL & "b.due_day"
sSQL = sSQL & vbCrLf
sSQL = sSQL & " FROM"
sSQL = sSQL & " SFP_Report_Data_Conversion.dbo.nyl_sfp_poolmast nyl_sfp_poolmast"
sSQL = sSQL & vbCrLf
sSQL = sSQL & " left outer join (select pool, pool_prin_bal = sum(prin_bal),"
sSQL = sSQL & " pool_original_amt = sum(original_amt),"
sSQL = sSQL & " pool_funding_amt = sum(funding_amt),"
sSQL = sSQL & " cur_ltv = round(max(case prin_bal when 0 then 0 else wcltv/prin_bal end),2),"
sSQL = sSQL & " gross_rate = round(max(case prin_bal when 0 then 0 else wac/prin_bal end),3),"
sSQL = sSQL & " service_fee = round(max(case prin_bal when 0 then 0 else wcsfee/prin_bal end),4),"
sSQL = sSQL & " net_rate = round(max((case prin_bal when 0 then 0 else wac/prin_bal end)- (case prin_bal when 0 then 0 else wcsfee/prin_bal end)),3),"
sSQL = sSQL & " orig_term = max(case prin_bal when 0 then 0 else wcoterm/prin_bal end),"
sSQL = sSQL & " cur_rterm = max(case prin_bal when 0 then 0 else wcrterm/prin_bal end),"
sSQL = sSQL & " cur_age = max(case prin_bal when 0 then 0 else wage/prin_bal end),"
sSQL = sSQL & " due_day = max(case prin_bal when 0 then 0 else wcremit/prin_bal end),"
sSQL = sSQL & " loan_count = count(case when prin_bal != 0 then 1 end),"
sSQL = sSQL & " hist_date"
sSQL = sSQL & vbCrLf
sSQL = sSQL & " from nyl_sfp_balances"
sSQL = sSQL & vbCrLf
sSQL = sSQL & " where hist_date = '2007-04-30'"
sSQL = sSQL & " group by pool,hist_date) b on"
sSQL = sSQL & " b.pool = nyl_sfp_poolmast.pool"
sSQL = sSQL & vbCrLf
sSQL = sSQL & " left outer join (select hist_date,sum(prin_bal) as all_pools_prin_bal"
sSQL = sSQL & vbCrLf
sSQL = sSQL & " from nyl_sfp_balances"
sSQL = sSQL & vbCrLf
sSQL = sSQL & " where hist_date = '2007-04-30'"
sSQL = sSQL & " group by hist_date) p on"
sSQL = sSQL & vbCrLf
sSQL = sSQL & " p.hist_date = '2007-04-30'"
sSQL = sSQL & vbCrLf
sSQL = sSQL & " ORDER BY"
sSQL = sSQL & " nyl_sfp_poolmast.pool ASC"
Debug.Print sSQL
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=SFP_Report_Data_Conversion;UID=nyl_sfp;APP=Microsoft Office XP;WSID=IMN-FMO-TXKV5;DATABASE=SFP_Report_Data_Conversion;Trust" _
), Array("ed_Connection=Yes")), Destination:=Range("A4"))
.CommandText = sSQL
.Name = "Query from SFP_Report_Data_Conversion"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub