[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

Tools->References->

Visual Basic For Applications
MicroSoft Excel 14.0 Object Library
OLE Automation
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.

Happy Programming...........cheers

+1

0