I have a flow chart in txt format. I can import the file into excel,
I select a semicolon delimeter as there are none in the file, and I
don't want excel messing up the spacing. I can export the file with
all the correct spacing, but I am not allowed to save formatting
(changing color, making text bold). So I since each of our jobs in
the file starts with MP, I want to replace those letters with ** if
the job has completed. A list of completed jobs will be on a seperate
worksheet. Each job name is not in its own cell, so I'll have to edit
the string. Any info on how to do this would be very helpfull. Also,
if there is a way to directly edit the file, or other suggestions,
that would be great as well.

I figured it out, and I'm posting my code in case anybody else might
find it handy.

Sub Get_Text()

Dim strSourceFile As String, strOutFile As String, strInText As
String, strOutText As String
Dim strFront As String, strBack As String, strSearch As String
Dim lngInputFile As Long, lngOutputFile As Long, lngStringCalc As
Long
Dim intLength As Integer, intFoundAt As Integer
Dim intFoundText As Integer, intNumRows As Integer

strSourceFile = Application.GetOpenFilename("Text File
(*.txt),*.txt")
lngInputFile = FreeFile
Open strSourceFile For Input As lngInputFile

strOutFile = Left(strSourceFile, 12) & "out"
lngOutputFile = FreeFile

Open strOutFile For Output As lngOutputFile

intNumRows = Worksheets("Sheet1").UsedRange.Rows.Count

While Not EOF(lngInputFile)
Line Input #lngInputFile, strInText
Sheets("Sheet1").Range(Cells(1, "A"), Cells(intNumRows,
"A")).Select
For Each cell In Selection
strSearch = cell
intLength = Len(strInText)
intFoundAt = InStr(1, strInText, strSearch, vbTextCompare)
If intFoundAt 0 Then
lngStringCalc = intFoundAt - 1
strFront = Left(strInText, lngStringCalc)
lngStringCalc = intLength - intFoundAt - 7
strBack = Right(strInText, lngStringCalc)
strOutText = strFront & "**" & Right(strSearch, 6) &
strBack
strInText = strOutText
intFoundText = 1
End If
Next cell
If intFoundText 0 Then
Print #lngOutputFile, strOutText
intFoundText = 0
Else
Print #lngOutputFile, strInText
End If
Wend

End Sub



(Slick Willie) wrote in message . com...
I have a flow chart in txt format. I can import the file into excel,
I select a semicolon delimeter as there are none in the file, and I
don't want excel messing up the spacing. I can export the file with
all the correct spacing, but I am not allowed to save formatting
(changing color, making text bold). So I since each of our jobs in
the file starts with MP, I want to replace those letters with ** if
the job has completed. A list of completed jobs will be on a seperate
worksheet. Each job name is not in its own cell, so I'll have to edit
the string. Any info on how to do this would be very helpfull. Also,
if there is a way to directly edit the file, or other suggestions,
that would be great as well.