I have a VBA program that opens a text file but needs to do a SaveAs and change the encoding to Unicode. I haven't been able to figure it out.
Printable View
I have a VBA program that opens a text file but needs to do a SaveAs and change the encoding to Unicode. I haven't been able to figure it out.
Just to clarify, when I say "open a text file" I'm not referring to importing into the spreadsheet. I want to open the file and change the encoding before the import step.
I tried this script but didn't change the file:
Sub TestTXTFileOpen()
Dim fileToOpen, system, file, contents, newFile
fileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt")
Shell ("C:\Windows\notepad.exe " & fileToOpen)
Const ForReading = 1
Const OpenAsUnicode = -1
Set system = CreateObject("Scripting.FileSystemObject")
Set file = system.OpenTextFile(fileToOpen, ForReading, False, OpenAsUnicode)
contents = file.ReadAll()
file.Close
Set newFile = system.CreateTextFile("fileToOpen", True)
End Sub
This changes the file to Unicode but only after it is closed but this code doesn't close the file so I think my only remaining issue is how to close the text file after it is converted.
Sub TestTXTFileOpen()
Dim fileToOpen As String, fso, stream, f, Text
fileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt") 'Text files only
Shell ("C:\Windows\notepad.exe " & fileToOpen)
Set stream = CreateObject("ADODB.Stream")
stream.Open
stream.Type = 2
stream.Charset = "utf-8"
stream.LoadFromFile fileToOpen
Text = stream.ReadText
stream.Close
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.OpenTextFile(fileToOpen, 2, True, True)
f.Write Text
f.Close
End Sub