Results 1 to 4 of 4

Thread: Text file SaveAs from Excel VBA

  1. #1
    Junior Member
    Join Date
    Jul 2013
    Posts
    4

    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.

  2. #2
    Junior Member
    Join Date
    Jul 2013
    Posts
    4
    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.

  3. #3
    Junior Member
    Join Date
    Jul 2013
    Posts
    4
    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

  4. #4
    Junior Member
    Join Date
    Jul 2013
    Posts
    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
  •