0

I have an Excel VBA Script that I originally wrote for Windows (where it works fine) and now had to port to Mac OS. I don't think that it matters but the script is calling cURL to get a JSON Response from a web API which is then parsed, edited and inserted into the spreadsheet.

Some of the fields in the parsed JSON contain special characters like Ä, Ü, Ö (German characters). The script can handle these just fine on Windows but on Mac instead of ÖÜÄ I get other symbols. This breaks the tool as it depends on some vlookup-functions where the values are written by hand (with the correct symbols).

I tried lots of googling but was not able to find anything.

One thing that might be interesting is that the code itself changes on Mac as well! I have some statements printed to the console and even the hardcoded strings that contain a special character are broken as soon as I open the script on a Mac.

ksbawpn
  • 302
  • 4
  • 19
  • A [mojibake](https://en.wikipedia.org/wiki/Mojibake) case. `'ÖÜÄ'.encode('cp1252').decode("mac_roman")` returns `'÷‹ƒ'`. See (an follow) [UTF-8 everywhere](https://utf8everywhere.org/). – JosefZ Jan 05 '21 at 14:43
  • Now I understand the reason behind the error - but do you have any idea how to fix this elegently? I believe I need to change the mac code or the mac/excel settings - but I do not know where to start. – ksbawpn Jan 05 '21 at 19:08

1 Answers1

1

The question is for Mac VBA. This is a pain. The only solution I have is to send the curl output to a file, then open that file with workbooks.opentext and Origin:=65001 and all the response is in cell A1, correctly encoded. I have asked my own question on that, to see if any one has a more recent answer. How to read UTF8 data output from cURL in popen/fread in VBA on Mac?

sysmod
  • 463
  • 3
  • 11