Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text File Problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text File Problem
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem encountered when saving worksheet as a text file | Excel Worksheet Functions | |||
problem with open text file in excel 2007 | Excel Discussion (Misc queries) | |||
problem in importing text file! | Excel Discussion (Misc queries) | |||
opening text file in excel problem | Excel Discussion (Misc queries) | |||
Get External Data, Import Text File, File name problem | Excel Programming |