search

VBA-tools / VBA-JSON

JSON conversion and parsing for VBA
MIT License
1.79k stars 573 forks source link

Help with json #183

Open ul9192 opened 3 years ago

ul9192 commented 3 years ago

Morning I am working on a project for myself to try to understand how json works with vba and I am happy to say I did some good progess when the json file is simply structured but now I am facing a challenge and I have no idea how to solve it.

I attached my json file as an example. When I parsed it using jsonconverter and I want to paste result in a cells I have got always an "object required" error

    Dim things_json2 As Object
    Set things_json2 = JsonConverter.ParseJson(things2)

    Dim Device2 As Object
    Dim i2 As Long

i2 = 3

ws2.Cells(2, 1) = "Activity Type Id" ws2.Cells(2, 2) = "Activity Type Name"

For Each Device2 In things_json2 ws2.Cells(i2, 1) = Device2("userId") ws2.Cells(i2, 2) = Device2("personId") i2 = i2 + 1 Next Device2

I think it's due to the way the json is structured. If I do something like this, it works but I need the value before which are the frist in the file

    Dim things_json2 As Object
    Set things_json2 = JsonConverter.ParseJson(things2)

    Dim Device2 As Object
    Dim i2 As Long

i2 = 3

ws2.Cells(2, 1) = "Activity Type Id" ws2.Cells(2, 2) = "Activity Type Name"

For Each Device2 In things_json2("personDomain") ws2.Cells(i2, 1) = Device2("domainId") ws2.Cells(i2, 2) = Device2("userSecurityRoleId") i2 = i2 + 1 Next Device2

If someone could have an idea on how to solve this problem it would be really appreciated. Again I am a beginner probably I don't understand everything in the json "concept"

user.txt

houghtonap commented 3 years ago

VBA-JSON maps json objects to the VBA Scripting.Dictionary object and json arrays to the VBA Collection object. These objects, unlike JavaScript, have different methods for retrieval and storage of values. See Microsoft developer website for information on these objects.

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dictionary-object

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/collection-object

Note, you need to add a VBA project reference to the Scripting.Dictionary objects DLL to see and use it with intelligence. In Excel, Developer ribbon tab, Visual Basic icon in the Code section, then the Tools menu, References... choice. Scroll the list and check Microsoft Scripting Runtime, then click OK. If you don't see the Developer ribbon tab, then you need to enable it in Excel's options under Popular, check Show Developer tab in the Ribbon.

Hope that helps, Andrew.

From: ul9192 notifications@github.com Sent: Thursday, December 10, 2020 5:52:08 AM To: VBA-tools/VBA-JSON VBA-JSON@noreply.github.com Cc: Subscribed subscribed@noreply.github.com Subject: [VBA-tools/VBA-JSON] Help with json (#183)

Morning I am working on a project for myself to try to understand how json works with vba and I am happy to say I did some good progess when the json file is simply structured but now I am facing a challenge and I have no idea how to solve it.

I attached my json file as an example. When I parsed it using jsonconverter and I want to paste result in a cells I have got always an "object required" error

Dim things_json2 As Object
Set things_json2 = JsonConverter.ParseJson(things2)

Dim Device2 As Object
Dim i2 As Long

i2 = 3

ws2.Cells(2, 1) = "Activity Type Id" ws2.Cells(2, 2) = "Activity Type Name"

For Each Device2 In things_json2 ws2.Cells(i2, 1) = Device2("userId") ws2.Cells(i2, 2) = Device2("personId") i2 = i2 + 1 Next Device2

I think it's due to the way the json is structured. If I do something like this, it works but I need the value before which are the frist in the file

Dim things_json2 As Object
Set things_json2 = JsonConverter.ParseJson(things2)

Dim Device2 As Object
Dim i2 As Long

i2 = 3

ws2.Cells(2, 1) = "Activity Type Id" ws2.Cells(2, 2) = "Activity Type Name"

For Each Device2 In things_json2("personDomain") ws2.Cells(i2, 1) = Device2("domainId") ws2.Cells(i2, 2) = Device2("userSecurityRoleId") i2 = i2 + 1 Next Device2

If someone could have an idea on how to solve this problem it would be really appreciated. Again I am a beginner probably I don't understand everything in the json "concept"

user.txthttps://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FVBA-tools%2FVBA-JSON%2Ffiles%2F5672129%2Fuser.txt&data=04%7C01%7C%7Cc1e8399c9b3342bc77da08d89cf9a4e2%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637431943309677978%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=5nLlRu%2FXAQdIA37NUBQ9JnabzpFSkh1mv3bgwegFwQs%3D&reserved=0

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHubhttps://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FVBA-tools%2FVBA-JSON%2Fissues%2F183&data=04%7C01%7C%7Cc1e8399c9b3342bc77da08d89cf9a4e2%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637431943309677978%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=fPN6%2BeaaHBdSY1rsPOfUf8WRwogM%2FsfHhj8JJsJGM0Y%3D&reserved=0, or unsubscribehttps://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAIKTRA5AV4DWDHAEAWFDTLTSUCR5RANCNFSM4UU2W4SA&data=04%7C01%7C%7Cc1e8399c9b3342bc77da08d89cf9a4e2%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637431943309687971%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=qz1x%2BTZiARyCXq%2FgMseHQE0rEOVTTx7gbTsNaXoTyYE%3D&reserved=0.

ul9192 commented 3 years ago

Thank you Andrew. Microsoft Scripting Runtime is already added to the project. As I said I am really not an expert and what you asked me to read is a bit like chinese for me. Thanks again