How to change where query results start?

[alwayslrN]

I have the following in a module. The results start in A5, is there anything in the code I can change so that the results start in A6 instead?

Sub sumQry()
'
' sumQry Macro
' Macro recorded 6/7/2007
'
' Keyboard Shortcut: Ctrl+q
'
Dim sSQL As String
Dim acctg_date As Date
Dim xl As Application
    
Set xl = Application

acctg_date = wsSummary.Range("A3").Value

sSQL = "SELECT "
sSQL = sSQL & "nyl_sfp_poolmast.deal,"
sSQL = sSQL & "nyl_sfp_poolmast.pool,"
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 & " LMS_NYL.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((case sum(prin_bal) when 0 then 0 else sum(wcltv)/sum(prin_bal) end),2),"
sSQL = sSQL & "gross_rate = round((case sum(prin_bal) when 0 then 0 else sum(wac)/sum(prin_bal) end),3),"
sSQL = sSQL & "service_fee = round((case sum(prin_bal) when 0 then 0 else sum(wcsfee)/sum(prin_bal) end),4),"
sSQL = sSQL & "net_rate = round(((case sum(prin_bal) when 0 then 0 else sum(wac)/sum(prin_bal) end)- (case sum(prin_bal) when 0 then 0 else sum(wcsfee)/sum(prin_bal) end)),3),"
sSQL = sSQL & "orig_term = (case sum(prin_bal) when 0 then 0 else sum(wcoterm)/sum(prin_bal) end),"
sSQL = sSQL & "cur_rterm = (case sum(prin_bal) when 0 then 0 else sum(wcrterm)/sum(prin_bal) end),"
sSQL = sSQL & "cur_age = (case sum(prin_bal) when 0 then 0 else sum(wage)/sum(prin_bal) end),"
sSQL = sSQL & "due_day = (case sum(prin_bal) when 0 then 0 else sum(wcremit)/sum(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 = '" & acctg_date & "'"
sSQL = sSQL & vbCrLf
sSQL = sSQL & " group by pool,hist_date) b on"
sSQL = sSQL & vbCrLf
sSQL = sSQL & " b.pool = nyl_sfp_poolmast.pool"
sSQL = sSQL & vbCrLf
sSQL = sSQL & " left outer join (select hist_date,all_pools_prin_bal = sum(prin_bal)"
sSQL = sSQL & vbCrLf
sSQL = sSQL & " from nyl_sfp_balances"
sSQL = sSQL & vbCrLf
sSQL = sSQL & " where hist_date = '" & acctg_date & "'"
sSQL = sSQL & vbCrLf
sSQL = sSQL & " group by hist_date) p on"
sSQL = sSQL & vbCrLf
sSQL = sSQL & " p.hist_date = '" & acctg_date & "'"
sSQL = sSQL & vbCrLf
sSQL = sSQL & " where nyl_sfp_poolmast.closed_date <= '" & acctg_date & "'" & " or nyl_sfp_poolmast.closed_date is null"
sSQL = sSQL & vbCrLf
sSQL = sSQL & " ORDER BY"
sSQL = sSQL & " nyl_sfp_poolmast.pool ASC"

'clear existing records in case user chooses an earlier accounting date
wsSummary.Range("sum_table").Select
xl.DisplayAlerts = False
Application.ScreenUpdating = False
Selection.ClearContents
xl.DisplayAlerts = True
Application.ScreenUpdating = True


    With wsSummary.QueryTables(1)
        .Connection = _
        "ODBC;DSN=LMS_NYL;UID=report;PWD=nylim;APP=Microsoft Office XP;WSID=IMN-FMO-TXKV5;DATABASE=LMS_NYL" _
        '), Array("ed_Connection=Yes"))
        .CommandText = sSQL
        .Refresh BackgroundQuery:=False
    End With
    Range("G6").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "0.00%"
    Range("I6").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "0.00000000"

End Sub

 

[PHV]

With wsSummary.QueryTables(1)
.Destination = wsSummary.Range("A6")
.Connection = _
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[alwayslrN]

Thanks PH - that didn't work, suprisingly.

 

[PHV]

And this ?
.Destination = "A6"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Hi,

The Destination property of the QueryTable object is READ ONLY.

You can CUT and the PASTE the QueryTable

    wsSummary.QueryTables(1).ResultRange.Cut
    wsSummary.Paste wsSummary.Range("A6")

Skip,

_[red][/red]

 

[PHV]

BTW, why not simply insert a row after A4 ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Method A or method B.

As one surgeon said to the other, "Suture self!" ;-)


Skip,

_[red][/red]

 

[alwayslrN]

Thanks Skip and PH. I ended up using the method given by Skip. I had to do some minor tweeks in other areas, but it worked fine after that.