How to Refresh just one of ThisWorkbook.Queries - and the link to ThisWorkbook.Connections-CodePudding

I just want to know how Queries and Connections in Excel Workbooks are connected as objects. I noticed that I can use ThisWorkbook.Connections("Name of my connection").Refresh but that is not the same as the name used in my Queries (as I have commented out in my code).

Sub EditAllWorkbookFormuals(Order As String)
For Each q In ThisWorkbook.Queries
 q.Formula = NewQuery(q.Formula, Order)
 'q.Refresh
 'ThisWorkbook.Connections(q.Name).Refresh
 ThisWorkbook.Connections(1).Refresh
Next
'ThisWorkbook.RefreshAll
End Sub

CodePudding user response：

To refresh a single query, you need to target it specifically.

Sub Refresh_each_query()

    ThisWorkbook.Worksheets("Sheet1").ListObjects("Query1").QueryTable.Refresh
    'ThisWorkbook.Worksheets("Sheet1").ListObjects("Query2").QueryTable.Refresh

End Sub

You can also refresh using the just the connection.

Sub Refresh_each_connection()

    ThisWorkbook.Connections("Query - Query1").Refresh
    'ThisWorkbook.Connections("Query - Query2").Refresh

End Sub

Your connections for queries and queries can be enumerated like this.

Sub Connections_Queries()

strMsgText = "Connections:" & vbCrLf

    For Each c In ThisWorkbook.Connections
        strMsgText = strMsgText & c.Name & vbCrLf
    Next

strMsgText = strMsgText & vbCrLf & "Queries:" & vbCrLf
    
    For Each q In ThisWorkbook.Queries
         strMsgText = strMsgText & q.Name & vbCrLf
    Next

    MsgBox strMsgText

End Sub

CodePudding user response：

This seams to do the trick. But it is not exactly what I was hoping for. Was hoping the Queries object held the link to the Connection object.

Sub EditAllWorkbookFormuals(Order As String)
For Each q In ThisWorkbook.Queries
 q.Formula = NewQuery(q.Formula, Order)
 'q.Refresh
 ThisWorkbook.Connections("Query - " & q.Name).Refresh
Next
End Sub

How this works: My Sup routine receive an input Order - and then calls the change of the formula in my query. I then calls this function to manipulate the query Formula.

Function NewQuery(MyQuery As String, Order As String) As String
' This function replace a string parts of my web query API that return info of JSON order report.
' MsgBox MyQuery
' Use Select case, If, Inst, replace, and so on.
' .....
Pos1 = InStr(1, MyQuery, "GetOrderReport?order=") ' Find Start position to be replaced
If Pos1 > 0 Then Pos2 = InStr(Pos1, MyQuery, ")") ' Finds then End posistion to be replaced
If Pos1 > 0 Then
    
    ToBeReplaced = Mid(MyQuery, Pos1, Pos2 - Pos1   1)
    'MsgBox ToBeReplaced
    
    NewFilter = "GetOrderReport?order=" & Order & """)"
    'MsgBox NewFilter
    
    ' Replace the filter
    MyQuery = Replace(MyQuery, ToBeReplaced, NewFilter)
End If
' Parse the new string back to Sub as the NewQuery string to Formula
NewQuery = MyQuery
End Function