Excel 2016 - Macro working on Win10 1709 and older but not on Win10 1803

[Dutchmaste]

We received a excel sheet from a third party with macro's. It works fine when using Office2016 and Windows 10 build 1709 or older. But when using Windows 10 build 1803 it always goes to CONNECTION ERROR :S

When we go to the original website it give back the response it should, but whenever using the excel sheet it doesn't seem to work. Checked with out security team but they state no security rules should be blocking this.

Sub Workbook_Open()

   On Error GoTo Workbook_Open_error



   Dim strResult As String

   Dim objHTTP As Object

   Dim URL As String

   Dim cVersion As String

   Dim i As Integer



   cVersion = ThisWorkbook.Sheets(1).Range("A5")

   Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")

   URL = "http://www.awebsite.com/something.asp"

   objHTTP.Open "GET", URL, False

   objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"

   objHTTP.setRequestHeader "Content-type", "application/x-www-form-urlencoded"

   objHTTP.send

   strResult = objHTTP.responseText

   If cVersion <> strResult Or Len(strResult) = 0 Then

      MsgBox "LOAD SHEET EXPIRED Please download new version"

      Me.Application.ActiveWorkbook.Close SaveChanges:=False

   End If



   For i = 1 To Sheets.Count

      ThisWorkbook.Sheets(i).Protect Password:="************", UserInterfaceOnly:=True

   Next



Workbook_Open_exit:

   Exit Sub

Workbook_Open_error:

   MsgBox "CONNECTION ERROR"

   Resume Workbook_Open_exit

End Sub

 

[sijpie]

Use [ code=rich] to use colours or fonts in the code block

Sub Workbook_Open()
On Error GoTo Workbook_Open_error

Dim strResult As String
Dim objHTTP As Object
Dim URL As String
Dim cVersion As String
Dim i As Integer

cVersion = ThisWorkbook.Sheets(1).Range("A5")
Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
URL = "http://www.awebsite.com/something.asp"
objHTTP.Open "GET", URL, False
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
objHTTP.send
strResult = objHTTP.responseText
If cVersion <> strResult Or Len(strResult) = 0 Then
MsgBox "LOAD SHEET EXPIRED Please download new version"
Me.Application.ActiveWorkbook.Close SaveChanges:=False
End If

For i = 1 To Sheets.Count
ThisWorkbook.Sheets(i).Protect Password:="************", UserInterfaceOnly:=True
Next

Workbook_Open_exit:
Exit Sub
Workbook_Open_error:
MsgBox "CONNECTION ERROR"
Resume Workbook_Open_exit
End Sub

 

[sijpie]

At the moment your code is failing somewhere but because of the instructions it goes to the error line in your code and brings up the message you see.

If you comment out (put a ' in front of) the first line of code 'On Error GoTo Workbook_Open_error, then when the code fails you will get an error message where you can select to debug. Doing that will show the line where the code has failed. This will give us a bit more useful information.

 

[Dutchmaste]

At the moment your code is failing somewhere but because of the instructions it goes to the error line in your code and brings up the message you see.

If you comment out (put a ' in front of) the first line of code 'On Error GoTo Workbook_Open_error, then when the code fails you will get an error message where you can select to debug. Doing that will show the line where the code has failed. This will give us a bit more useful information.

Gives the following error:
Run-time error '13':
Type mismatch
Buttons Continue and Debug are deactivated. Checking the help button to see what Microsoft says about this.

 

[ScottInTexas]

What line throws that error?

 

[Dutchmaste]

Well thats the issue no debug button to select so above it the only thing it is giving me back. When clicking health it sends me to Run-time error 13

 

[sijpie]

In that case you can run the code in debug mode.

Open the VBA editor and click somewher in the sub. Then press the F8 key repeatedly to step through each line of the code. At some point you will get the error. I think it will be at the line objHTTP.Send

 

[Fluff]

Buttons Continue and Debug are deactivated.

Is the VBA project password protected?
If so unlock it & try again.