Refreshable Data Connection from Web to Excel TABLE

[MichaelSchulz]

The end goal is to have an Excel Table with a refreshable data connection to a web table.

The reason to have an Excel Table is to use that Table as a reference point on other sheets, to perform calculations on other sheets, and to create summaries on other sheets. All of this is made much easier with the use of Table references. Also of importance is that the users wish to be able to click that 'Refresh All' button to get a current data set from the web source.

Using 'Get External Data' 'From Web' does not result in an Excel Table. Formatting the result as a Table removes the data connection.

How can this be achieved?

 

[Zack Barresse]

Edit the connection properties and get the connection string, post it here. We can write code to do the query for you automatically, but we need the connection string to do anything.

 

[Zack Barresse]

I'll go ahead and post the code. Adjust the parts as necessary.

Sub ResetTableQueryButLeaveTableIntact()
    
    Dim Sheet As Worksheet
    Dim Table As ListObject
    Dim Connection As Object
    Dim Records As Object
    Dim TotalShowing As Boolean
    Dim FieldIndex As Long
    
    Dim SQL As String
    
    On Error Resume Next
    Set Sheet = ThisWorkbook.Worksheets("Sheet1")
    If Sheet Is Nothing Then
        MsgBox "Couldn't find 'Sheet1'"
        Exit Sub
    End If
    Set Table = Sheet.ListObjects("TableNameHere")
    If Table Is Nothing Then
        MsgBox "Couldn't find a Table"
        Exit Sub
    End If
    On Error GoTo 0
    
    Set Connection = CreateObject("ADODB.Connection")
    Set Records = CreateObject("ADODB.Recordset")
    
    SQL = "YOUR SQL EXPRESSION GOES RIGHT HERE"
    Connection.Open "Provider=SQLOLEDB.1;User ID=USERNAMEHERE;Password=PASSWORDHERE;Persist Security Info=True;Data Source=SERVERNAMEHERE;"
    Set Records = Connection.Execute(SQL)
    
    TotalShowing = Table.ShowTotals
    If Not Table.DataBodyRange Is Nothing Then Table.DataBodyRange.Delete
    Table.ShowTotals = False
    
    If Not Records.EOF Then
        Table.InsertRowRange(1, 1).CopyFromRecordset Records
        For FieldIndex = 1 To Records.Fields.Count
            Table.ListColumns(FieldIndex).Name = Records.Fields(FieldIndex - 1).Name
        Next FieldIndex
        Records.Close
        Sheet.Cells.EntireColumn.AutoFit
    End If
    
    Table.ShowTotals = TotalShowing
    
End Sub

If you don't need a username or password, remove those parts from the connection string.

HTH

 

[MichaelSchulz]

Zack,

I appreciate your help—thank you!—but website from which I am pulling the data is a private site so I cannot post the connection string.

That being said, would your code be appropriate to get external data from a webpage? The connection string is essentially a URL address that passes parameter values, e.g. http://[…]/LR.asp?LocType1=2&WC=&SORT1=&EF=EF

Also, would after creating a table out of the data, would it keep the connection to the web data? I ask because when I manually format the results as a Table, the connection is removed.

 

[Zack Barresse]

Ooh, that presents difficulties. I read 'data connection' as in over the web, not a web page via URL. These work differently as it's not a standard connection. It's like connecting to a text file - there is no known configuration, data could be anything or anywhere. You can't bring this kind of data back into a Table. What you can do is to create your web query, bring it back to a worksheet, then get some code to take that data and put it into a Table. At that point you can use this secondary Table for other things in your workbook, and refresh it whenever you refresh your web query.

To do this we would need an idea of how your data query results were structured. Can you give us an idea of that? An example would work best. Obfuscate all the data you need to, and we only need about 20 rows of data. Dummy it up all you want, it's the structure we're concerned with, especially since you can't share the website URL with us.