Excel VBA Web-Query error saying cannot locate internet server or proxy server

Question

So I've been doing quite a lot of "development" in excel vba (not really high class stuff) for my company and have been experiencing an issue I can't seem to solve.

What the app does: QC's check for errors in customer boxes and then logs any found errors by noting down the details and then submitting the information to a google form by using a web query function I found on the internet along with a parser class, the google form then writes to a google sheet where consolidated I use the information to display current error rates and sample sizes on a google sheet/dashboard. The QC's use Microsoft windows surface pro tablets to run the application in excel and submit the errors they find.

The problem: At very random times some tablets would give an error when trying to submit a response to the google form (error comes up in the web query module) and I have no idea what is causing it. Sometimes it is all the tablets, sometimes only some that have the problem and usually the issue is resolved by restarting the app or just after waiting a few minutes.

My guesses:

Network outages/drops
Network firewall related issue

Solutions I have tried:

Spoke to network admin to find out if there are any restrictions that would cause something like this. No luck there
Googled for potentially similar problems, but it doesn't seem like this is something a lot of people do, which is weird in the coding/development field for me.
Current solution I am trying, I will update whether or not this works. So far so good.

Code for the web query I use:

Public Sub CreateWebQuery(Destination As Range, url As String, Optional WebSelectionType As XlWebSelectionType = xlEntirePage, Optional SaveQuery As Boolean, Optional PlainText As Boolean)
    
    With Destination.Parent.QueryTables.Add(Connection:="URL;" & url, Destination:=Destination)
        .Name = "WebQuery"
        .RefreshStyle = xlOverwriteCells
        .WebSelectionType = WebSelectionType
        .PreserveFormatting = PlainText
       .BackgroundQuery = False
       .Refresh
        If Not SaveQuery Then .Delete
    End With

End Sub

Error that comes up when trying to submit a response (Happens at random times)

What further things can I try?

Edit: For what it's worth, the tablets are running Microsoft office 365 - 64 bit. And the Windows is also the 64 bit version.

Excel VBA Web-Query error saying cannot locate internet server or proxy server

0 Answers0