How to Convert Excel Data to JSON Using VBA

hi my name is Eric and today I'm going to quickly go over how to convert your Excel worksheet data to JSON data all while using only Excel and VBA code and of course you can download the code that I'm showing you how to write using the link in the description this is on github and you can see the excel files right here so you can go ahead and get started immediately or you can stick around and watch the tutorial and get a good explanation of how this works so as you can see this is the final excel file that we are going to be creating and the first sheet we have just some sample data of some companies and their headquarters location and the industry they're in and you can see we have a header for each column here in the first row on the second sheet we have a button and when we click the button it will immediately fill in the next cell over here with our jason data that was created based off the data in the first sheet and it you can see it's in the format of an array of JSON objects with the column header for each property as each properties key so you can see company is in is the name of the first property headquarters name of the second property etc and it does that for each object in the array I'm also going to show you how to get your JSON data to come out as an array of arrays rather than an array of objects so if we go over and take a look at our VBA code we're going to change this one argument true and we're going to go back to the first sheet and get rid of our header row and then we're gonna click parse again and now you can see we have an array of string arrays and rather than an array of JSON objects and as you can see I have a new excel file open the only thing I've done so far is just paste the sample data I had in that other file into the first sheet and now that we have this we're going to create a new sheet and we're going to want to make some space here because we're gonna put our button and cell a1 and then we're gonna put our JSON data and cell b1 now I need to go over to the Developer tab up here in the ribbon if you can't see that you need to enable that in options to do that go to file options and then you're going to go to the customize ribbon menu and make sure that developer is checked and then choose ok and you should see the Developer tab here and the ribbon choose insert and then under form controls choose button and if you hold down alt you can make sure that the button that you're drawing here on the worksheet automatically snaps to the cell that you're over and it's gonna ask you what macro to assign we're going to hit cancel here because we're gonna be writing our macro in a minute we don't have a macro ready to assign the button I'm going to change the label here to just say parse and now we want to make sure that we save our excel file and we're gonna choose the location that we want to save it as you want to make sure that you save it as an Excel macro enabled workbook instead of just a regular Excel workbook so now that it's saved we are going to go ahead and jump into the code now you could write your code using the built in Visual Basic editor here in Excel but I'm going to be using Microsoft Visual Studio code instead simply because it does a much better job with syntax highlighting and auto completion however once we're done writing the code and Visual Studio code we're just going to copy it and paste it into excels VBA editor so it really doesn't matter what you use this is just easier and you can get Visual Studio code from Microsoft for free so we are going to just save this new file that I have open and you want to make sure that you choose all files as the file type and we're going to call it excel to Jason dot V B so room to write two functions and a sava sub is going to be what's run when you click the button so we're gonna call our sub parse data it's going to make sure that we have the correct cell that we want to assign our adjacent data to so we're going to select the worksheet that we're in worksheet two dot range and we want to select the cell that we want to put our jason data in in this case it's b1 and we want to set the value to the return value of our to jason function that we're about to write and then we're going to pass it the return value from and the other function that we're going to write called get values range and to this function we're going to pass what sheet we want to get our range of values from in this case it's sheet 1 since that's where our data is and we're going to pass it a false boolean for our second parameter and I will explain that in a minute now we need to write our get values range function and basically what this function does is it's going to look at the worksheet and figure out the range of the data that's in the worksheet so we switch back to excel if we go to the first sheet we we don't want to convert the entire worksheet including all of these blank cells to jason we don't want a bunch of empty strings in there so we only want to convert the range a 1 to C 16 and instead of having to provide this range man to our data parser we want to have it figure out the number of columns and the number of rows to parse automatically and that's what we're writing right now in our code we want to receive the sheet name as the string as one of our parameters and this function is going to return a range now let's declare our variables that we're going to use to keep track of what rows and columns we are currently in and the loops that we're about to write in this function we're going to assign this variable about you zero so I just want a value of one and this one a value of 100 so we are going to be looping through each cell and this function and we want to keep track of how many rows we've counted so far that we know have data in them and this is is of course going to be set to zero at first because as far as we know there's no data until we've counted it we're gonna use row counter to keep up with what row we're currently on in our loop and roasted count is how many rows we should count at first so if we copy these three variable declarations paste them again and now we're going to change the word row two column and we're gonna change the value four columns to count to fifty because there's most likely going to be more rows than there are columns in any given data set so we're gonna only initially check the first 100 rows and the first 50 columns so now let's write our first do-while loop and inside this we're going to have a nested loop and and here we're going to have an if statement if worksheets and then we're gonna pass it a sheet name that we were given dot cells we're going to pass the current row that we're on and the current column that we're on if this cell if it's value is not equal to a nippy string which means that it has data in it then we're gonna want to do some stuff with this cell we're going to record this cell as one of our used rows so we want to include it and then we're going to check to see if the column counter is greater than columns or used columns and if it is then we're going to set used columns equal to column counter then we're going to check to see if you used rows equals rows to count then we're going to we're going to increase rows to count because this is limiting how many we're going to loop through if the hundreth row has data then we're going to want to keep checking more rows we're not going to want to stop at the hundredth row so we're going to add 100 to the rows to count variable so it equals to itself plus 100 and then we're going to do a similar thing here with columns if used columns equals columns to count then then we're going to increase columns to count by 50 then outside of this initial if statement here after it ends we're going to increment column counter this way we can check the next column and then after this first loop but before the second loop closes here we want to increment the row counter and we want to reset the column counter because we always want to check the first column whenever we are checking a new row and after the outside loop here we're going to return the range that we found that has data so we're going to set our function equal to worksheets sheet dot range worksheets she and of course we're getting the range here so our first cell in the range is going to be the first cell a1 and then we're going to be getting the address of the last cell what we found and that's it for this function now we need to write our second function here which is called to Jason and we want the first parameter to be called range to parse and it is a range the second parameter is going to be parse as a raise and this is going to tell us whatever is calling this function if they want us to return adjacent string with the data as an array of arrays or not and of course this is going to be returning a string let's declare our variables and we're going to be looping through our range that we're given as an argument so we need some variables to help us keep track of where we are in our and loops and we want to set parsed data equal to an Open bracket and finally we're going to declare a string called tip now we're going to check the value of our parse as a raised parameter the argument that we're passed we're gonna see if it's true then we're gonna do some stuff and if it's false we're gonna do some other stuff so if it's true then we know we need to return an array of a raise so we're going to have a for loop for row counter equals 1 to range to parse dot rows dot count we're first going to reset our temp value to an empty string and inside this for loop we're going to have another for loop the column counter equals 1/2 range to parse columns dot count inside this loop we're going to set temp equal to its self and we're going to concatenate double quotation mark and Visual Basic if you have two quotation marks inside of two other quotation marks that equals one quotation mark so we're gonna then add a range range to parse dot cells row counter column counter so that's where we're actually adding the cells value and then we're going to add another quotation mark and comma and then we're going to set temp equal to open bracket and then we want to add itself minus the last character because we have an extra comma and the temp variable that we don't need we want to remove the last character and in order to do this we're going to use the left function pass it the string and then for the length of the string to grab we're going to use the length of the string minus one and then we're going to concatenate a closing bracket and then we're going to set parsed data to itself with tip tacked on to the end so now we are done with this first part of the if statement if we need to return an array of JSON objects we're going to run the code that we're about to write which has a for loop for Road counter equals to two range to parse dot rows dot count the reason why we're setting this equal to two instead of one like we did up here is because if we are creating an array of JSON objects then we don't want to include the column header which is going to be the first row so we're starting with the second row because that's where our data actually starts so first thing we're gonna do and this loop is we're going to set tip variable equal to an empty string and then we're going to have another loop column counter equal one because the first column has actual data in it to range to parse dot columns inside of this nested loop we're going to set tip equal to itself and the quotation mark and range to parse dot cells and then we're going to reference the first row because that's where our header is and then like column counter so we can get the property for our property that we are placing into this JSON object and then another quotation mark and get cool one and another quotation mark and range to parse now we're actually placing in the property's value here dot cells row counter column counter and another quotation mark and finally a comma and that's it for this loop after this loop finishes we're going to set temp equal to a curly bracket and itself except for the last character and we're going to tack on the closing curly bracket and a comma and then we're going to set parsed data we pull to itself and tip and now we need to write the code that runs after our initial if statement we're going to set parsed data equal to itself except for the last character which is an extra comma and then we're going to tap on the closing bracket and then we're going to return parsed data so now we've written all the code that we need we're gonna hit control a and copy our code go back to excel and we are going to switch to sheet 2 and go over to the Developer tab here in the ribbon and choose visual basic right click and this side pane choose insert module and then you're just going to paste the code close this window we're going to right click on our button and choose assigned macro and now we're going to choose the macro that we created called parse data and then choose ok and click parse and now you can see we have a JSON string of our data with an array of objects with each property with the proper key and proper value and now we can test to see if we got the other part right where we just want an array of arrays so we're going to get rid of the first row which is our header we're going to go back to visual basic change this argument to true so that it knows we want an array of arrays and we're going to hit parse again and now you can just see that we don't have the property keys anymore we just have just plain strings and it all reflects the data in our first sheet thank you for watching I hope it's been helpful and remember you can get all of the code to do whatever you want with from github via the download link in the description thanks

Channel: Eric Burnett

Views: 20,184

Keywords: Excel, JSON, VBA, Visual Basic, Tutorial, Macro, Excel Macro

Id: YgwUS9vvTTU

Length: 25min 58sec (1558 seconds)

Published: Tue Dec 19 2017