Thanks Eric, your code developed in VBA was very useful, with small changes I was able to adapt it to my needs (SQL Server). Thank you with all my heart. Greetings from Rome . 💪🏻💻👍🏻
Dang, mayn. What happened? 2 vid, and this one is from 2017?! I'mma subscribe, just in case you keep em comin. Figures that the only vid I've come across with what I looked for was from someone not even trippin off makin more vids.
Hi Eric For the GetValuesRange Function Try This Function GetValuesRange(Sheet as String) As Range GetValuesRange = Worksheet(Sheets).range("A1").currentregion End Function
I can't open the .xlsm file. Tried it on two different computers, it gives me the following error: Excel cannot open the file 'Excellent JSON.xlsm' because the file or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file. Do you have any idea what is wrong?
Is there a row and column limit in this? Because I tried it but not able to convert everything to .json (ps. I have like more than a thousand rows and just 6 columns)
@@ericburnett5612 And how to Create a Macro button in a new sheet and call it as Refresh JSON. and On click of the above button, the following should be achieved a. Parse data in all sheets into JSON. The number of sheets and number of columns in each sheet might vary. b. Write this into an output file as XYZIndex.json - Single out put file containing data from all sheets . Save the file in the same location as the excel. c. Note that Column A (Name) of every sheet has hyperlinked data. This should get parsed as "Name": "filename", "URL": "file complete address"
You probably dont care but if you guys are stoned like me atm you can stream all the new movies on Instaflixxer. I've been watching with my gf for the last couple of days :)
This works great, however I have a 450 row and 3 columns to export to json, and it uses more than the 32676 characters allowed in each cell. Anyway to have it export in a text file or something instead?
thanks a lot eric but im getting an overflow error. In my case i have 153.972 rows and 40 columns and all are numbers instead of names besides the first row. How can i fix this? much appreciated...
yeah but now do some more complexity, linked tables/lists, arrays of objects in objects in objects, or dont quote numerics or booleans and further syntax changes to simplify possible user input in the table(ie merges/ prefix/suffix/ ect)
do you have any examples in case i have to create a nested json. { xxxx:xxx, "yyy":[ { "aaa": "aaa","bbb":"bbb"}],"nnn":"nnn"} somthing like this.... really in need for something like this asap
nested json, has to be coded seperately. ex: { "people" : [ { "name" : "Rajesh" , "id" : 123 } ] } will be in a excel file with a seperator for example like underscore '_'. The above json will be in excel as --- row1) people_name, Rajesh | row2) people_id, 123
Thanks Eric...I like your code and thanks for sharing it. I just wanted to let you know that I changed the getValuesRange function because I have a cleaner way to get this same range. It is a little hard coded in terms on columns which for my work is fine but I thought that I would share it with your. Thanks: Function getValuesRange2(sheet As String, StartingCell As String) As Range ' Row variables Dim rngStart As Range Dim rngWholeRange As Range With ThisWorkbook.Sheets(sheet) Set rngStart = .Range(StartingCell) Set getValuesRange2 = .Range(StartingCell, .Range(StartingCell).End(xlDown).Cells(1, 9)) End With End Function
Hey Eric, Thanks, this tutorial was very helpful Can you also post a video of doing a reverse action? I want to convert the JSON data to excel and cannot find a good tutorial for it. Thanks
Thanks Eric, your code developed in VBA was very useful, with small changes I was able to adapt it to my needs (SQL Server). Thank you with all my heart. Greetings from Rome . 💪🏻💻👍🏻
Dang, mayn. What happened? 2 vid, and this one is from 2017?! I'mma subscribe, just in case you keep em comin. Figures that the only vid I've come across with what I looked for was from someone not even trippin off makin more vids.
Hi Eric
For the GetValuesRange Function Try This
Function GetValuesRange(Sheet as String) As Range
GetValuesRange = Worksheet(Sheets).range("A1").currentregion
End Function
I can't open the .xlsm file. Tried it on two different computers, it gives me the following error:
Excel cannot open the file 'Excellent JSON.xlsm' because the file or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file. Do you have any idea what is wrong?
That is strange indeed. If you delete the file and re-download it, does anything change? Also, what version of Excel are you using?
Very lucid explanation, Eric. Really appreciate the content and thanks for providing the code.
Is there a row and column limit in this? Because I tried it but not able to convert everything to .json (ps. I have like more than a thousand rows and just 6 columns)
Thank you Eric. This tutorial has been very helpful for my interview. I’m very grateful.
Glad it helped!
@@ericburnett5612 And how to Create a Macro button in a new sheet and call it as Refresh JSON.
and On click of the above button, the following should be achieved
a. Parse data in all sheets into JSON. The number of sheets and number of columns in each sheet might vary.
b. Write this into an output file as XYZIndex.json - Single out put file containing data from all sheets . Save the file in the same location as the excel.
c. Note that Column A (Name) of every sheet has hyperlinked data. This should get parsed as "Name": "filename", "URL": "file complete address"
You probably dont care but if you guys are stoned like me atm you can stream all the new movies on Instaflixxer. I've been watching with my gf for the last couple of days :)
@Collin Jamal definitely, I've been using instaflixxer for since november myself :D
This works great, however I have a 450 row and 3 columns to export to json, and it uses more than the 32676 characters allowed in each cell. Anyway to have it export in a text file or something instead?
Use a textbox instead of cell
@@rushrush6754 Is the textbox the name of the command? Sorry, I'm new and I need to export JSON almost 2150 rows and 15 columns.
@@Nay92nve No, if you don't know what is a text box then you probably can't do. It's very simple but it requires knowledge in VBA.
@@Nay92nve I will do the update with textbox and share in a few moments in comment
Excellent Eric. I appreciate your teaching skill while coding. God Bless you.
This is excellent detailed explanation...thanks!
thanks a lot eric but im getting an overflow error.
In my case i have 153.972 rows and 40 columns and all are numbers instead of names besides the first row.
How can i fix this? much appreciated...
yeah but now do some more complexity, linked tables/lists, arrays of objects in objects in objects, or dont quote numerics or booleans and further syntax changes to simplify possible user input in the table(ie merges/ prefix/suffix/ ect)
You saved my day! Thanks
do you have any examples in case i have to create a nested json. { xxxx:xxx, "yyy":[ { "aaa": "aaa","bbb":"bbb"}],"nnn":"nnn"} somthing like this.... really in need for something like this asap
nested json, has to be coded seperately. ex: { "people" : [ { "name" : "Rajesh" , "id" : 123 } ] } will be in a excel file with a seperator for example like underscore '_'. The above json will be in excel as --- row1) people_name, Rajesh | row2) people_id, 123
I have an excel with more than 300 row and it is parsing half of it, can someone help me?
Thanks Eric. You make my day:)
Thanks Eric...I like your code and thanks for sharing it. I just wanted to let you know that I changed the getValuesRange function because I have a cleaner way to get this same range. It is a little hard coded in terms on columns which for my work is fine but I thought that I would share it with your. Thanks:
Function getValuesRange2(sheet As String, StartingCell As String) As Range
' Row variables
Dim rngStart As Range
Dim rngWholeRange As Range
With ThisWorkbook.Sheets(sheet)
Set rngStart = .Range(StartingCell)
Set getValuesRange2 = .Range(StartingCell, .Range(StartingCell).End(xlDown).Cells(1, 9))
End With
End Function
Sure John, I'm happy you found it helpful. And thanks, I appreciate your sending me an optimization!
Very good! Very clear! Very simply explained! Thank you! What about reading data from a JSON string and store it in a Range?
Thank you Eric , you saved me :) , what if i have more than 1 sheet in same workbook how can i get all sheets in one button and in same cell ?
Thanks Man!
Im getting 1004 error ..can u share the code as such?
You help me alot. Thanks
If there is integer, how to add integer column without using " " . Thank you.
I got it now. I just filter the column using If condition. Thanks :
If IsNumeric(rangeToParse.Cells(rowCounter, columnCounter)) Then
temp = temp & """" & rangeToParse.Cells(1, columnCounter) & """" & ":" & rangeToParse.Cells(rowCounter, columnCounter) & ","
Else
temp = temp & """" & rangeToParse.Cells(1, columnCounter) & """" & ":" & """" & rangeToParse.Cells(rowCounter, columnCounter) & """" & ","
End If
Thanks.
Hey Eric, Thanks, this tutorial was very helpful Can you also post a video of doing a reverse action? I want to convert the JSON data to excel and cannot find a good tutorial for it. Thanks
Prerna Singh you can pull in JSON using power query or get & transform
thankyou so much