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