Finding and updating a data connection

[Rastaman]

I’m hoping someone can help with a problem dealing with data connections. I’m trying to create a tool that will refresh an existing web data connection on the active sheet, prompting the user to input a new URL. This is a generic utility, intended to work on any existing data connection. My plan is as follows:

Find any data connections on the active sheet
Verify there is only one connection
Prompt user for a new URL
Refresh the query

I first want to check to see if a data connection exists. I could use something like the code below, but I haven’t figured out how to tell if the connection is on the active sheet. Also, this connection name does not work in the Querytables(TestConn).Refresh command. The TestConn name with this code matches the connections name, but the Querytables Refresh command requires the data range properties name. These two names do not match unless someone had previously renamed them to match, which I can’t count on in my generic macro.

For Each DataConn In ActiveWorkbook.Connections
    TestConn = DataConn.Name
    If ConnName = TestConn Then FoundMatch = "Yes"
Next DataConn

So instead I found that I can loop through each named range in the active sheet to find the data connection. In my testing this works well since I rarely have a named range with a scope of worksheet besides data connections. I just have to parse the resulting name string to remove the sheet name.

For Each nm In ActiveSheet.Names
    ConnName = nm.Name
    NameCount = NameCount + 1
Next nm

Since this isn’t 100% certain to find the right data connection, I am trying to loop through all data connections and validate that the named range that I found is an actual data connection. But I have the same problem that I can’t compare the named range name with the data connection name. Is there a better approach to this?

Thanks

 

[Rastaman]

Ok, it looks like I don't need to use the ActiveWorkbook.Connections.Name at all. Instead, I found that I can use the ActiveSheet.QueryTables(indexnumber).Name. Here's my code.

Sub EditExistingDataConnection()
'find existing data connection on the current sheet, prompt user for new URL
'abort if more than one connection found or if no data connection found
Dim TableIndex As Integer, errnum As Integer
Dim QryName As String, newURL As String

'count the number of queries in the current sheet
errnum = 0
TableIndex = 0
On Error Resume Next
Do While errnum = 0
    TableIndex = TableIndex + 1
    QryName = ActiveSheet.QueryTables(TableIndex).Name
    errnum = Err.Number
Loop

'abort if no queries or more than 1 query
If TableIndex > 2 Then
    MsgBox "duplicates data connections found on this sheet, aborting"
    Exit Sub
Else: End If
If TableIndex = 1 Then
    MsgBox "No data connect found on this sheet, aborting"
    Exit Sub
Else: End If

'get the new URL from the user and test it
newURL = InputBox("Enter the new data URL:")
If newURL = vbNullString Then Exit Sub       'exit if Cancel selected or no URL entered
If Left(newURL, 8) <> "https://" Then
    MsgBox "The URL doesn't appear to be valid, aborting"
    Exit Sub
Else: End If
If Len(newURL) >= 254 Then
    If MsgBox("The character length of the URL you pasted reached the max allowed and may have been truncated. Continue?", vbYesNo) = vbNo Then Exit Sub
Else: End If

'refresh query
    With ActiveSheet.QueryTables(QryName)
        .Connection = "URL;" & newURL
        .Refresh BackgroundQuery:=False
    End With


End Sub