How to get data out of a programmatically created "Connection" and "Query" in Excel VBA?

Question

I've created a connection to an oData feed in a macro, without saving "Queries" or "Connections" in the workbook, so that it will hopefully defer oData authentication to the built in Excel mechanisms. Basically I want to do the following in a VBA macro:

1. Create a "Query", which appears to require a "Connection"
2. Refresh that Query and read its data (I think it MUST put that data in a range, for some reason)
3. Remove the query and connection that were created in step 1

The reason for not wanting to just create queries manually with the GUI is because this macro is going to exist in an .XLAM (add-in) therefore saving queries in the add-in's workbook causes a security prompt when Excel starts, and I'm trying to just make the same behaviour as if you were to use XMLHTTP or similar

I have this so far:

Public Sub Test()
    
    On Error GoTo handler
    
    Dim s As String
    Dim c As WorkbookConnection
    Dim q As WorkbookQuery
    Dim t As QueryTable
    Dim ws As Worksheet
    
    Application.StatusBar = "Creating Query..."
    s = "let " & _
            "Source = OData.Feed(""" & URL & """, null, [Implementation=""2.0""]), " & vbCrLf & _
            "ES_ATTRIBUTE_SPECIFICATION_table = Source{[Name=""ES_ATTRIBUTE_SPECIFICATION"",Signature=""table""]}[Data], " & vbCrLf & _
            "#""Removed Other Columns"" = Table.SelectColumns(ES_ATTRIBUTE_SPECIFICATION_table,{""OBJECT_SPECIFICATION""}), " & vbCrLf & _
            "#""Removed Duplicates"" = Table.Distinct(#""Removed Other Columns""), " & vbCrLf & _
            "#""Sorted Rows"" = Table.Sort(#""Removed Duplicates"",{{""OBJECT_SPECIFICATION"", Order.Ascending}}) " & vbCrLf & _
        "in " & vbCrLf & _
            "#""Sorted Rows"""
    
    Set q = ThisWorkbook.Queries.Add(Name:=QUERY_NAME, Formula:=s)
    

    
    'Application.StatusBar = "Creating Connection..."
    'Set c = ThisWorkbook.Connections.Add2( _
    '            CONN_NAME, _
    '            "", _
    '            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & QUERY_NAME & ";Extended Properties="""";", _
    '            "SELECT * FROM [ES_ATTRIBUTE_SPECIFICATION]", _
    '            xlCmdSql) 'This prevents "invalid procedure call or argument"
            
    'c.OLEDBConnection.BackgroundQuery = False
    
    
    Application.StatusBar = "Creating Data Table..."
    Set ws = shtODataDump
    Set t = shtODataDump.QueryTables.Add( _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & QUERY_NAME & ";Extended Properties="""";", _
        ws.Range("A1"), _
        "SELECT * FROM [ES_ATTRIBUTE_SPECIFICATION]")
    t.Refresh False  '1004 - [Expression.Error] The import ES_ATTRIBUTE_SPECIFICATION matches no exports. Did you miss a module reference?

normalexit:
    On Error Resume Next
    If Not c Is Nothing Then
        c.Delete
    End If
    If Not q Is Nothing Then
        q.Delete
    End If
    If Not ws Is Nothing Then
        ws.QueryTables.Item(TABLE_NAME).Delete
    End If
    
    Application.StatusBar = ""
    Exit Sub
handler:
    Debug.Print Err.Number & " - " & Err.Description
    Resume normalexit
End Sub

The problem I'm facing is that when I refresh the QueryTable I get 1004 - [Expression.Error] The import ES_ATTRIBUTE_SPECIFICATION matches no exports. Did you miss a module reference? and I can't find an answer as to what that actually means..?

What am I doing wrong?

Answer 1

I might come up against other things but I got to a point where I want to share a solution. The working code is:

Private Const QUERY_NAME = "MY_COOL_QUERY"

Public Sub Test()
    
    On Error GoTo handler
    
    Dim s As String
    Dim c As WorkbookConnection
    Dim q As WorkbookQuery
    Dim t As QueryTable
    Dim ws As Worksheet
    
    Application.StatusBar = "Creating Query..."
    s = "let " & _
            "Source = OData.Feed(""" & URL & """, null, [Implementation=""2.0""]), " & vbCrLf & _
            "ES_ATTRIBUTE_SPECIFICATION_table = Source{[Name=""ES_ATTRIBUTE_SPECIFICATION"",Signature=""table""]}[Data], " & vbCrLf & _
            "#""Removed Other Columns"" = Table.SelectColumns(ES_ATTRIBUTE_SPECIFICATION_table,{""OBJECT_SPECIFICATION""}), " & vbCrLf & _
            "#""Removed Duplicates"" = Table.Distinct(#""Removed Other Columns""), " & vbCrLf & _
            "#""Sorted Rows"" = Table.Sort(#""Removed Duplicates"",{{""OBJECT_SPECIFICATION"", Order.Ascending}}) " & vbCrLf & _
        "in " & vbCrLf & _
            "#""Sorted Rows"""
    
    Set q = ThisWorkbook.Queries.Add(Name:=QUERY_NAME, Formula:=s)

    Application.StatusBar = "Creating Data Table..."
    Set ws = shtODataDump
    
    Set t = shtODataDump.QueryTables.Add( _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & QUERY_NAME & ";Extended Properties="""";", _
        ws.Range("A1"), _
        "SELECT * FROM [" & QUERY_NAME & "]")
        
    t.Refresh BackgroundQuery:=False

normalexit:
    On Error Resume Next
    If Not c Is Nothing Then
        c.Delete
    End If
    If Not q Is Nothing Then
        q.Delete DeleteConnection:=True
    End If
    If Not ws Is Nothing Then
        ws.QueryTables.Item(TABLE_NAME).Delete
    End If
    
    Application.StatusBar = ""
    Exit Sub
handler:
    Debug.Print Err.Number & " - " & Err.Description
    Resume normalexit
End Sub

This runs the query against oData and puts the result in A1 on a sheet. A Connection object is created automatically when creating WorkbookQuery, but this can be deleted along with the Query by doing q.Delete DeleteConnection:=True where q is the query

Edit: Doing this also creates named ranges automatically, which can also be programmatically deleted