Excel to JSON Conversion using VBA macro

Hi,

Need to parse a excel sheet containing multiple sheets in it in to a JSON using VBA Macro.
And during JSON conversion below should be achieved.

1. The file has multiple sheets.
2. Create a Macro button in a new sheet and call it as Refresh JSON.
3. 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"


Thanks

You need to provide the following as a minimum:

1. A sample input workbook
2. Example Json output or at the very least a Schema

Rule 03: Cross-posting Without Telling Us

Your post does not comply with Rule 3 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

Post a link to any other forums where you have asked the same question. If you have fewer than 10 posts here, you will not be able to post a link, but you must still tell us where else you have asked the question.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

If you have less than 10 posts, do not try to copy and paste the link. Instead, type the link out in your thread.

No further help to be offered, please, until the OP has complied with this request.

hi intention is not to waste time only intention to seek help on this problem.

and it's posted on mrexcel website.

can someone please help now on my query soon?

also how to attach sample excel sheet and sample json file here as no attachment tab is available on this forum?

any updates please?

any forum leaders available to help on this issue please?

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Total 6 sheets in excel work book ( from column headings A to R (total 18 columns in it) and first column of each sheet has hyperlinked data and names of these 6 sheets be sheet 1,sheet 2,....sheet 6. This should get parsed(only first column should get parsed like this as it has hperlinked data in it) as "Name": "filename", "URL": "file complete address". Other columns should get parsed as usually but all these parsing of all of these excel sheets should be at the same place where this original Excel file is stored let's say it's stored in D drive so once 'Refresh JSON' button which is created in sheet 7 is clicked all these parsed sheets should be Written 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.

Again, without an input workbook and the sample Json output, no one can help you

and in this JSON output file sequence of parsing should be as per sequence of excel sheets like first sheet 1 then sheet 2 ..and at bottom sheet 6. So parsed excel sheet 1 should 1 at top top and parsed sheet 6 should be at bottom in same JSON file.

I think I’m wasting my time here ��

Please find sample excel and json file for the same.

Where's the json?

json outfile is however .json format but unable to upload it on this forum on this format so uploading a sample output file in text format for the same.

How's this:

Please Login or Register  to view this content.

You'll need to download and put the code from here into a module called "JsonConvert": https://github.com/VBA-tools/VBA-JSO...nConverter.bas

Copy and paste everything expect the top line into a new module
Name that module JsonConvert
Add a reference to the Microsoft Scripting Runtime

Output from your file attached:

Thanks, But when assigning this macro by creating a new button in attached excel sheet and naming it as 'Refresh JSON' Then error is appearing attached is the screen shots for the same.

It means that your workbook isn't set up like your sample - i.e you don't have a table on every sheet

You need a table on every sheet as per your example and they must all start in A1

no all sheets are same like the example and just one extra sheet added 'Sheet 7' and in it's A1 cell created a click button 'Refresh JSON' and assign it the same macro which is shared by you and when clicking on this button i am getting error as pasted in previous post.

and it's type was 'excel Macro enabled workbook'.

Does the extra sheet, sheet 7 have a table in it?

no table in sheet 7 just a 'Refresh JSON Button' in cell A1, and assigned a macro shared by you to it and upon clicking this refresh JSON button I was expecting all previous 6 sheets would get parsed at same location where this excel is but nothing such is happening instead of it error is appearing as pasted in previous post.

Then it isn't the same as the sample workbook you uploaded.

Originally Posted by Kyle123

You need a table on every sheet as per your example and they must all start in A1

You requested:

Originally Posted by itpgm21

a. Parse data in all sheets into JSON. The number of sheets and number of columns in each sheet might vary.

That's what the macro does.

If you remove sheet 7 and run the macro from the dialog box, then it will work correctly

i am not sure where is this dialog box for running this macro but as per my very first post :-

I need a 'Refresh JSON' button to parse data of all these 6 sheets and it was mentioned in very first post itself like below:-

"
Need to parse a excel sheet containing multiple sheets in it in to a JSON using VBA Macro.
And during JSON conversion below should be achieved.

1. The file has multiple sheets.
2. Create a Macro button in a new sheet and call it as Refresh JSON.
3. 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"

still error 424 for this macro please refer attached screen shot for the same.(it's coming when creating new sheet 7 and assigning it the macro shared by you as above code.)

not sure how to do this could you please explain step by step like when i click on this link i get a lengthy 1124 lines of code,so in this code what i need to do?
should i use these 1124 lines of code for my 'Refresh JSON' button assigned macro?

No, I’ve given you step by step instructions.

I’ll call out to other forum members to see if they can help you

yes please it would be much appreciated.

as still not sure what to do of these 1124 lines of code shared by you in previous link.

can someone please explain it in detail as some confusion is here :-"You'll need to download and put the code from here into a module called "JsonConvert": https://github.com/VBA-tools/VBA-JSO...nConverter.bas

Copy and paste everything expect the top line into a new module
Name that module JsonConvert
Add a reference to the Microsoft Scripting Runtime"

Which part are you having difficulty with?

i did as advised above but after step no.5 what should i do should i add new click button 'Refresh JSON' in sheet 7 and assign it the macro pasted in above link(after removing first line in the link code) and then should i expect all 6 sheets data would be parsed in .json file in same location automatically where this main excel(which has 6 sheets and module(Json Convert) ) in it?

Nothing after step 5. If you have done that correctly then the original code will work and you will have a textfile with the json in at the location in the code here:

Please Login or Register  to view this content.

You don't need to change your button

But I needed to create a button 'Refresh JSON' in sheet 7 and upon clicking it parsing in .json file format should have happened for all the 6 shets in this excel also
after completing all above 5 steps i am not getting any .txt file either whereas original requirement was to have parsed json file in .json format at same location(excel).

You've already created the button and assigned the macro to it!!!!

None of that has anything to do with the last 20 posts where you've been struggling to copy and paste.

Have you looked in C:\temp\ to see if there is a text file there called txt with the json in?


Attach the workbook, exactly as you have it set up now.

How ever one file is there in C:\temp\ seems parsed one but i want it to be generated .json file format and it should only be generated when i am clicking on this 'Refresh JSON' button but even unable to assign macro (minus top line in link) to it,to assign macro to this button 'Refresh JSON' should populate but it's not getting populated,attaching excel for more clarification as it is.

also how did it come in C:\temp\ whereas i was nver able to assign macro to 'Refresh JSON' button created in sheet 7 in A1 cell,it's amazing,however it should only have been generated if i had clicked on 'Refresh JSON' button in sheet 7.

It is in the json file format. Do you mean you want a .json extension on the file?

Why have you deleted the original code I gave you? You need both lots of code - this is incredibly hard work, are you being purposefully obtuse?

In a module, put the following:

Please Login or Register  to view this content.

Assign this macro to the button in sheet 7, KEEPING THE CODE THAT YOU HAVE PUT IN JsonConvert

On the assumption that the workbook is saved (it needs to be saved to have a file path), you will have a file called output.json in the same directory

also don't you think 1123 lines of code for this task is too much? can it be done in fewer lines of code?

yes,i want .json extension on the file and above pasted code is to be put above or below on KEEPING THE CODE THAT I HAVE PUT IN JsonConvert

if both the codes link plus above pasted codes are required then i need a sequence of these whichone should come first and which one should come after.

No, you don't. Just use the instructions in post #43

no,task is not done yet i need a sequence of pasted code plus link so that when i click on 'refresh json' button in sheet 7 i should get .json extension file at same location but it's not happening yet.

You don't need a ******* sequence, what sequence are you talking about? Just follow the instructions in post 43

actually i had followed thread 36 by humdingaling to understand it in easy way (sequence of steps)

after assigning the macro code pasted above i am getting compile time error,please refer attached screen shot for more information.

any solution to recover from this error?

now after clicking on 'refresh json' button nothing is happening. attached in the excel for your reference.please advise what is wrong happeining here
now link code plus pasted code both are there inspite of this it is not producing desired result.

experts please advise.

senior experts could you please advise soon?

hi, is anyone available on this forum for this issue please?

finally with these 2 set of codes link code and pasted code above it has started generating .json output file but i need a json data in format like which is appearing in c drive temp folder with txt name where top to bottom data is parsed like first pased for sheet 1(External Artifacts) then for sheet 2 and then ..finally for sheet 6.
it seems in .json output file format it's not getting parsed in vertical way like in txt file(in c drive temp folder) also it seems parsing is not happening for all the sheets,can experts please look in to this on.

Change

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

Though I don't know why it matters, whitespace is immaterial in json

Great and Thanks a lot for this help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.