Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Encoding query - Ansi
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 & "あ*s*sd", 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Encoding query - Ansi
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 & "あ*s*sd", 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Encoding query - Ansi
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 & "あ*s*sd", 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Encoding query - Ansi
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 & "あ*s*sd", 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Encoding query - Ansi
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Encoding query - Ansi
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Encoding query - Ansi
It appears to be a ? that when replaced removes all the text from the cell.
I uploaded the small file i was using as an example, one of the larger files and an image of what is being seen in the VBEwhen trying to get the text into a cell. http://www.savefile.com/projects/808757320 Thanks for lookign at this. "joel" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Encoding query - Ansi
I had a similar problem a couple of weeks ago. Modify my old code below.
the 1st macro will put on the worksheet what the data actually looks like. column A is the character and column B the Ascii code. the second program will extract the asci data from the unicode only keepin the odd characters. Sub test() Const ForReading = 1, ForWriting = -2, _ ForAppending = 3 Dim ReadData TABChar = 9 ReadFile = Application _ .GetOpenFilename(FileFilter:="Text Files (*.txt), *.txt", _ Title:="Select Read File") If ReadFile = False Then MsgBox ("No file Selected - Exiting Macro") End If Open ReadFile For Binary As #1 'Set fs = CreateObject("Scripting.FileSystemObject") 'Set fin = fs.OpenTextFile(ReadFile, _ ' ForReading, TristateTrue) ActiveSheet.Cells.ClearContents RowCount = 1 Do While Not EOF(1) ReadData = Input(1, #1) ' ReadData = fin.Read(1) If Not EOF(1) Then Range("A" & RowCount) = ReadData Range("B" & RowCount) = Asc(ReadData) RowCount = RowCount + 1 End If Loop Exit Sub fin.Close End Sub Sub GetText() Const ForReading = 1, ForWriting = -2, _ ForAppending = 3 LFChar = 10 ReadFile = Application _ .GetOpenFilename(FileFilter:="Text Files (*.txt), *.txt", _ Title:="Select Read File") If ReadFile = False Then MsgBox ("No file Selected - Exiting Macro") End If Set fs = CreateObject("Scripting.FileSystemObject") Set fin = fs.OpenTextFile(ReadFile, _ ForReading, TristateTrue) ActiveSheet.Cells.ClearContents 'Only Read Odd bytes, data i s unicode Odd = True ReadLine = "" RowCount = 1 FirstRow = True Do While fin.AtEndOfStream < True ReadData = fin.Read(1) If Odd = True Then a = Asc(ReadData) Select Case Asc(ReadData) 'ignore UniCode Case Is = 128 'skip Case Else ReadLine = ReadLine & ReadData If Asc(ReadData) = LFChar Then Range("A" & RowCount) = ReadLine ReadLine = "" RowCount = RowCount + 1 End If End Select Odd = False Else Odd = True End If Loop fin.Close End Sub "George J" wrote: It appears to be a ? that when replaced removes all the text from the cell. I uploaded the small file i was using as an example, one of the larger files and an image of what is being seen in the VBEwhen trying to get the text into a cell. http://www.savefile.com/projects/808757320 Thanks for lookign at this. "joel" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Encoding query - Ansi
Many thanks Joel.
I really appreciate you taking the time to look at this. I'll try the code tomorrow when i'm back in work. Got my 4yo neice sleeping over so don't have access to my home computer for a while. Right now i'm just glad it wasn't me missing something obvious in getting the text from the file. From looking at those macros, i can safely say i would not have even got close. "joel" wrote: I had a similar problem a couple of weeks ago. Modify my old code below. the 1st macro will put on the worksheet what the data actually looks like. column A is the character and column B the Ascii code. the second program will extract the asci data from the unicode only keepin the odd characters. Sub test() Const ForReading = 1, ForWriting = -2, _ ForAppending = 3 Dim ReadData TABChar = 9 ReadFile = Application _ .GetOpenFilename(FileFilter:="Text Files (*.txt), *.txt", _ Title:="Select Read File") If ReadFile = False Then MsgBox ("No file Selected - Exiting Macro") End If Open ReadFile For Binary As #1 'Set fs = CreateObject("Scripting.FileSystemObject") 'Set fin = fs.OpenTextFile(ReadFile, _ ' ForReading, TristateTrue) ActiveSheet.Cells.ClearContents RowCount = 1 Do While Not EOF(1) ReadData = Input(1, #1) ' ReadData = fin.Read(1) If Not EOF(1) Then Range("A" & RowCount) = ReadData Range("B" & RowCount) = Asc(ReadData) RowCount = RowCount + 1 End If Loop Exit Sub fin.Close End Sub Sub GetText() Const ForReading = 1, ForWriting = -2, _ ForAppending = 3 LFChar = 10 ReadFile = Application _ .GetOpenFilename(FileFilter:="Text Files (*.txt), *.txt", _ Title:="Select Read File") If ReadFile = False Then MsgBox ("No file Selected - Exiting Macro") End If Set fs = CreateObject("Scripting.FileSystemObject") Set fin = fs.OpenTextFile(ReadFile, _ ForReading, TristateTrue) ActiveSheet.Cells.ClearContents 'Only Read Odd bytes, data i s unicode Odd = True ReadLine = "" RowCount = 1 FirstRow = True Do While fin.AtEndOfStream < True ReadData = fin.Read(1) If Odd = True Then a = Asc(ReadData) Select Case Asc(ReadData) 'ignore UniCode Case Is = 128 'skip Case Else ReadLine = ReadLine & ReadData If Asc(ReadData) = LFChar Then Range("A" & RowCount) = ReadLine ReadLine = "" RowCount = RowCount + 1 End If End Select Odd = False Else Odd = True End If Loop fin.Close End Sub "George J" wrote: It appears to be a ? that when replaced removes all the text from the cell. I uploaded the small file i was using as an example, one of the larger files and an image of what is being seen in the VBEwhen trying to get the text into a cell. http://www.savefile.com/projects/808757320 Thanks for lookign at this. "joel" wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Encoding query - Ansi
If youimport the data using the menu you get the same results as my code
Data - Import External Data - Import Data Choose text file and browse for the folder. "George J" wrote: Many thanks Joel. I really appreciate you taking the time to look at this. I'll try the code tomorrow when i'm back in work. Got my 4yo neice sleeping over so don't have access to my home computer for a while. Right now i'm just glad it wasn't me missing something obvious in getting the text from the file. From looking at those macros, i can safely say i would not have even got close. "joel" wrote: I had a similar problem a couple of weeks ago. Modify my old code below. the 1st macro will put on the worksheet what the data actually looks like. column A is the character and column B the Ascii code. the second program will extract the asci data from the unicode only keepin the odd characters. Sub test() Const ForReading = 1, ForWriting = -2, _ ForAppending = 3 Dim ReadData TABChar = 9 ReadFile = Application _ .GetOpenFilename(FileFilter:="Text Files (*.txt), *.txt", _ Title:="Select Read File") If ReadFile = False Then MsgBox ("No file Selected - Exiting Macro") End If Open ReadFile For Binary As #1 'Set fs = CreateObject("Scripting.FileSystemObject") 'Set fin = fs.OpenTextFile(ReadFile, _ ' ForReading, TristateTrue) ActiveSheet.Cells.ClearContents RowCount = 1 Do While Not EOF(1) ReadData = Input(1, #1) ' ReadData = fin.Read(1) If Not EOF(1) Then Range("A" & RowCount) = ReadData Range("B" & RowCount) = Asc(ReadData) RowCount = RowCount + 1 End If Loop Exit Sub fin.Close End Sub Sub GetText() Const ForReading = 1, ForWriting = -2, _ ForAppending = 3 LFChar = 10 ReadFile = Application _ .GetOpenFilename(FileFilter:="Text Files (*.txt), *.txt", _ Title:="Select Read File") If ReadFile = False Then MsgBox ("No file Selected - Exiting Macro") End If Set fs = CreateObject("Scripting.FileSystemObject") Set fin = fs.OpenTextFile(ReadFile, _ ForReading, TristateTrue) ActiveSheet.Cells.ClearContents 'Only Read Odd bytes, data i s unicode Odd = True ReadLine = "" RowCount = 1 FirstRow = True Do While fin.AtEndOfStream < True ReadData = fin.Read(1) If Odd = True Then a = Asc(ReadData) Select Case Asc(ReadData) 'ignore UniCode Case Is = 128 'skip Case Else ReadLine = ReadLine & ReadData If Asc(ReadData) = LFChar Then Range("A" & RowCount) = ReadLine ReadLine = "" RowCount = RowCount + 1 End If End Select Odd = False Else Odd = True End If Loop fin.Close End Sub "George J" wrote: It appears to be a ? that when replaced removes all the text from the cell. I uploaded the small file i was using as an example, one of the larger files and an image of what is being seen in the VBEwhen trying to get the text into a cell. http://www.savefile.com/projects/808757320 Thanks for lookign at this. "joel" wrote: 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Encoding query - Ansi
Thanks Joel
Had a little play around with your code (it told me the file was already open, but was quite easy to bypass this). Both of those macros are fantastic. As i have hundreds of text files that will be going into the workbook i was really hoping this could be automated and thanks to you i will now be able to do this. It's good to know that the unicode text files won't need to be converted too. The 2nd macro putting each line of text in its own cell will make this much easier for me to manipulate. All i need to do is remove the char(10+13) and i'll be able to manouver the data as required. I can't tell you how grateful i am for your patience and efforts in helping. This is going to be a huge help to use and our client. Many thanks again George "joel" wrote: If youimport the data using the menu you get the same results as my code Data - Import External Data - Import Data Choose text file and browse for the folder. "George J" wrote: Many thanks Joel. I really appreciate you taking the time to look at this. I'll try the code tomorrow when i'm back in work. Got my 4yo neice sleeping over so don't have access to my home computer for a while. Right now i'm just glad it wasn't me missing something obvious in getting the text from the file. From looking at those macros, i can safely say i would not have even got close. "joel" wrote: I had a similar problem a couple of weeks ago. Modify my old code below. the 1st macro will put on the worksheet what the data actually looks like. column A is the character and column B the Ascii code. the second program will extract the asci data from the unicode only keepin the odd characters. Sub test() Const ForReading = 1, ForWriting = -2, _ ForAppending = 3 Dim ReadData TABChar = 9 ReadFile = Application _ .GetOpenFilename(FileFilter:="Text Files (*.txt), *.txt", _ Title:="Select Read File") If ReadFile = False Then MsgBox ("No file Selected - Exiting Macro") End If Open ReadFile For Binary As #1 'Set fs = CreateObject("Scripting.FileSystemObject") 'Set fin = fs.OpenTextFile(ReadFile, _ ' ForReading, TristateTrue) ActiveSheet.Cells.ClearContents RowCount = 1 Do While Not EOF(1) ReadData = Input(1, #1) ' ReadData = fin.Read(1) If Not EOF(1) Then Range("A" & RowCount) = ReadData Range("B" & RowCount) = Asc(ReadData) RowCount = RowCount + 1 End If Loop Exit Sub fin.Close End Sub Sub GetText() Const ForReading = 1, ForWriting = -2, _ ForAppending = 3 LFChar = 10 ReadFile = Application _ .GetOpenFilename(FileFilter:="Text Files (*.txt), *.txt", _ Title:="Select Read File") If ReadFile = False Then MsgBox ("No file Selected - Exiting Macro") End If Set fs = CreateObject("Scripting.FileSystemObject") Set fin = fs.OpenTextFile(ReadFile, _ ForReading, TristateTrue) ActiveSheet.Cells.ClearContents 'Only Read Odd bytes, data i s unicode Odd = True ReadLine = "" RowCount = 1 FirstRow = True Do While fin.AtEndOfStream < True ReadData = fin.Read(1) If Odd = True Then a = Asc(ReadData) Select Case Asc(ReadData) 'ignore UniCode Case Is = 128 'skip Case Else ReadLine = ReadLine & ReadData If Asc(ReadData) = LFChar Then Range("A" & RowCount) = ReadLine ReadLine = "" RowCount = RowCount + 1 End If End Select Odd = False Else Odd = True End If Loop fin.Close End Sub "George J" wrote: It appears to be a ? that when replaced removes all the text from the cell. I uploaded the small file i was using as an example, one of the larger files and an image of what is being seen in the VBEwhen trying to get the text into a cell. http://www.savefile.com/projects/808757320 Thanks for lookign at this. "joel" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DIR fn when filename has non-ANSI characters | Excel Programming | |||
convert utf-8 to ansi csv? | Excel Programming | |||
Unicode to ANSI | Excel Discussion (Misc queries) | |||
ANSI codes | Excel Programming |