MSQuery appending to next available columns

[alwayslrN]

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.

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

 

[PHV]

Once the QueryTable is created you may choose an automatic refresh on open in its properties window.

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

 

[SkipVought]

Hi,

Here's the drill that I most often use.

1) Add a QueryTable via Data>Get External Data...

2) Turn on the macro recorder and record Data>Get External Data>Edit Query... - Activate the QBE Editor and File>Return Data to Excel. Turn OFF recorder.

3) Modify recorded code and use it to execute the query, as most often I modify the SQL on the fly, or even the Connection String.

Example...

Sub GetRC_List()
    Dim sPath As String, sDB As String, sConn As String, sSQL As String
    Dim xl As Application
    
    Set xl = Application
    
    sPath = ThisWorkbook.Path
    
    sDB = Split(ThisWorkbook.Name, ".")(0)
    
    sConn = "ODBC;DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".xls;"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"
    
    sSQL = "SELECT"
    sSQL = sSQL & "  RESPONCD_201"
    sSQL = sSQL & ", Sum(Total) as RC_Tot"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "FROM `" & sPath & "\" & sDB & "`.`Summary$`"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "Where Total >=" & xl.Index(wsSummary.[Total], wsParms.[ParetoCnt], 1)
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "Group By"
    sSQL = sSQL & "  RESPONCD_201"
    
    Debug.Print sSQL
    
    With wsRC_List.QueryTables(1)
        .Connection = sConn
        .CommandText = sSQL
        .Refresh BackgroundQuery:=False
        xl.DisplayAlerts = False
        .ResultRange.CurrentRegion.CreateNames True, False, False, False
        xl.DisplayAlerts = True
    End With
    
    Set xl = Nothing
End Sub

Skip,

_[red][/red]

 

[alwayslrN]

Hi Skip.

So what you are saying is, the issue that I am having is happening due to the following

[b]With ActiveSheet.QueryTables.Add[/b](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"))

When I created the above, this was the created by

(1) Turn on macro recorder
(2) Data>Import External Data>New Database Query
(3) Select database from list of database
(4) Selected any table
(5) Last page of wizard select View SQL...
(6) Paste SQL
(7) Click object that works like your File>Return Data to Excel
(8) Select cell A5 for destination
(9) Turn off recorder
(10) Cleaned up the mess Excel made and use sSQL from Skip's example.

What in these steps should I have been excluding or am I fine with what I did and I just need to modify one more area from what I did?

Should I be using a connection string rather than using what Excel created?

Also where to you define wsRC_List?

Lastly, for now at least, I am totally sure of what you mean by changing SQL on the fly and from the other post "If your SQL does not change". I did, however, the ADD verses REFRESH. I at least know why new result sets kept getting added.

Sorry for being slow with this. I've never done this stuff before. I'm learning on the fly.

Thanks

 

[SkipVought]

"(1) Turn on macro recorder
(2) Data>Import External Data>New Database Query
(3) Select database from list of database
(4) Selected any table
(5) Last page of wizard select View SQL...
(6) Paste SQL
(7) Click object that works like your File>Return Data to Excel
(8) Select cell A5 for destination
(9) Turn off recorder
(10) Cleaned up the mess Excel made and use sSQL from Skip's example.

What in these steps should I have been excluding or am I fine with what I did and I just need to modify one more area from what I did?"
[blue]
I add the QT so I get exactly the data I want returned to Excel
THEN I turn on the macro recorder and record editing the query and returning data to Excel

Finally your step (10)
[/blue]
Should I be using a connection string rather than using what Excel created?
[blue]
Excel has a connection string. As you can see, it's all chopped up into array elements. I clean that up and use it. IF the query is to a database on the network, I use sPath & sDB to define the path and database names.
[/blue]
Also where to you define wsRC_List?
[blue]
wsRC_List is a Sheet Object. I rename the CodeName in the Project Explorer and use Sheet CodeName rather than Sheet Name, since the user can change tab names.
[/blue]
Lastly, for now at least, I am totally sure of what you mean by changing SQL on the fly and from the other post "If your SQL does not change". I did, however, the ADD verses REFRESH. I at least know why new result sets kept getting added.
[blue]
In my example ...

    sSQL = sSQL & "Where Total >=" & xl.Index(wsSummary.[Total], wsParms.[ParetoCnt], 1)

I calculate a value for the Total criteria. Thats changing the SQL on the fly.
[/blue]


Skip,

_[red][/red]

 

[alwayslrN]

Hi Skip - I had to go away from this for a while.

I cut out steps 1-9 and did what you suggested instead. Works good.

What I did not do is change from the Excel connection. I found some ADO code on the web because I am grabbing this data from SQL Server, however, for now, I am going to keep things as is (i.e. use Excel connection) because it's working.

Thanks

 

[SkipVought]

Great! Have a nice weekend.

Skip,

_[red][/red]