Import Web Data VBA "sometimes working"

Hello,
I have written some VBA code that will reach out to a URL link and will import external data into Excel. The code works great, well…..every once in a while. After getting the code to run all the way through and import the web query, I saved the file and closed it. When I reopened the file, and tried running the same code, it errors out. I am wondering if I break the connection/link once I close the file. Another weird thing is even though the code errors out, if I go import the URL manually by going to Data > External Data > From Web and I begin to import the URL, Excel keeps asking me the following:

“Do you want to view only the webpage content that was delivered securely? This webpage contains content that will not be delivered using a secure HTTPS connection, which could compromise the security of the entire webpage.”

Excel will ask this to me maybe 3-4 times for the URL link I am attempting to import. However this isn’t the weird thing…Once I press yes and the URL is loaded properly, then I import the data, the VBA coding that I wrote will work properly and will run all the way through. As long as I don’t close the file, I can keep refreshing/importing the data as much as I want to. I’d like to create a loop to basically skip any errors as well as any security warnings until the VBA code will load/refresh the data from the URL. Can anyone help me with this error loop? The code below is what I have to import my data and is working well when I have the file open. Thank you!!

Sub Link_4()

Dim Link4 As String

Link4 = "URL;https://wwww.google.com"

 With Sheets("Link4").QueryTables.Add(Connection:=Link4, Destination:=Sheets("Link4").Range("$A$1"))
        .Name = "Link4"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

Sheets("Link4").Activate

End Sub

NOTE: The URL link I have provided is NOT the actual link I am attempting to import. I put this URL only because the link I am trying to get to is confidential and holds propiertary information.

Hey JB. I actually tried that as well....No luck. I think I need a loop to keep trying the URL link. Maybe some code that will keep trying the code until there's no error. The website itself will work usually around 10x refershes. Can we add some code that will do this?

Hey JB,

Thanks for getting back to me about this! I tried inserting your piece of code and it isn't taking the error into account. It will error out just after trying to import the first time. Thoughts? Please see below for the code that is in a module:

Sub Link_2()


Dim Link2 As String
Dim Recount As Long

Application.DisplayAlerts = False

On Error GoTo StartOver

StartOver:
If Recount > 15 Then
   MsgBox "Still not working after 15 tries"
   Exit Sub
    End If
Recount = Recount + 1

Link2 = "URL;https://wwww.google.com"
 With Sheets("Link2").QueryTables.Add(Connection:=Link2, Destination:=Sheets("Link2").Range("$A$1"))
        .Name = "Link2"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

Sheets("Link2").Activate
End Sub

Hey abousetta,

I tried your response:

Application.wait Now() + Timevalue("00:00:05")

However, it still errored out. The link that I am going to is XML. What code do I need to tell it to loop until the webpage is loaded? I think you are correct as I also believe that is the culprit.

where do I put that in relation to my code?