I am trying to force a text file to be saved with Ansi encoding.
I have many text files that i am trying to import into a worksheet - one
file per cell (they are quite small), but as these text files have been
produced via an OLE, they are unicode files. When i put the text into the
cells, it still has extra characters that i don't want so i think the only
way i can do this is to resave the text files with ansi encoding.

I had tried StrConv(temp, vbfromUnicode) but there are still extra
characters there.

I had tried using
Dim xsystem, contents, temp, newtemp
Set xsystem = CreateObject("Scripting.FileSystemObject")
Set temp = xsystem.OpenTextFile(fn)
contents = temp.readall()
temp.Close
Set newtemp = xsystem.CreateTextFile(strFilePath & "\" & "gj-" &
strFilename, TristateUseDefault)
newtemp.WriteLine (contents)
newtemp.Close

But this still saves as unicode. Is there any way to force it to save as
ANSI?

I had found this code on the net, but it gives me chinese or something.
Dim InputFile As String, OutputFile As String, TextChar As String
InputFile = fn ' replace with actual path to input file
OutputFile = strFilePath & "\" & "gjj-" & strFilename ' replace with output
path
Open InputFile For Input As #1 ' Open file.
Open OutputFile For Output As #2 ' Open the output file: will either create
it or overwrite existing file
Do While Not EOF(1) ' Loop until end of file.
TextChar = Input(1, #1) ' Get one character.
' Perform any desired conversion here
Print #2, TextChar; ' Wirte to output file
Loop
Close #1 ' Close files
Close #2

Result: à´*倊牥桴à¨�à´*匊ç�¥ãˆ*〰ഷ䜊潲瑷¡¨¤¥€ ºà¨�㤱〮ശഊ

I know you can save as Ansi in notepad, but due to the number of files, i
would really like to be able to do this in excel. I can get the text file
contents into the cells, it's just getting the text to be "normal" text.

I found some information in a VB forum:
My.Computer.FileSystem.WriteAllText("c:\x.txt", ControlChars.NewLine &
"ã�‚ã�*ｓã�*ｓｄ", True, System.Text.Encoding.Default)

Third parameter "True" specifies appending and fourth parameter specifies
ANSI depending upon ur systems ANSI code page.

But have no idea if this is of any use for VBA.

Any tips or advice most welcome.
George

from
Set temp = xsystem.OpenTextFile(fn)

to
TristateFalse = 0
Set temp = xsystem.OpenTextFile(filename:=fn,format:=Tristate False)


TristateFalse
"George J" wrote:

I am trying to force a text file to be saved with Ansi encoding.
I have many text files that i am trying to import into a worksheet - one
file per cell (they are quite small), but as these text files have been
produced via an OLE, they are unicode files. When i put the text into the
cells, it still has extra characters that i don't want so i think the only
way i can do this is to resave the text files with ansi encoding.

I had tried StrConv(temp, vbfromUnicode) but there are still extra
characters there.

I had tried using
Dim xsystem, contents, temp, newtemp
Set xsystem = CreateObject("Scripting.FileSystemObject")
Set temp = xsystem.OpenTextFile(fn)
contents = temp.readall()
temp.Close
Set newtemp = xsystem.CreateTextFile(strFilePath & "\" & "gj-" &
strFilename, TristateUseDefault)
newtemp.WriteLine (contents)
newtemp.Close

But this still saves as unicode. Is there any way to force it to save as
ANSI?

I had found this code on the net, but it gives me chinese or something.
Dim InputFile As String, OutputFile As String, TextChar As String
InputFile = fn ' replace with actual path to input file
OutputFile = strFilePath & "\" & "gjj-" & strFilename ' replace with output
path
Open InputFile For Input As #1 ' Open file.
Open OutputFile For Output As #2 ' Open the output file: will either create
it or overwrite existing file
Do While Not EOF(1) ' Loop until end of file.
TextChar = Input(1, #1) ' Get one character.
' Perform any desired conversion here
Print #2, TextChar; ' Wirte to output file
Loop
Close #1 ' Close files
Close #2

Result: à´*倊牥桴à¨�à´*匊ç�¥ãˆ*〰ഷ䜊潲瑷¡¨¤¥€ ºà¨�㤱〮ശഊ

I know you can save as Ansi in notepad, but due to the number of files, i
would really like to be able to do this in excel. I can get the text file
contents into the cells, it's just getting the text to be "normal" text.

I found some information in a VB forum:
My.Computer.FileSystem.WriteAllText("c:\x.txt", ControlChars.NewLine &
"ã�‚ã�*ｓã�*ｓｄ", True, System.Text.Encoding.Default)

Third parameter "True" specifies appending and fourth parameter specifies
ANSI depending upon ur systems ANSI code page.

But have no idea if this is of any use for VBA.

Any tips or advice most welcome.
George

Thanks Joel, but the new workbook is still saved as unicode - not Ansi.

Is there something i need to do to
Set newtemp = xsystem.CreateTextFile(strFilePath & "\" & "gj-" &
strFilename, TristateUseDefault)

thanks
George

"joel" wrote:

from
Set temp = xsystem.OpenTextFile(fn)

to
TristateFalse = 0
Set temp = xsystem.OpenTextFile(filename:=fn,format:=Tristate False)


TristateFalse
"George J" wrote:

I am trying to force a text file to be saved with Ansi encoding.
I have many text files that i am trying to import into a worksheet - one
file per cell (they are quite small), but as these text files have been
produced via an OLE, they are unicode files. When i put the text into the
cells, it still has extra characters that i don't want so i think the only
way i can do this is to resave the text files with ansi encoding.

I had tried StrConv(temp, vbfromUnicode) but there are still extra
characters there.

I had tried using
Dim xsystem, contents, temp, newtemp
Set xsystem = CreateObject("Scripting.FileSystemObject")
Set temp = xsystem.OpenTextFile(fn)
contents = temp.readall()
temp.Close
Set newtemp = xsystem.CreateTextFile(strFilePath & "\" & "gj-" &
strFilename, TristateUseDefault)
newtemp.WriteLine (contents)
newtemp.Close

But this still saves as unicode. Is there any way to force it to save as
ANSI?

I had found this code on the net, but it gives me chinese or something.
Dim InputFile As String, OutputFile As String, TextChar As String
InputFile = fn ' replace with actual path to input file
OutputFile = strFilePath & "\" & "gjj-" & strFilename ' replace with output
path
Open InputFile For Input As #1 ' Open file.
Open OutputFile For Output As #2 ' Open the output file: will either create
it or overwrite existing file
Do While Not EOF(1) ' Loop until end of file.
TextChar = Input(1, #1) ' Get one character.
' Perform any desired conversion here
Print #2, TextChar; ' Wirte to output file
Loop
Close #1 ' Close files
Close #2

Result: à´*倊牥桴à¨�à´*匊ç�¥ãˆ*〰ഷ䜊潲瑷¡¨¤¥€ ºà¨�㤱〮ശഊ

I know you can save as Ansi in notepad, but due to the number of files, i
would really like to be able to do this in excel. I can get the text file
contents into the cells, it's just getting the text to be "normal" text.

I found some information in a VB forum:
My.Computer.FileSystem.WriteAllText("c:\x.txt", ControlChars.NewLine &
"ã�‚ã�*ｓã�*ｓｄ", True, System.Text.Encoding.Default)

Third parameter "True" specifies appending and fourth parameter specifies
ANSI depending upon ur systems ANSI code page.

But have no idea if this is of any use for VBA.

Any tips or advice most welcome.
George

Saving as text won't remove the control characters. Unicode is really 16
bit data and ascii is 8 bit data. Saving as text just changes the byte
structure of the data and doesn't eliminate the data


Unicode

1234
5678
9ABC


Ascii

12
34
56
78
9A
BC

"George J" wrote:

Thanks Joel, but the new workbook is still saved as unicode - not Ansi.

Is there something i need to do to
Set newtemp = xsystem.CreateTextFile(strFilePath & "\" & "gj-" &
strFilename, TristateUseDefault)

thanks
George

"joel" wrote:

from
Set temp = xsystem.OpenTextFile(fn)

to
TristateFalse = 0
Set temp = xsystem.OpenTextFile(filename:=fn,format:=Tristate False)


TristateFalse
"George J" wrote:

I am trying to force a text file to be saved with Ansi encoding.
I have many text files that i am trying to import into a worksheet - one
file per cell (they are quite small), but as these text files have been
produced via an OLE, they are unicode files. When i put the text into the
cells, it still has extra characters that i don't want so i think the only
way i can do this is to resave the text files with ansi encoding.

I had tried StrConv(temp, vbfromUnicode) but there are still extra
characters there.

I had tried using
Dim xsystem, contents, temp, newtemp
Set xsystem = CreateObject("Scripting.FileSystemObject")
Set temp = xsystem.OpenTextFile(fn)
contents = temp.readall()
temp.Close
Set newtemp = xsystem.CreateTextFile(strFilePath & "\" & "gj-" &
strFilename, TristateUseDefault)
newtemp.WriteLine (contents)
newtemp.Close

But this still saves as unicode. Is there any way to force it to save as
ANSI?

I had found this code on the net, but it gives me chinese or something.
Dim InputFile As String, OutputFile As String, TextChar As String
InputFile = fn ' replace with actual path to input file
OutputFile = strFilePath & "\" & "gjj-" & strFilename ' replace with output
path
Open InputFile For Input As #1 ' Open file.
Open OutputFile For Output As #2 ' Open the output file: will either create
it or overwrite existing file
Do While Not EOF(1) ' Loop until end of file.
TextChar = Input(1, #1) ' Get one character.
' Perform any desired conversion here
Print #2, TextChar; ' Wirte to output file
Loop
Close #1 ' Close files
Close #2

Result: à´*倊牥桴à¨�à´*匊ç�¥ãˆ*〰ഷ䜊潲瑷¡¨¤¥€ ºà¨�㤱〮ശഊ

I know you can save as Ansi in notepad, but due to the number of files, i
would really like to be able to do this in excel. I can get the text file
contents into the cells, it's just getting the text to be "normal" text.

I found some information in a VB forum:
My.Computer.FileSystem.WriteAllText("c:\x.txt", ControlChars.NewLine &
"ã�‚ã�*ｓã�*ｓｄ", True, System.Text.Encoding.Default)

Third parameter "True" specifies appending and fourth parameter specifies
ANSI depending upon ur systems ANSI code page.

But have no idea if this is of any use for VBA.

Any tips or advice most welcome.
George

Hmmm

When i look at the text from the file in the VB Editor, it looks like:
http://img412.imageshack.us/my.php?image=new1f.jpg
and displays in the cell as:
ÿþM

But in the text file it is displayed as "Mar 2003".
When i change the encoding manually for that file from unicode to ANSI, it
appears in both the VBE and in the cell as "Mar 2003".
This is the file:
http://www.filefactory.com/dlf/f/aga...0/n/text20_txt

I thought i might be able to save the files that are currently there only
with ANSI encoding. Would i need to try doing this using sendkeys and
notepad?


"joel" wrote:

Saving as text won't remove the control characters. Unicode is really 16
bit data and ascii is 8 bit data. Saving as text just changes the byte
structure of the data and doesn't eliminate the data


Unicode

1234
5678
9ABC


Ascii

12
34
56
78
9A
BC

I don't download files from these two sites because they create Ad-Ware on my
PC. Use something like Savefile.com instead.

There are some white characters ( control characters) that you don't see
that is causing the problems.

"George J" wrote:

Hmmm

When i look at the text from the file in the VB Editor, it looks like:
http://img412.imageshack.us/my.php?image=new1f.jpg
and displays in the cell as:
ÿþM

But in the text file it is displayed as "Mar 2003".
When i change the encoding manually for that file from unicode to ANSI, it
appears in both the VBE and in the cell as "Mar 2003".
This is the file:
http://www.filefactory.com/dlf/f/aga...0/n/text20_txt

I thought i might be able to save the files that are currently there only
with ANSI encoding. Would i need to try doing this using sendkeys and
notepad?


"joel" wrote:

Saving as text won't remove the control characters. Unicode is really 16
bit data and ascii is 8 bit data. Saving as text just changes the byte
structure of the data and doesn't eliminate the data


Unicode

1234
5678
9ABC


Ascii

12
34
56
78
9A
BC