Greetings. I am having the strangest problem with a text
file. The first character of every line is causing the
line to not read properly. It looks like the letter "y"
with two dots over it but it shows up as a square in Word
and Notepad.

I wrote a routine that reads in the text file line by
line, but it won't work if that weird character is in the
file. The line will read, but everything after that
character is lost. If I delete the character and try to
read the file line by line, it works fine.

How can I get around this so I can get to the data in the
file programatically?

~Jenny

Your first challenge will be to find out what that character really is:

Import it so you can see the funny y.
then if that character is the first character in A1, use a helper cell with:
=code(a1)

Make a note of that number.

You could also use Chip Pearson's addin:
http://www.cpearson.com/excel/CellView.htm

It's a very neat way to see what's in any position in any cell.

Then close that file and try running a macro like this:
Option Explicit
Sub UpDateTxtFile()

Dim FSO As Object
Dim RegEx As Object

Dim myFile As Object
Dim myContents As String
Dim myInFileName As String
Dim myOutFileName As String

myInFileName = "C:\my documents\excel\test.txt"
myOutFileName = Environ("temp") & "\testout.txt"

Set FSO = CreateObject("Scripting.FileSystemObject")

Set myFile = FSO.OpenTextFile(myInFileName, 1, False)
myContents = myFile.ReadAll
myFile.Close

Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
.Global = True
.IgnoreCase = False
.Pattern = Chr(159)
myContents = .Replace(myContents, "")
End With

Set myFile = FSO.CreateTextFile(myOutFileName)
myFile.Write myContents
myFile.Close

End Sub

It opens the text file, does a mass change and saves to a new text file in your
windows temp folder.

Adjust the input name and adjust this to match the character's code:
.Pattern = Chr(159)

Then point at the temp folder's testout.txt in your other macro.


Jenny wrote:

Greetings. I am having the strangest problem with a text
file. The first character of every line is causing the
line to not read properly. It looks like the letter "y"
with two dots over it but it shows up as a square in Word
and Notepad.

I wrote a routine that reads in the text file line by
line, but it won't work if that weird character is in the
file. The line will read, but everything after that
character is lost. If I delete the character and try to
read the file line by line, it works fine.

How can I get around this so I can get to the data in the
file programatically?

~Jenny

--

Dave Peterson