Fetch data through an API call which requires credentials using Excel VBA

Question

I am trying the fetch data through an API call which requires credentials (API key and password).

I can do it through Python script. I would like to do it through Excel VBA because I want to download the data and formatting in same excel sheet in one click.

I have tried many things through VBA Excel after taking the help from many developer community websites.

Python code snippet -

import requests
import json

api_key = "NotLikeToMention1"
domain = "NotLikeToMention2"
password = "NotLikeToMention3"

r = requests.get("https://"+ domain +".freshdesk.com/api/v2/tickets?filter=new_and_my_open", auth = (api_key, password))

data = r.json()
print(data)

I tried the below code in Excel VBA. I also tried using authorization through setRequestHeader property. While using these ways, I get an error something like

'credential error, you will have to login'.

Dim response As String

With CreateObject("Microsoft.XMLHTTP")
  .Open "GET", address, false, emailId, password
  .Send
  response = .responseText
End With

Msgbox response

I'd recommend either doing all your work in Python, using an [Excel library](https://www.quora.com/What-python-libraries-are-best-for-working-with-Microsoft-Excel) or if you want to use VBA, use a [JSON parser](https://github.com/VBA-tools/VBA-JSON) and be mindful of [how the data is returned to the code](https://stackoverflow.com/a/46245469/4717755). — PeterT, Aug 03 '19 at 22:14
@PeterT Yes, I will use Json parser, however as of now I am not able to get the Json string after calling API get request. I am struggling to find how to call a API link which requires credential. — Piyush, Aug 04 '19 at 07:25
@LuizLai I don't know how to check the status. If I am sign in at Freshdesk portal then I can directly open this url in browser and I can see the huge json string. Through python also I can see, but it I am trying to do it through VBA excel then I am getting error which is popping up through a error message window and title of that error message is "Microsoft Excel" and full error message is "{"code":"invalid_credentials","message":"You have to be logged in to perform this action."" — Piyush, Aug 04 '19 at 08:02
I'd keep your code in Python and wrap it a COM class that is callable from VBA http://exceldevelopmentplatform.blogspot.com/2018/01/calling-python-class-using-vba-with-com.html — S Meaden, Aug 04 '19 at 14:52
Use Fiddler to compare the actual request from the Python script vs VBA code. — omegastripes, Aug 04 '19 at 15:37
Use `MSXML2.XMLHTTP` instead of deprecated `Microsoft.XMLHTTP`. Also try `MSXML2.ServerXMLHTTP`. — omegastripes, Aug 04 '19 at 17:27

score 0 · Answer 1 · answered Apr 25 '21 at 18:22

Maybe this could be an option for your problem.

There is a simple way to fectch data from API using Power Query, and formmating JSON into Excel files.

This is several great example

Fetch data through an API call which requires credentials using Excel VBA

1 Answers1