John V wrote:
I wish to import financial data from numerous text files into Excel
worksheets. The text files are fixed width, designed for printing one page
each. But the column formatting varies from file to file, and sometimes
varies within file (the top half will have 5 columns, the bottom half two). I
*think* the following is the general approach I need to use (and my questions
are contained in brackets):
1. Open text file and read start a loop to read in one line at a time.[I
think I know how to code this; pretty sure I want sequential input.]
2. Search for key words (e.g. Assets, Securities) that signify the desired
line [how do I say 'if line contains "Assets" then...'? The keyword may not
always be at the start of the line.]
3. When desired line is found, then use MID function to extract the desired
substring [I should be able to use specific position counts as long as the
file format doesn't change.] Convert that sub-string to a number [syntax??
for this step] [Will leading blanks in the sub-string cause a conversion
error? How would a string of blanks be converted, if at all?]
4. Place number in array and end loop. [How, if I need to, do I begin again
at line one of a particular file for a new keyword search?]
5. When all data is collected, write array to an excel column.

Final question: A total of about 240 files are involved. Is there a more
efficient method out there, or will this be fast enough (I'm prepared to wait
5 minutes, not 30, for this to chug along).

As you can tell from the questions, I'm a newbie. Any and all help
appreciated.

Hi John,

On question #1, I would use InStr(lineVariableName, "Securities") so it
would something like . . .
If InStr(myVar,"Securities") then
myVar2 = mid(stringName, x, y)
'with x and y representing the beginning and ending position you want
to 'capture
end if

On question #3, I agree that leaving any leading spaces in the string
would cause a conversion error. I would test for leading spaces and
then remove.

Some VBA code in Excel like this would work . . .

Sub checkForLeadingSpaces()
Dim myVar As Variant
Dim x As Integer
Dim myBool As Boolean
Dim SLen As Integer

Set myVar = Range("A1")
Do While Not IsEmpty(myVar)
Set nextVar = myVar.Offset(1, 0)

myBool = True
x = 1

Do Until myBool = False

If Mid(myVar, x, 1) = " " Then 'look for leading spaces
SLen = Len(myVar)
myVar = Right(myVar, SLen - 1)
Else

myBool = False
End If
Loop
MsgBox myVar

Set myVar = nextVar
Loop
End Sub

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?