topic SD-WAN API Calls from Excel VBA failing in DevNet Sandbox

SD-WAN API Calls from Excel VBA failing

ROBERT STEVENS — Tue, 04 Feb 2025 19:43:07 GMT

Hello,

I am working on a tool that is built on/in an Excel Workbook - and would like to pull data directly from vManage via API. I am able to get authenticated via Postman however when I attempt to pull from inside an Excel Workbook (using VBA) i am unable to successfully get a response (to the initial Post) so I never get a Cookie (and so I truly am not even in the game yet)......

After I send the POST -- I get a run-time error -- 2146697208 (800c0008) and a description that states "The download of the specified resource has failed"

I get no response from the API Call.

I am curious if anyone has successfully written code to interact with the vManage API using Visual Basic or more specifically a macro-enabled workbook with VBA Code to pull data via the API.

As a tangent - this working using the Meraki API but the authentication is ENTIRELY different.....

Thanks in advance for any info

Re: SD-WAN API Calls from Excel VBA failing

bigevilbeard — Wed, 05 Feb 2025 09:50:26 GMT

This is very interesting, this error a very strange one, normally this mean that there was an issue with the download of the resources, which could be due to a few things such as network issues, incorrect URL, or issues with the server.

What are you using for request set up WinHttp.WinHttpRequest or XMLHTTP? Can you share your code?

Re: SD-WAN API Calls from Excel VBA failing

ROBERT STEVENS — Wed, 05 Feb 2025 16:34:13 GMT

Hi -- sure am happy to share -- note that it looks like I have it working now... I dont know WHY but - my original code was using MSXML2.XMLHTTP. After reading your response I started toying with WinHttp.WinHttpRequest.5.1. I will admit I truly dont know the differences between the two object types - but switching to WinHttp.WinHttpRequest.5.1 looks to have done the trick... I will post the code that appears to be working instead of what was NOT working... The only significant difference was the use of the other object type.....

Two things to note - the funky URL is because I am currently using this to talk to a lab environment built in CML.... Once i have everything setup and working as expected, I'll talk to our production vmanage... Also - when I switched to the new object, I stopped getting the other error but got a message about bad cert -- so you will see I am setting up to ignore the cert error (.option(4) = &H3300)

Appreciate your help.... May come back later if i find more issues, but for now I am on the right track...

Sub test()
Dim http As Object
Dim url As String
Dim username As String
Dim password As String
Dim jsonBody As String
Dim response As String
Dim token As String

' Initialize variables
url = "https://127.0.0.1:8443/j_security_check"
username = "xxxx"
password = "yyyy"

' Create JSON body for the request
jsonBody = "j_username=" & username & "&j_password=" & password

' Create the HTTP object
Set http = CreateObject("WinHttp.WinHttpRequest.5.1")

' Send the POST request
With http
.Open "POST", url, False
.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.Option(4) = &H3300
.send jsonBody
'response = .responseText

If .Status = 200 Then
' Extract the token from the response headers
token = http.getResponseHeader("Set-Cookie")
MsgBox "Authentication successful! Token: " & token
Else
MsgBox "Authentication failed. Status: " & http.Status & " Response: " & response
End If
'
End With

End Sub

Re: SD-WAN API Calls from Excel VBA failing

bigevilbeard — Wed, 05 Feb 2025 16:46:38 GMT

Amazing, happy to help here. I am not an expert on this stuff, but recalled in the grey matter of my mind someone once saying the WinHttp.WinHttpRequest object is more robust for handling API requests compared to XMLHTTP, not something i have played with to comment more, but if its now working yipee right!