John V wrote:
MS, That code you posted will help me a lot. Your question below confused. I
plan to write an Excel macro that will open a text file, read it line by line
until I find the string(s) that I want, then clean up and copy the values
into Excel.

It may be more efficient to import the text file into an Excel sheet, then
find and clean up the desired data from within Excel. However, the formats
and delimiters for these 36 files are not consistent. Opinions welcome.

Some of the pages are pipe delimited, others are columnar with no
delimiters, all designed for pretty printed output, none designed for data
import and analysis.

Thanks for your help; hope I've explained the project better.

"MrScience" wrote:

What are you using to import the text file into Excel? The code above
obviously applies only if you sucessfully import the data first. I'm
wondering it your writing an import routine or if you've tried to
simply open the text file in Excel. The .txt file appears to be pipe
delimited. Is this the only delimiter?

Sorry for the confusion, John, when I asked what you were using to
import the text file into Excel I was wondering if you were using a VB6
program, or something other than VBA code (Macro) in Excel. I often
have to do this same type of work and I switch back and forth between
VB6 and VBA code written in the Excel workbook. I usually only use a
VB6 program if I need to constuct something that others are going to
use for a long time then it's worth spending the time to do it that
way.

Anyway, back to your situation at hand. I guess you're planning on
using different workbooks for each time of file. As I think about it,
though, I guess you could use a procedure to check to see which type(s)
of delimiters are present in the .txt file and then call subsequent
procedures accordingly.

If InStr(myVar,"|") then
'call procedure to handle pipe delimiters
End If

When I'm faced with the situation you have, I usually go ahead and open
the file in Excel just to get a good look at how Excel will parse it.
Lots of times, because of strange delimiters or something Excel doesn't
quite know what to do with, it will concatenate or parse incorrectly.
If this happens, I feel much more comfortable writing the VBA code
based on what I can see in each column.

You could also use the Split function if you want to write code to
handle the importing of the text file . . .

Sub ImportFile()

Dim myFile As String
Dim myString As String
Dim myStringPart() As String 'note this is an array
Dim fileLen as Long
Dim fileNum as Integer

myFile = "C:\myFoler\myFileName"

Open fileName For Input as filenum

fileLen = Len(myFile)
fileNum = FreeFile

Do While Not EOF(fileNum)
Line Input fileNum, myString 'get one string at a time

'now that we have one string, let's parse it

myStringPart = Split(myString, "|") 'substitute in any delimiter you
need

'code here to either place into another array to do further work or
copy
'to Excel

Loop

End Sub


John, I copied this from one of my VB6 programs so it may need some
revision to work in Excel.