How do I import a JSON file into Excel?

How do I import JSON to Excel 2016?

I have a requirement where I need to load some JSON data in to a spreadsheet, but I'm having trouble getting it to work. I can easily read the JSON into a text box and export it to a csv, but I need to load it into an Excel spreadsheet, so it needs to be in the form of a data table.

The code I am using is: Dim str As String. Str = GetJson(x). 'Get the result from http post request. Dim json As Object. Dim results As Object. Set json = CreateObject("MSXML2.XMLHTTP") With json. .Open "POST", "", False .setRequestHeader "Content-Type", "application/x-www-form-urlencoded" .setRequestHeader "Content-Length", Len(str) .send str Set results = .responseText End With. This then returns the following error on the Set results line: Run-time error '91': Object variable or With block variable not set. What am I doing wrong? You're trying to use the json object before you've actually assigned a value to it. If you remove the Set json = CreateObject("MSXML2.XMLHTTP") statement, you should be able to do the rest of your code without any errors.

How do I import a JSON file into Excel?

I have a JSON data file I'm trying to import into Excel 2023. I want to load this file from the hard drive in excel and have it pull out only the "ID" key in column 1, so that I can import this into Access and have it update the recordset. I have a code that pulls the keys out just fine, but I'm trying to feed the correct data into a recordset.

Any help would be greatly appreciated. Thanks! Code: Option Explicit. Public Sub readjson(). Dim strFilePath As String. Dim json As Object. Dim objFSO As Object. Dim oStream As Object. Dim strText As String. Dim I As Long. Dim x As Long. Const fsoAPath As String = "domehomeexcel". Const csvFolder As String = "C:datacsv". strFilePath = fsoAPath & "sample.json" '// Get the json text from the source. Set oStream = CreateObject("ADODB.Stream") oStream.Type = 2 oStream.Mode = 3 oStream.Open Set json = JsonConverter.ParseJson(oStream.ReadText)

strText = json. Set objFSO = CreateObject("Scripting.FileSystemObject") Set aStream = objFSO.