Special Character codes from API Source

[Edsancar]

I'm accesing a JSON API frontend with VBA and VBA-JSON v2.3.1.

The issue I'm facing is that the API database is sending me character code for special characters thus I get
José
instead of José

Is there a way I can solve this?

Thanks for your help.

 

[Edsancar]

Assuming the data in Col.A and output the result in col.B.

Sub test()
    Dim a, i&, x&, s$, temp&
    With Range("a1", Range("a" & Rows.Count).End(xlUp))
        a = .Resize(, 2).Value
        For i = 1 To UBound(a, 1)
            s = a(i, 1)
            x = InStr(s, "&#")
            Do While x
                temp = Val(Mid$(s, x + 2))
                s = Application.Replace(s, x, 2 + Len(temp), WorksheetFunction.Unichar(temp))
                x = InStr(x + 1, s, "&#")
            Loop
            a(i, 2) = s
        Next
        .Resize(, 2) = a
    End With
End Sub

Thanks Fuji

This works pretty good, I still have to find how to put it into my worksheet, specially since I see many instructions I have never used before (".Resize") is one of them, but that can be done.

Hopefully I don't stumble on somethng else.

Eduardo

 

[Fuji]

You are welcome and thanks for the feedback.

That code uses array to speed up when you have thousands of rows.
If you prefer some kind of readable code then.

Sub test()
    Dim r As Range, x&, s$, temp&
    For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
        s = r
        x = InStr(s, "&#")
        Do While x
            temp = Val(Mid$(s, x + 2))
            s = Application.Replace(s, x, 2 + Len(temp), WorksheetFunction.Unichar(temp))
            x = InStr(x + 1, s, "&#")
        Loop
        r = s   '<--- update col.A
        'r(, 2) = s  '<--- output to adjacent cell
    Next
End Sub