-
July 25th, 2013, 14:01 PM
#1
Text file SaveAs from Excel VBA
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.
-
July 25th, 2013, 14:32 PM
#2
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.
-
July 25th, 2013, 18:27 PM
#3
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
-
July 25th, 2013, 20:29 PM
#4
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
Last edited by norm42; July 25th, 2013 at 20:57 PM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
Bookmarks