Special Character codes from API Source

Edsancar

New Member
Joined
Aug 7, 2024
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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.
 
Assuming the data in Col.A and output the result in col.B.
Code:
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
 
Upvote 0
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.
Rich (BB code):
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
 
Upvote 0

Forum statistics

Threads
1,226,902
Messages
6,193,581
Members
453,808
Latest member
EBERHARDTJOEY

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top