Create a Refreshable Listobject via VBA

[Schwimms]

Hi All,

I am able to select data out of my SQL server database into a ListOjbect via VBA. My problem is I cannot make the ListOjbect Refresh. Meaning I would like to be able to select the list object in Excel and right click and choose refresh (currently grayed out) or refresh it via VBA (receive an error).

Here is my code to produce the listobject in VBA:

[B]Sub refresh()
Dim oCn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim ConnString As String
Dim SQL As String
Dim qt As QueryTable
ConnString = "Provider=SQLOLEDB;Integrated Security=SSPI;" & _
   "Data Source=NCXXCHAR9S\PDS_SSQL02; Database=MWPMO;" & _
    "Persist Security Info=False;"
Set oCn = New ADODB.Connection
oCn.ConnectionString = ConnString
oCn.Open
SQL = "SELECT * from Rpt_RFD"
Set oRS = New ADODB.Recordset
oRS.Source = SQL
oRS.ActiveConnection = oCn
oRS.Open
Set qt = ActiveSheet.ListObjects.Add( _
    SourceType:=XlListObjectSourceType.xlSrcQuery, _
        Source:=oRS, _
            Destination:=ActiveSheet.Range("A3")).QueryTable
With qt
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = True
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .refresh BackgroundQuery:=True
End With
End Sub
[/B]

Please help if you can.

 

[Andrew Poulsom]

Maybe this will help you:

vba - Excel Querytable Refresh only works once - Stack Overflow

It uses xlSrcExternal rather than xlSrcQuery as the SourceType.

 

[Schwimms]

Thanks for the post. I tried that change with NO luck. My goal with this code is to pull data from sql into excel and keep the format (conditonal formats, row highlights, column/row sizes, data formats) of the table each time the data is pulled. If there are any other ways in doing please let me know.