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

Dutchmaste

New Member
Joined
Jun 14, 2016
Messages
17
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.

Code:
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
 
Last edited by a moderator:

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
Use [ code=rich] to use colours or fonts in the code block
Rich (BB code):
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

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
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

New Member
Joined
Jun 14, 2016
Messages
17
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

Board Regular
Joined
Oct 28, 2003
Messages
168

ADVERTISEMENT

What line throws that error?
 

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
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
 

Forum statistics

Threads
1,137,349
Messages
5,680,963
Members
419,946
Latest member
Trickay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top