Data being trimmed / truncated ?

[jjmpsp]

Hi all,

I've managed to get an example working with this package but the source JSON data I'm using is being truncated when it is outputted to an Excel sheet. See the below images which shows the the precision of the latitude/longitude values being trimmed, as well as the '0' being dropped from the phone number:

I'm using the following code:

    ' Parse JSON sample
    JSON.Parse sJSONString, vJSON, sState
    If sState = "Error" Then MsgBox "Invalid JSON": End

    ' Convert JSON to 2D Array
    JSON.ToArray vJSON, aData, aHeader

    ' Output to worksheet #1
    Output aHeader, aData, ThisWorkbook.Sheets(2)
    MsgBox "Completed"

Sub Output(aHeader, aData, oDestWorksheet As Worksheet)
    With oDestWorksheet
        .Activate
        .Cells.Delete
        With .Cells(1, 1)
            .Resize(1, UBound(aHeader) - LBound(aHeader) + 1).Value = aHeader
            .Offset(1, 0).Resize( _
                    UBound(aData, 1) - LBound(aData, 1) + 1, _
                    UBound(aData, 2) - LBound(aData, 2) + 1 _
                ).Value = aData
        End With
        .Columns.AutoFit
    End With
End Sub

How can I debug this issue to work out what is happening?

Thank you

[omegastripes]

Hi @jjmpsp, Thats due to Excel autoformatting, you may format cells as text with .NumberFormat = "@" prior the data be inserted, try the code below:

Sub Output(aHeader, aData, oDestWorksheet As Worksheet)
    With oDestWorksheet
        .Activate
        .Cells.Delete
        With .Cells(1, 1)
            .Resize(1, UBound(aHeader) - LBound(aHeader) + 1).Value = aHeader
            With .Offset(1, 0).Resize( _
                    UBound(aData, 1) - LBound(aData, 1) + 1, _
                    UBound(aData, 2) - LBound(aData, 2) + 1 _
                )
                .NumberFormat = "@"
                .Value = aData
            End With
        End With
        .Columns.AutoFit
    End With
End Sub

[jjmpsp]

Hi @omegastripes thank you so much - this did the trick! Formatting with "@" (text) is exactly what I was looking for.

Thank you also for developing this wonderful library. My not-so-technical co-workers will be able to utilize the data from an API I've developed directly in Excel thanks to this! :+1: