JsonText = ws.Cells(1, 1) This step is not necessary for the code to work for us. We could simply reference the cell in the worksheet that contains the JSON. However, most of the time you’ll be returning your JSON string from another data source, most likely a HTTP GET Web Call.

If you’re not familiar with HTTP Requests in VBA, to learn more. Lastly, to put the JSON string into the jsonObject, we will use one of the methods contained in the JsonConverter file you imported to begin this example. Since the builder of this library made all of the subroutines public, it is callable from the module this code is in.

That call looks like this. Set jsonObject = JsonConverter.ParseJson(jsonText) This call instructs the JsonConverter module to parse the JSON string you’ve passed it using the jsonText variable to a collection of dictionaries that we can now access. Now that we’ve finished our setup, we can start learning to import JSON to Excel in the next section. Import JSON to Excel The code to import JSON to Excel might sound daunting, but it is much easier than it sounds. At a high level, all you have to do is loop through the jsonObject you’ve created, and write each dictionary value to the sheet.

To change the excel vba module name, from the tools menu go to Macro From the dropdown select visual basic editor. Then select the module under the VBAProject which houses your filename. Jan 4, 2017 - The RefEdit reference was missing on MacOS. I removed it and now it seems to work.

Quicken for Mac Download Bank Files Issue I went back to Quicken for Mac 2007 last week (Lion version), having tried a couple of other programs. I still like QM 2007 best, especially for the reporting features. I used my QM 2007 data file, which was only a few months out of date, and simply brought it current. Quicken today announced the launch of Quicken 2019, the newest version of its popular finance and budgeting software for PC and Mac. Quicken 2019 introduces web access for the first time, designed. Quicken for mac rumors 2011 download. I was using Quicken Deluxe 2010 under Parallels and was really tired of dedicating system resources for it (that and the Rhapsody App were pretty much the only reasons I had Parallels running). Quicken today announced the launch of the 2018 version of its popular finance and budgeting software for the PC and Mac. Quicken 2018 introduces access to online bills from more than 11,000.

The wonderful thing about dictionaries, is this is easy to do with a for each loop. I’ve added a couple of variables and a for each loop to our code and the result is this. Sub JsonToExcelExample() Dim jsonText As String Dim jsonObject As Object, item As Object Dim i As Long Dim ws As Worksheet Set ws = Worksheets('JSON to Excel Example') jsonText = ws.Cells(1, 1) Set jsonObject = JsonConverter.ParseJson(jsonText) i = 3 ws.Cells(2, 1) = 'Color' ws.Cells(2, 2) = 'Hex Code' For Each item In jsonObject ws.Cells(i, 1) = item('color') ws.Cells(i, 2) = item('value') i = i + 1 Next End Sub I set a counter variable, i, that I can use to tell the loop which row to write the data to. Next, create my column headers for the data.

Then, for each “item” in the jsonObject write that dictionaries values to the cell row I indicate and increment my counter variable. The results of our import JSON to Excel code looks like this: As you can see, we’ve turned that odd-looking string of text in cell A1 to an easy to understand table of data with just a few lines of code! Understanding JSON to Excel (Advanced) Now, I do want to point out that understanding the structure of the JSON you’ll be parsing is very important. Within the for each loop, the code can access the value of each object by referencing its key. This goes back to understanding the key/value pair relationship of JSON and the dictionary structure to which it is parsed.

The reason I stress this so much is because, when you’re pulling back more complex looking JSON, you may need to have multiple levels of dictionary references to access the value you want. There may also be times when you have to nest for loops when you have an array of objects with arrays of objects nested inside each object. For example, let’s look at an example where the JSON is structured as a single object with key “data” and the value is an array of data about employee’s and their organizations: As you can see, we have an object with an array nested inside. Some objects in the array contains other nested objects as well.