[Solved] vba macro not working with https
Hello friends, I was working with excel programming. It was my first project in excel programming so I was very excited. I was already familiar with visual basic but didn’t developed any application using VBA.
My VBA application wasn’t much complex. Actually I have to get data from a web api and bind it with a excel sheet. I created the web api in asp.net and deployed it to a secure network means https instead of http.
First I tried with the in-secure network means http and I able to get the records by passing parameters to the api and it was working properly. When I tried to call the web api of secure network https it didn’t work and throw error 403, forbidden.
I did a lot of research on that and spend my 2 days to find the workaround of this problem but didn’t. Actually I was using WinHttpRequest library of vba to access the https network which doesn’t allow. At the end of second day finally I got it working.
Here the working code example
Please add below libraries references before run the code, otherwise it will throw errors
Visual Basic For Applications
MicroSoft Excel 14.0 Object Library
Microsoft Office 14.0 Object Library
MicroSoft Forms 2.0 Object Library
MicroSoft WinHTTP Services, Version 5.1
MicroSoft XML, v3.0
Dim status As String Dim statusText As String Dim responseJson As String Dim city as String: city="gurdaspur" Dim state as String: state="punjab" Dim url As String: url = "https://testapi/test/api/places?city=" & city & "&state=" & macAddress 'To Call secure connection network always use XMLHTTP instead of WinHttpRequest Dim oHTML As Object Set oHTML = CreateObject("Microsoft.XMLHTTP") With oHTML .Open "GET", url, False .Send HttpGet = .ResponseText status = .status statusText = .statusText End With If (status >= 400 And status <= 599) Then MsgBox "Error Occurred : " & statusText Else MsgBox "Success : " & statusText End If
if the status code come 200 then it means it's working otherwise throw the error like 400,403 something like that.
I am sharing this because I don't wanna someone else waste his two or more days to find the solution of this issue.
If it solve your problem then don't forgot to share and comment.