Batch replacing External Connection Stream

[fluffynicesheep]

Hi,

I currently have a spreadsheet that includes lots of pages imported from external data (an external website).

The links that I used were similar to:

https://www.twitrss.me/twitter_user_to_rss/?user=barkingcollege

However recently there has been an issue with a security certificate on the website, so unless I manually click [ok], when excel opens - the data can no longer refresh in the background overnight.

What seems to work is if I remove the s from the link and replace with:

http://www.twitrss.me/twitter_user_to_rss/?user=barkingcollege

With hundreds of links this would take an age to do manually, so what I'm after is a piece of code that would allow me to cycle through all of my connection strings and do a Find and Replace ...

If it finds any link containing twitrss.me it will simply replace the https with http

Hopefully someone can help?

Thanks

 

[fluffynicesheep]

Hi,

Just wondering if anyone had any ideas?

 

[John_w]

Try this macro.

Public Sub Change_Query_Connections()

    Dim ws As Worksheet
    Dim qt As QueryTable
    
    For Each ws In ActiveWorkbook.Worksheets
        For Each qt In ws.QueryTables
            qt.Connection = Replace(qt.Connection, "https://", "http://")
            qt.Refresh BackgroundQuery:=False
        Next
    Next
    
End Sub

 

[fluffynicesheep]

Try this macro.

Public Sub Change_Query_Connections()

    Dim ws As Worksheet
    Dim qt As QueryTable
   
    For Each ws In ActiveWorkbook.Worksheets
        For Each qt In ws.QueryTables
            qt.Connection = Replace(qt.Connection, "https://", "http://")
            qt.Refresh BackgroundQuery:=False
        Next
    Next
   
End Sub

Hi John,

Thanks for the reply.

Unfortunately I've added this and run it and it doesn't appear to do anything. I have had a look back at the properties in the connection settings menu and it is still showing the link with the s at the end.

 

[jkpieterse]

You could try my FlexFind add-in from Excel: Flexfind. Make sure you check the "Objects" checkbox.

 

[John_w]

How did you import the external data in the first place? What are the exact steps?

 

[fluffynicesheep]

Hi John,

I am using Excel 2016.

I clicked on [data] and then [from web] then I added the weblink:

https://www.twitrss.me/twitter_user_to_rss/?user=barkingcollege

Originally this all worked fine, but as you can see when I try to add it now, I get this message:

So any new links, I'm simply changing to http://www.twitrss.me/twitter_user_to_rss/?user=barkingcollege and it goes in and updates fine.

So, in order to update this workbook I now have to manually click [Yes] once.

This would be fine, but I would like the links to auto refresh overnight - so in order to do this automatically I have to get rid of this security alert.

I know moving forward that removing the S works fine and the cert will no longer show .... but it's changing all the previous weblinks that is gong to be so time consuming!

I basically need to change https: to http: in the below location for around 1000 different connections.

I hope this helps

 

[John_w]

Your screenshot shows the connection is using a web query, and my macro changes the connection string for every web query on every sheet, so I don't understand why it isn't changing https to http.

Try this macro, which is another way of changing web queries, but from the workbook connections. It also displays details of each web query connection.

Public Sub Change_Workbook_Connections()

    Dim wbConn As WorkbookConnection
    Dim wbConnRange As Range
    Dim qt As QueryTable
    Dim prevConnection As String
    
    For Each wbConn In ActiveWorkbook.Connections
        Select Case wbConn.Type
            Case Is = xlConnectionTypeWEB, xlConnectionTypeXMLMAP
                For Each wbConnRange In wbConn.Ranges
                    Set qt = wbConnRange.QueryTable
                    prevConnection = qt.Connection
                    qt.Connection = Replace(qt.Connection, "https://", "http://", 1, , vbTextCompare)
                    MsgBox "Connection Name: " & wbConn.Name & vbCrLf & _
                        "Type: " & wbConn.Type & vbCrLf & _
                        "Sheet: " & wbConnRange.Parent.Name & ", Range: " & wbConnRange.Address & vbCrLf & _
                        "Previous connection: " & prevConnection & vbCrLf & _
                        "Current connection: " & qt.Connection
                Next
            Case Else
                MsgBox "Uncoded WorkbookConnection Type " & wbConn.Type
        End Select
    Next
    
End Sub

 

[fluffynicesheep]

Hi John,

I've run this and a debug pops up straight way. The yellow line that is highlighted is:

Set qt = wbConnRange.QueryTable

 

[John_w]

It's very strange that the macros aren't working for you. When I import https://www.twitrss.me/twitter_user_to_rss/?user=barkingcollege (via Data - From Web), the page is imported using a Web Query and as XML data:

Could you record a macro of you changing one of the connections and post the code here. With that I should be able to modify it to change all connections from https to http.