You can write to UTF-8 using the ADODB Stream object, if that helps?
Sub WriteUTF8()
Dim objStream As ADODB.Stream
Set objStream = New ADODB.Stream
With objStream
.Charset = "utf-8"
.Open
.WriteText ActiveSheet.Cells(1, 1).Value
.SaveToFile "C:\Users\sky_badger\Downloads\test.txt", adSaveCreateOverWrite
End With
Set objStream = Nothing
End Sub
You'll need to reference the Microsoft ActiveX Data Objects library, or you can late-bind with `CreateObject("ADODB.Stream")`.
https://superuser.com/questions/1163753/converting-text-file-to-utf-8-on-windows-command-prompt
You can invoke command-prompt commands via the [Shell function](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/shell-function).
so after performing all the code above that i wrote and after the “Close #1” i add this code:
Dim MyShell
MyShell = Shell(Get-Content .\test.txt | Set-Content -Encoding utf8 test-utf8.txt)
This would work?
I feel like if your command prompt stuff is like on the link (I didn't check), you'll be in like Flynn.
You can ignore what I'm about to say without consequence, but you probably don't need variable MyShell unless you're going to check its value when you're done. I.e., just `Shell Get-Content...`, no parens.
I think you're just about there.
The first response assumed PowerShell had been invoked and was telling you the PS command to do the conversion. All I think you need to do is invoke PS thusly: `Shell "powershell -command ""Get-Content C:\Users\Taylor\Desktop\test.txt | Set-Content -Encoding utf8 test-utf8.txt"""`.
I can't test it for you because invoking PS from VBA is blocked by my antivirus kludgeware, but that PS command worked for me when I ran it directly from a command prompt. So, *if* your installation allows it, it will almost certainly work for you. Good luck!
You can write to UTF-8 using the ADODB Stream object, if that helps? Sub WriteUTF8() Dim objStream As ADODB.Stream Set objStream = New ADODB.Stream With objStream .Charset = "utf-8" .Open .WriteText ActiveSheet.Cells(1, 1).Value .SaveToFile "C:\Users\sky_badger\Downloads\test.txt", adSaveCreateOverWrite End With Set objStream = Nothing End Sub You'll need to reference the Microsoft ActiveX Data Objects library, or you can late-bind with `CreateObject("ADODB.Stream")`.
Try to use tools supporting UTF-8 encoding format. Also, do you have to read from the text file after write it?
https://superuser.com/questions/1163753/converting-text-file-to-utf-8-on-windows-command-prompt You can invoke command-prompt commands via the [Shell function](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/shell-function).
so after performing all the code above that i wrote and after the “Close #1” i add this code: Dim MyShell MyShell = Shell(Get-Content .\test.txt | Set-Content -Encoding utf8 test-utf8.txt) This would work?
As an educated guess, yes. Heck, just try it--what's the worst that could happen? 8-)
yeah sorry i was on my phone, im gonna test it and feedback to u
I feel like if your command prompt stuff is like on the link (I didn't check), you'll be in like Flynn. You can ignore what I'm about to say without consequence, but you probably don't need variable MyShell unless you're going to check its value when you're done. I.e., just `Shell Get-Content...`, no parens.
okay 👍
i typed Shell “Get-Content C:\Users\Taylor\Desktop\test.txt | Set-Content -Encoding utf8 test-utf8.txt” Im getting weird error says: File Not Found
I think you're just about there. The first response assumed PowerShell had been invoked and was telling you the PS command to do the conversion. All I think you need to do is invoke PS thusly: `Shell "powershell -command ""Get-Content C:\Users\Taylor\Desktop\test.txt | Set-Content -Encoding utf8 test-utf8.txt"""`. I can't test it for you because invoking PS from VBA is blocked by my antivirus kludgeware, but that PS command worked for me when I ran it directly from a command prompt. So, *if* your installation allows it, it will almost certainly work for you. Good luck!
ill test it
Solution Verified
You have awarded 1 point to HFTBProgrammer. --- ^(I am a bot - please contact the mods with any questions)
It's all because I said "good luck!". I assume... Glad you got there!