How to fill json parse result in next column in Excel?

Question

I tried to convert the json file to Excel.

I got the module from: https://github.com/VBA-tools/VBA-JSON/releases

My code:

Private Sub CommandButton1_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 lastCol As Long
            lastCol = Cells(Columns.Count, 1).End(xlDown).Column
            Dim dummyData As Object
            Set dummyData = JsonConverter.ParseJson(content)
            i = lastCol + 1
            For Each dummyDatas In dummyData
                Cells(i, 1) = dummyDatas("perusahaan")
                Cells(i, 2) = dummyDatas("nama1")
                Cells(i, 3) = dummyDatas("email1")
                Cells(i, 4) = dummyDatas("nama2")
                Cells(i, 5) = dummyDatas("email2")
                Cells(i, 6) = dummyDatas("nama3")
                Cells(i, 7) = dummyDatas("email3")

                i = i + 1
                Next
            Close #iFile
        End If
    End With
End Sub

My JSON file :

    [
  {
    "perusahaan": "Indofood",
    "nama1": "afif",
    "email1": "afif@gmail.com",
    "nama2": "dino",
    "email2": "dino@gmail.com",
    "nama3": "dito",
    "email3": "dito@gmail.com"
  },
  {
    "perusahaan": "Garuda",
    "nama1": "alif",
    "email1": "alif@gmail.com",
    "nama2": "dini",
    "email2": "dini@gmail.com",
    "nama3": "diti",
    "email3": "diti@gmail.com"
  },
  {
    "perusahaan": "Unilever",
    "nama1": "arif",
    "email1": "arif@gmail.com",
    "nama2": "dina",
    "email2": "dina@gmail.com",
    "nama3": "dita",
    "email3": "dita@gmail.com"
  },
  {
    "perusahaan": "Wisikita",
    "nama1": "ajif",
    "email1": "ajif@gmail.com",
    "nama2": "dana",
    "email2": "dana@gmail.com",
    "nama3": "data",
    "email3": "data@gmail.com"
  }
]

the end result is like this

This result will always be filled from column A:1 not continue the column automatically.

How do I enter the json result and the result will continue the column from before?

Follow the instruction in the answer/comment below and read this [answer](https://stackoverflow.com/questions/38882321/better-way-to-find-last-used-row). you are defining `lastCol`/`lastRow` wrongly. @Afif — Raymond Wu, Sep 29 '21 at 08:56
Follow the instruction in the answer below (the one submitted by Sam to your question). The answer in that link is for you to learn how to get last row/column. @Afif — Raymond Wu, Sep 29 '21 at 09:05

score 0 · Answer 1 · answered Sep 29 '21 at 08:49

0

You are using lastCol as if it was lastRow. If you rename the variable throughout your method and change the lastCol = ... row to this

lastRow = Cells(Rows.Count, "A").End(xlUp).Row

you will start filling at the first empty row.

answered Sep 29 '21 at 08:49

Sam

5,424
1
18
33

sorry sir, where should i add the code because I'm a beginner at this vba – Afif abiyyuna Sep 29 '21 at 08:53
Replace 1) `Dim lastCol As Long` with `Dim lastRow As Long` 2) `lastCol = Cells(Columns.Count, 1).End(xlDown).Column` with `lastRow = Cells(Rows.Count, "A").End(xlUp).Row` 3) `i = lastCol + 1` with `i = lastRow + 1` – Raymond Wu Sep 29 '21 at 08:55
thank you sir for the suggestion i will try the code – Afif abiyyuna Sep 29 '21 at 09:07

How to fill json parse result in next column in Excel?

1 Answers1