Hello!

I have a Microsoft anomaly I can’t figure out! This has to do with adding a web query connection via VBA. I have saved a web query connection in a folder on my desktop. My goal is to open a new workbook, and run some code that will add the stored connection and put it in a sheet. The current code I have errors out because Excel changes the URL link somewhere in the background. The anomaly is this:

When I carry out the following steps manually, the URL works because I am FORCING excel to use the correct UR. The steps I take to carry this out manually are as follows:

1) Data > Get External Data > Existing Connections > Browse for Connection > Double click connection  After performing this task, I get an “Unable to Open https://www.......... Cannot download the information you requested”
a. This is fine, because I understand the issue here. The issue is that Excel is actually taking out part of the URL link.
b. From here, I go to Data > Connections > Double click on my connection > Definition > Edit Query.
c. Once I “Edit Query” I get the Edit Web Query window that opens. This shows me the INCORRECT url. I then press CANCEL. This takes me back to “Connection Properties”. Now, when I click on “Edit Query” again, the CORRECT url is there and I can import the data with no issues whatsoever.

2) So, now that you know where my problem lies, I am trying to FORCE excel to edit the query via VBA and force the edit the query with the CORRECT url that I am using. I have the following code, but it is still errors at the .Refresh . Any ideas on the code? Maybe I am not doing this properly? Thoughts?

Sub ForceConnection()

Dim NewURL As String

NewURL = "https://www.google.com"
 
 Workbooks("Book1").Connections.AddFromFile _
        "C:\Documents and Settings\Desktop\Sprint.iqy"
    
     With ActiveSheet.QueryTables.Add(Connection:= _
        "FINDER;C:\Documents and Settings\Desktop\Sprint.iqy", _
        Destination:=Range("$A$1"))
        .Name = "Sprint"
        .FieldNames = True
        .BackgroundQuery = True
        .SaveData = True
        .TablesOnlyFromHTML = True
      End With
        With ActiveSheet.QueryTables(1)
            .Connection = "URL;" & NewURL
            .Refresh
        End With
End Sub
***** Note: The URL shown here in this thread is actually not the URL I am using, I can't include the URL I am attempting to use since it holds proprietary information *****

HELP!