Querytable Refresh Adds Columns

[krysolov]

I have a requirement for query with parameters to get data from MS SQL 2005 db. Apparently, one cannot use the OLEDB driver and the Connection Properties box, so I'm writing a macro to collect the parameters from Sheet2 and output data to Sheet1.
When Sheet1 is blank, the following works like a charm. Once the data has been retrieved though, the consequent executions of the macro just add columns with data.

Before having added the qt.Delete statement, I had a condition of trying to refresh the qt, which didn't work, so I guess the only means to refresh the data is to re-create the qt every time the macro runs, which seems extremely inefficient.

So I guess I'd like to find out answers to the following questions:
1) Is there a way to actually refresh a QueryTable that was based on a recordset by changing the recordset and just perform the .Refresh method (without constantly doing the .Add)?
2) If not, what's the best mechanism of doing the "refresh"?

Thank you,
Sergey

Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim stSQL As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rn As Range
    Dim qt As QueryTable
     
    Const stADO As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
    "Persist Security Info=False;" & _
    "Initial Catalog=SiriusSQL;" & _
    "Data Source=SRV-MTC-SIRIUS"
     
    Set wb = ActiveWorkbook
    Set ws = wb.Worksheets(1)
     
    With ws
        Set rn = .Range("A1")
    End With
     
    stSQL = "<some SQL statement>"
     
    Set cn = New ADODB.Connection
     
    With cn
        .CursorLocation = adUseClient
        .Open stADO
        .CommandTimeout = 0
        Set rs = .Execute(stSQL)
    End With
     
    Set qt = ws.QueryTables.Add(Connection:=rs, Destination:=rn)
    With qt
        .RefreshStyle = xlInsertDeleteCells
        .Refresh False
        .MaintainConnection = False
        .Delete
    End With

     'Cleaning up.
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing

 

[DonkeyOte]

Not entirely sure I follow but regards:

When Sheet1 is blank, the following works like a charm. Once the data has been retrieved though, the consequent executions of the macro just add columns with data.

Perhaps modify your query refreshstyle setting to:

.RefreshStyle =xlOverwriteCells

 

[xenou]

Did you come to any conclusion about my thoughts on the matter in your previous post:
http://www.mrexcel.com/forum/showthread.php?t=566768