How to import JSON file to MS excel horizontally using VBA

Question

Hello i want to ask about some problem about excel, i have some data like this:

and i have import that JSON data to excel with modules from https://github.com/TheEricBurnett/Excellent-JSON

and my code form are

Private Sub ImportJSONFIle_Click()
    Dim fd As Office.FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
            .Title = "Select a JSON File"
            .AllowMultiSelect = False  
        If .Show() Then
            Filename = .SelectedItems(1)
            Dim content As String
            Dim iFile As Integer: iFile = FreeFile
            Open Filename For Input As #iFile
                content = Input(LOF(iFile), iFile)
                ' Parse JSON String
                Dim dummyData As Object
                Set dummyData = JsonConverter.ParseJson(content)
                i = 1
                For Each dummyDatas In dummyData
                    Cells(i, 1) = dummyDatas("nama")
                    Cells(i, 2) = dummyDatas("email")
                    i = i + 1
                    Next
                Close #iFile
        End If
    End With End Sub

finally the result is:

Here i want to ask how to make the data written horizontally not vertically? Here the result what i want :

Please don't post your code in images, edit your question and paste your code in it directly. — Raymond Wu
– Raymond Wu, Commented Sep 17, 2021 at 7:54
ohh thank you for your suggestion sorry because this my first post — Afif abiyyuna
– Afif abiyyuna, Commented Sep 17, 2021 at 8:11
In future, please also post your JSON in text as well. Anything that can be text should be posted in the question as text, not image. This makes it easy for people to test. — Raymond Wu
– Raymond Wu, Commented Sep 17, 2021 at 8:27

Raymond Wu · Accepted Answer · 2021-09-17 09:04:33Z

1

Since you could potentially deal with alot of entries from the JSON, it is recommended to populate the values in an array first then write into your worksheet.

Replace this:

For Each dummyDatas In dummyData
    Cells(i, 1) = dummyDatas("nama")
    Cells(i, 2) = dummyDatas("email")
    i = i + 1
Next

To this:

Dim outputArr() As Variant
ReDim outputArr(1 To 1, 1 To dummyData.Count * 2) As Variant

For Each dummyDatas In dummyData
    outputArr(1, i) = dummyDatas("nama")
    i = i + 1
    outputArr(1, i) = dummyDatas("email")
    i = i + 1
Next

Cells(1, 1).Resize(, UBound(outputArr, 2)).Value = outputArr

EDIT - To insert result after the last column

Dim outputArr() As Variant
ReDim outputArr(1 To 1, 1 To dummyData.Count * 2) As Variant

For Each dummyDatas In dummyData
    outputArr(1, i) = dummyDatas("nama")
    i = i + 1
    outputArr(1, i) = dummyDatas("email")
    i = i + 1
Next

Dim lastCol As Long
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column

Cells(1, lastCol + 1).Resize(, UBound(outputArr, 2)).Value = outputArr

edited Sep 17, 2021 at 9:04

answered Sep 17, 2021 at 8:35

Raymond Wu

3,4272 gold badges9 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Afif abiyyuna Over a year ago

wowww this code is work for me thank you sir, god bless you

Afif abiyyuna Over a year ago

sorry sir, I want to ask, can we determine the result with the desired column according to excel?

Raymond Wu Over a year ago

@Afifabiyyuna Sorry I don't understand your question, what do you mean determine the result with the desired column?

Raymond Wu Over a year ago

@Afifabiyyuna Side-note - If an answer resolves your question, you can accept it by clicking the tick beside the answer. (If there are multiple answers to your question, you can only accept one)

Afif abiyyuna Over a year ago

the point is, for example I want the result to be from column H because the previous result starts from column A

|

The KNVB · Accepted Answer · 2021-09-17 08:28:26Z

1

You may try to replace :

  Cells(i, 1) = dummyDatas("nama")
  Cells(i, 2) = dummyDatas("email")

with

  Cells(1,i) = dummyDatas("nama")
  i=i+1
  Cells(1,i) = dummyDatas("email")

answered Sep 17, 2021 at 8:28

The KNVB

3,8523 gold badges35 silver badges64 bronze badges

5 Comments

Foxfire And Burns And Burns Over a year ago

notice that at first loop, it will be cells(1,1) and cells(1,2) but on the second loop it will overwrite cell(1,2). I think you are missing and extra line of i=i+1 at the end

The KNVB Over a year ago

Another i=i+1 exists below the Cells(1,i) = dummyDatas("email"), so it should not overwrite cell(1,2)

Foxfire And Burns And Burns Over a year ago

Ohhh, So actually the extra i=i+1 line exists. I understand now. Yes, it would work.

The KNVB Over a year ago

Although the solution is dirty, it should work.

Afif abiyyuna Over a year ago

yeah the result almost worked but still broken :(

Foxfire And Burns And Burns · Accepted Answer · 2021-09-17 08:28:21Z

0

Not tested but this should work. Replace this:

Cells(i, 1) = dummyDatas("nama")
Cells(i, 2) = dummyDatas("email")
i = i + 1

With:

Cells(1, i) = dummyDatas("nama")
Cells(1, i+1) = dummyDatas("email")
i=i+2

answered Sep 17, 2021 at 8:28

Foxfire And Burns And Burns

12.1k2 gold badges19 silver badges42 bronze badges

1 Comment

Afif abiyyuna Over a year ago

the result almost worked but still broken :(

Collectives™ on Stack Overflow

How to import JSON file to MS excel horizontally using VBA

3 Answers 3

12 Comments

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

12 Comments

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related