In Excel, I recorded a macro while importing a text file (because that's the
easiest way I've found to capture the fixed-width values for my fields). It
imported just fine.

Now I'm using VBA to import that same text file a line at a time and parse
each line for data values. I was having trouble (after the first line, the
code would quit). I used a msgbox to display that first line prior to
parsing- and it showed me the whole file! (or as much as could fit in a
msgbox, anyway).

So somehow, on importing the text file via the menu (file/open), Excel knew
that there were multiple lines and put the data in multiple rows. However,
when I use:

Do While Not EOF(1)
Line Input #1, LineofText
MsgBox LineofText
Loop

It opens the entire file as one line.

Clearly I'm missing something here, but I'm not sure what. Any suggestions?

Thank you,
Keith

In the VB world, the end of line marker is a Carriage Return followed by a
Line Feed... if your lines are not delimited with this, Line Input does not
see your individual lines of text as individual lines. I'm guessing your
lines are delimited by simple Line Feeds. If this is the case, and if this
code will only be used for files generated in the same way as the one you
are currently working with, you could do away with the Do-Loop, read the
entire file into a variable and then Split the text into individual lines
using the Split function with vbLf, or Chr(10) if you prefer, as the
delimiter... this will produce a zero-based array that you can loop through
element by element (which will then be line-by-line) and do whatever you
need to. For example...

Dim X As Long
Dim TotalFile As String
Dim Paragraphs() As String
Line Input #1, TotalFile
Paragraphs = Split(TotalFile, vbLf)
For X = 0 To UBound(Paragraphs)
' Do whatever you need to on each line in your file
Debug.Print Paragraphs(X)
Next

Remember, this code will only work for Line Feed delimited text... if your
code could have to handle both Line Feed delimited text and text delimited
with Windows' normal line delimiter (a Carriage Return followed by a Line
Feed), then write back and I'll generalize the code for you.

Rick


"ker_01" wrote in message
...

In Excel, I recorded a macro while importing a text file (because that's
the easiest way I've found to capture the fixed-width values for my
fields). It imported just fine.

Now I'm using VBA to import that same text file a line at a time and parse
each line for data values. I was having trouble (after the first line, the
code would quit). I used a msgbox to display that first line prior to
parsing- and it showed me the whole file! (or as much as could fit in a
msgbox, anyway).

So somehow, on importing the text file via the menu (file/open), Excel
knew that there were multiple lines and put the data in multiple rows.
However, when I use:

Do While Not EOF(1)
Line Input #1, LineofText
MsgBox LineofText
Loop

It opens the entire file as one line.

Clearly I'm missing something here, but I'm not sure what. Any
suggestions?

Thank you,
Keith

Rick-

Thank you for your response, explanation, and sample code. For this project
I will import several different files, but their sources and formats are so
different I'm making a separate sub for each. I'll adapt the snippet you've
provided, and post back if I have any problems or questions.

Thank you!!
Keith

"Rick Rothstein (MVP - VB)" wrote in
message ...
In the VB world, the end of line marker is a Carriage Return followed by a
Line Feed... if your lines are not delimited with this, Line Input does
not see your individual lines of text as individual lines. I'm guessing
your lines are delimited by simple Line Feeds. If this is the case, and if
this code will only be used for files generated in the same way as the one
you are currently working with, you could do away with the Do-Loop, read
the entire file into a variable and then Split the text into individual
lines using the Split function with vbLf, or Chr(10) if you prefer, as the
delimiter... this will produce a zero-based array that you can loop
through element by element (which will then be line-by-line) and do
whatever you need to. For example...

Dim X As Long
Dim TotalFile As String
Dim Paragraphs() As String
Line Input #1, TotalFile
Paragraphs = Split(TotalFile, vbLf)
For X = 0 To UBound(Paragraphs)
' Do whatever you need to on each line in your file
Debug.Print Paragraphs(X)
Next

Remember, this code will only work for Line Feed delimited text... if your
code could have to handle both Line Feed delimited text and text delimited
with Windows' normal line delimiter (a Carriage Return followed by a Line
Feed), then write back and I'll generalize the code for you.

Rick


"ker_01" wrote in message
...

In Excel, I recorded a macro while importing a text file (because that's
the easiest way I've found to capture the fixed-width values for my
fields). It imported just fine.

Now I'm using VBA to import that same text file a line at a time and
parse each line for data values. I was having trouble (after the first
line, the code would quit). I used a msgbox to display that first line
prior to parsing- and it showed me the whole file! (or as much as could
fit in a msgbox, anyway).

So somehow, on importing the text file via the menu (file/open), Excel
knew that there were multiple lines and put the data in multiple rows.
However, when I use:

Do While Not EOF(1)
Line Input #1, LineofText
MsgBox LineofText
Loop

It opens the entire file as one line.

Clearly I'm missing something here, but I'm not sure what. Any
suggestions?

Thank you,
Keith

Is it possible to open the file (using the equivalent of File|Open), then parse
the data from the worksheet?

If not, maybe you can open the file, convert it, save it as a new name, then
have your code open the converted file:

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 = vbLf
myContents = .Replace(myContents, vbCrLf)
End With

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

End Sub

Then point at the temp folder's testout.txt.

You'll want to be nice, so clean up that file in the %temp% folder when you're
done with it.



ker_01 wrote:

In Excel, I recorded a macro while importing a text file (because that's the
easiest way I've found to capture the fixed-width values for my fields). It
imported just fine.

Now I'm using VBA to import that same text file a line at a time and parse
each line for data values. I was having trouble (after the first line, the
code would quit). I used a msgbox to display that first line prior to
parsing- and it showed me the whole file! (or as much as could fit in a
msgbox, anyway).

So somehow, on importing the text file via the menu (file/open), Excel knew
that there were multiple lines and put the data in multiple rows. However,
when I use:

Do While Not EOF(1)
Line Input #1, LineofText
MsgBox LineofText
Loop

It opens the entire file as one line.

Clearly I'm missing something here, but I'm not sure what. Any suggestions?

Thank you,
Keith

--

Dave Peterson

In case you come back to this thread, here is a general function (with
comments) that will handle any normally delimited text file (Line Feed as
from a UNIX or Linux system, Carriage Return as from a Mac, or Carriage Feed
followed by a Line Feed which is an normal Windows type file). You pass it
the full path and filename for your file and it returns an array containing
each individual line.

Function SplitFileIntoLines(PathFileName As String) As String()
Dim X As Long
Dim FileNum As Long
Dim TotalFile As String
Dim Lines() As String
' Better to let VB select the file channel number than to hard code it.
FileNum = FreeFile
' Load entire file into the TotalFile variable in "one fell swoop".
Open PathFileName For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
' Replace CarriageReturns with Line Feeds... this will help
' handle normal Windows files AND files generated by a Mac.
TotalFile = Replace(TotalFile, vbCr, vbLf)
' Now, if it was a normar Windows file, there will be two
' Line Feeds following each other, so we reduce them to just one.
TotalFile = Replace(TotalFile, vbLf & vbLf, vbLf)
' Now that the entire file is now contained in the TotalFile
' variable as a Line Feed delimited file, let's return those
' individual lines in an array for use by the calling code.
SplitFileIntoLines = Split(TotalFile, vbLf)
End Function

To see how to use it, consider this sample macros...

Sub Test()
Dim X As Long
Dim FileLines() As String
FileLines = SplitFileIntoLines("c:\temp\Book1.txt")
For X = 0 To UBound(FileLines)
Debug.Print FileLines(X)
Next
End Sub

Rick


"ker_01" wrote in message
...
Rick-

Thank you for your response, explanation, and sample code. For this
project I will import several different files, but their sources and
formats are so different I'm making a separate sub for each. I'll adapt
the snippet you've provided, and post back if I have any problems or
questions.

Thank you!!
Keith

"Rick Rothstein (MVP - VB)" wrote in
message ...
In the VB world, the end of line marker is a Carriage Return followed by
a Line Feed... if your lines are not delimited with this, Line Input does
not see your individual lines of text as individual lines. I'm guessing
your lines are delimited by simple Line Feeds. If this is the case, and
if this code will only be used for files generated in the same way as the
one you are currently working with, you could do away with the Do-Loop,
read the entire file into a variable and then Split the text into
individual lines using the Split function with vbLf, or Chr(10) if you
prefer, as the delimiter... this will produce a zero-based array that you
can loop through element by element (which will then be line-by-line) and
do whatever you need to. For example...

Dim X As Long
Dim TotalFile As String
Dim Paragraphs() As String
Line Input #1, TotalFile
Paragraphs = Split(TotalFile, vbLf)
For X = 0 To UBound(Paragraphs)
' Do whatever you need to on each line in your file
Debug.Print Paragraphs(X)
Next

Remember, this code will only work for Line Feed delimited text... if
your code could have to handle both Line Feed delimited text and text
delimited with Windows' normal line delimiter (a Carriage Return followed
by a Line Feed), then write back and I'll generalize the code for you.

Rick


"ker_01" wrote in message
...

In Excel, I recorded a macro while importing a text file (because that's
the easiest way I've found to capture the fixed-width values for my
fields). It imported just fine.

Now I'm using VBA to import that same text file a line at a time and
parse each line for data values. I was having trouble (after the first
line, the code would quit). I used a msgbox to display that first line
prior to parsing- and it showed me the whole file! (or as much as could
fit in a msgbox, anyway).

So somehow, on importing the text file via the menu (file/open), Excel
knew that there were multiple lines and put the data in multiple rows.
However, when I use:

Do While Not EOF(1)
Line Input #1, LineofText
MsgBox LineofText
Loop

It opens the entire file as one line.

Clearly I'm missing something here, but I'm not sure what. Any
suggestions?

Thank you,
Keith

Rick-
This looks fabulous- Just to help me learn, I understand everything except
the use of the 'space' keyword:

TotalFile = Space(LOF(FileNum))

It appears that this is getting the total file length (length of string to
load in the next line), but I'm not clear what the 'space' keyword is doing
for the statement. Can you educate me with a brief explanation?

Thank you!
Keith

"Rick Rothstein (MVP - VB)" wrote in
message ...
In case you come back to this thread, here is a general function (with
comments) that will handle any normally delimited text file (Line Feed as
from a UNIX or Linux system, Carriage Return as from a Mac, or Carriage
Feed followed by a Line Feed which is an normal Windows type file). You
pass it the full path and filename for your file and it returns an array
containing each individual line.

Function SplitFileIntoLines(PathFileName As String) As String()
Dim X As Long
Dim FileNum As Long
Dim TotalFile As String
Dim Lines() As String
' Better to let VB select the file channel number than to hard code it.
FileNum = FreeFile
' Load entire file into the TotalFile variable in "one fell swoop".
Open PathFileName For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
' Replace CarriageReturns with Line Feeds... this will help
' handle normal Windows files AND files generated by a Mac.
TotalFile = Replace(TotalFile, vbCr, vbLf)
' Now, if it was a normar Windows file, there will be two
' Line Feeds following each other, so we reduce them to just one.
TotalFile = Replace(TotalFile, vbLf & vbLf, vbLf)
' Now that the entire file is now contained in the TotalFile
' variable as a Line Feed delimited file, let's return those
' individual lines in an array for use by the calling code.
SplitFileIntoLines = Split(TotalFile, vbLf)
End Function

To see how to use it, consider this sample macros...

Sub Test()
Dim X As Long
Dim FileLines() As String
FileLines = SplitFileIntoLines("c:\temp\Book1.txt")
For X = 0 To UBound(FileLines)
Debug.Print FileLines(X)
Next
End Sub

Rick


"ker_01" wrote in message
...
Rick-

Thank you for your response, explanation, and sample code. For this
project I will import several different files, but their sources and
formats are so different I'm making a separate sub for each. I'll adapt
the snippet you've provided, and post back if I have any problems or
questions.

Thank you!!
Keith

"Rick Rothstein (MVP - VB)" wrote
in message ...
In the VB world, the end of line marker is a Carriage Return followed by
a Line Feed... if your lines are not delimited with this, Line Input
does not see your individual lines of text as individual lines. I'm
guessing your lines are delimited by simple Line Feeds. If this is the
case, and if this code will only be used for files generated in the same
way as the one you are currently working with, you could do away with
the Do-Loop, read the entire file into a variable and then Split the
text into individual lines using the Split function with vbLf, or
Chr(10) if you prefer, as the delimiter... this will produce a
zero-based array that you can loop through element by element (which
will then be line-by-line) and do whatever you need to. For example...

Dim X As Long
Dim TotalFile As String
Dim Paragraphs() As String
Line Input #1, TotalFile
Paragraphs = Split(TotalFile, vbLf)
For X = 0 To UBound(Paragraphs)
' Do whatever you need to on each line in your file
Debug.Print Paragraphs(X)
Next

Remember, this code will only work for Line Feed delimited text... if
your code could have to handle both Line Feed delimited text and text
delimited with Windows' normal line delimiter (a Carriage Return
followed by a Line Feed), then write back and I'll generalize the code
for you.

Rick


"ker_01" wrote in message
...

In Excel, I recorded a macro while importing a text file (because
that's the easiest way I've found to capture the fixed-width values for
my fields). It imported just fine.

Now I'm using VBA to import that same text file a line at a time and
parse each line for data values. I was having trouble (after the first
line, the code would quit). I used a msgbox to display that first line
prior to parsing- and it showed me the whole file! (or as much as could
fit in a msgbox, anyway).

So somehow, on importing the text file via the menu (file/open), Excel
knew that there were multiple lines and put the data in multiple rows.
However, when I use:

Do While Not EOF(1)
Line Input #1, LineofText
MsgBox LineofText
Loop

It opens the entire file as one line.

Clearly I'm missing something here, but I'm not sure what. Any
suggestions?

Thank you,
Keith

Whenever you have a question about a keyword in VB, the first thing you
should do is click anywhere on the word and press F1 to get VB's help file
for that keyword. The Space function returns the number of spaces (character
with the ASCII value of 32) called for by the value you pass into it (in
this case, you are right, the length of the file).

Rick


"ker_01" wrote in message
...
Rick-
This looks fabulous- Just to help me learn, I understand everything except
the use of the 'space' keyword:

TotalFile = Space(LOF(FileNum))

It appears that this is getting the total file length (length of string to
load in the next line), but I'm not clear what the 'space' keyword is
doing for the statement. Can you educate me with a brief explanation?

Thank you!
Keith

"Rick Rothstein (MVP - VB)" wrote in
message ...
In case you come back to this thread, here is a general function (with
comments) that will handle any normally delimited text file (Line Feed as
from a UNIX or Linux system, Carriage Return as from a Mac, or Carriage
Feed followed by a Line Feed which is an normal Windows type file). You
pass it the full path and filename for your file and it returns an array
containing each individual line.

Function SplitFileIntoLines(PathFileName As String) As String()
Dim X As Long
Dim FileNum As Long
Dim TotalFile As String
Dim Lines() As String
' Better to let VB select the file channel number than to hard code it.
FileNum = FreeFile
' Load entire file into the TotalFile variable in "one fell swoop".
Open PathFileName For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
' Replace CarriageReturns with Line Feeds... this will help
' handle normal Windows files AND files generated by a Mac.
TotalFile = Replace(TotalFile, vbCr, vbLf)
' Now, if it was a normar Windows file, there will be two
' Line Feeds following each other, so we reduce them to just one.
TotalFile = Replace(TotalFile, vbLf & vbLf, vbLf)
' Now that the entire file is now contained in the TotalFile
' variable as a Line Feed delimited file, let's return those
' individual lines in an array for use by the calling code.
SplitFileIntoLines = Split(TotalFile, vbLf)
End Function

To see how to use it, consider this sample macros...

Sub Test()
Dim X As Long
Dim FileLines() As String
FileLines = SplitFileIntoLines("c:\temp\Book1.txt")
For X = 0 To UBound(FileLines)
Debug.Print FileLines(X)
Next
End Sub

Rick


"ker_01" wrote in message
...
Rick-

Thank you for your response, explanation, and sample code. For this
project I will import several different files, but their sources and
formats are so different I'm making a separate sub for each. I'll adapt
the snippet you've provided, and post back if I have any problems or
questions.

Thank you!!
Keith

"Rick Rothstein (MVP - VB)" wrote
in message ...
In the VB world, the end of line marker is a Carriage Return followed
by a Line Feed... if your lines are not delimited with this, Line Input
does not see your individual lines of text as individual lines. I'm
guessing your lines are delimited by simple Line Feeds. If this is the
case, and if this code will only be used for files generated in the
same way as the one you are currently working with, you could do away
with the Do-Loop, read the entire file into a variable and then Split
the text into individual lines using the Split function with vbLf, or
Chr(10) if you prefer, as the delimiter... this will produce a
zero-based array that you can loop through element by element (which
will then be line-by-line) and do whatever you need to. For example...

Dim X As Long
Dim TotalFile As String
Dim Paragraphs() As String
Line Input #1, TotalFile
Paragraphs = Split(TotalFile, vbLf)
For X = 0 To UBound(Paragraphs)
' Do whatever you need to on each line in your file
Debug.Print Paragraphs(X)
Next

Remember, this code will only work for Line Feed delimited text... if
your code could have to handle both Line Feed delimited text and text
delimited with Windows' normal line delimiter (a Carriage Return
followed by a Line Feed), then write back and I'll generalize the code
for you.

Rick


"ker_01" wrote in message
...

In Excel, I recorded a macro while importing a text file (because
that's the easiest way I've found to capture the fixed-width values
for my fields). It imported just fine.

Now I'm using VBA to import that same text file a line at a time and
parse each line for data values. I was having trouble (after the first
line, the code would quit). I used a msgbox to display that first line
prior to parsing- and it showed me the whole file! (or as much as
could fit in a msgbox, anyway).

So somehow, on importing the text file via the menu (file/open), Excel
knew that there were multiple lines and put the data in multiple rows.
However, when I use:

Do While Not EOF(1)
Line Input #1, LineofText
MsgBox LineofText
Loop

It opens the entire file as one line.

Clearly I'm missing something here, but I'm not sure what. Any
suggestions?

Thank you,
Keith

Rick-

I had looked up the word, I guess I didn't fully understand it's use in this
context; I would have thought that
LOF(FileNum)

would provide a numeric (Long) length of file, which I would have assumed
was sufficient to use in
Get #FileNum, , TotalFile

e.g., get from selected file (#FileNum) a total of n (TotalFile) characters

Space, from help:
"Returns a Variant (String) consisting of the specified number of spaces"

So I guess in my mind, my confusion stems from the fact that I'm translating
TotalFile = Space(LOF(FileNum))

as setting TotalFile equal to a very long empty string, rather than a
numeric value to use in
Get #FileNum, , TotalFile

Your version works just fine in my code (properly attributed with comments,
of course), but at the moment it's still code magic, since my brain is still
working on the logic above
:)

Thanks,
Keith

"Rick Rothstein (MVP - VB)" wrote in
message ...
Whenever you have a question about a keyword in VB, the first thing you
should do is click anywhere on the word and press F1 to get VB's help file
for that keyword. The Space function returns the number of spaces
(character with the ASCII value of 32) called for by the value you pass
into it (in this case, you are right, the length of the file).

Rick


"ker_01" wrote in message
...
Rick-
This looks fabulous- Just to help me learn, I understand everything
except the use of the 'space' keyword:

TotalFile = Space(LOF(FileNum))

It appears that this is getting the total file length (length of string
to load in the next line), but I'm not clear what the 'space' keyword is
doing for the statement. Can you educate me with a brief explanation?

Thank you!
Keith

"Rick Rothstein (MVP - VB)" wrote
in message ...
In case you come back to this thread, here is a general function (with
comments) that will handle any normally delimited text file (Line Feed
as from a UNIX or Linux system, Carriage Return as from a Mac, or
Carriage Feed followed by a Line Feed which is an normal Windows type
file). You pass it the full path and filename for your file and it
returns an array containing each individual line.

Function SplitFileIntoLines(PathFileName As String) As String()
Dim X As Long
Dim FileNum As Long
Dim TotalFile As String
Dim Lines() As String
' Better to let VB select the file channel number than to hard code
it.
FileNum = FreeFile
' Load entire file into the TotalFile variable in "one fell swoop".
Open PathFileName For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
' Replace CarriageReturns with Line Feeds... this will help
' handle normal Windows files AND files generated by a Mac.
TotalFile = Replace(TotalFile, vbCr, vbLf)
' Now, if it was a normar Windows file, there will be two
' Line Feeds following each other, so we reduce them to just one.
TotalFile = Replace(TotalFile, vbLf & vbLf, vbLf)
' Now that the entire file is now contained in the TotalFile
' variable as a Line Feed delimited file, let's return those
' individual lines in an array for use by the calling code.
SplitFileIntoLines = Split(TotalFile, vbLf)
End Function

To see how to use it, consider this sample macros...

Sub Test()
Dim X As Long
Dim FileLines() As String
FileLines = SplitFileIntoLines("c:\temp\Book1.txt")
For X = 0 To UBound(FileLines)
Debug.Print FileLines(X)
Next
End Sub

Rick


"ker_01" wrote in message
...
Rick-

Thank you for your response, explanation, and sample code. For this
project I will import several different files, but their sources and
formats are so different I'm making a separate sub for each. I'll adapt
the snippet you've provided, and post back if I have any problems or
questions.

Thank you!!
Keith

"Rick Rothstein (MVP - VB)" wrote
in message ...
In the VB world, the end of line marker is a Carriage Return followed
by a Line Feed... if your lines are not delimited with this, Line
Input does not see your individual lines of text as individual lines.
I'm guessing your lines are delimited by simple Line Feeds. If this is
the case, and if this code will only be used for files generated in
the same way as the one you are currently working with, you could do
away with the Do-Loop, read the entire file into a variable and then
Split the text into individual lines using the Split function with
vbLf, or Chr(10) if you prefer, as the delimiter... this will produce
a zero-based array that you can loop through element by element (which
will then be line-by-line) and do whatever you need to. For example...

Dim X As Long
Dim TotalFile As String
Dim Paragraphs() As String
Line Input #1, TotalFile
Paragraphs = Split(TotalFile, vbLf)
For X = 0 To UBound(Paragraphs)
' Do whatever you need to on each line in your file
Debug.Print Paragraphs(X)
Next

Remember, this code will only work for Line Feed delimited text... if
your code could have to handle both Line Feed delimited text and text
delimited with Windows' normal line delimiter (a Carriage Return
followed by a Line Feed), then write back and I'll generalize the code
for you.

Rick


"ker_01" wrote in message
...

In Excel, I recorded a macro while importing a text file (because
that's the easiest way I've found to capture the fixed-width values
for my fields). It imported just fine.

Now I'm using VBA to import that same text file a line at a time and
parse each line for data values. I was having trouble (after the
first line, the code would quit). I used a msgbox to display that
first line prior to parsing- and it showed me the whole file! (or as
much as could fit in a msgbox, anyway).

So somehow, on importing the text file via the menu (file/open),
Excel knew that there were multiple lines and put the data in
multiple rows. However, when I use:

Do While Not EOF(1)
Line Input #1, LineofText
MsgBox LineofText
Loop

It opens the entire file as one line.

Clearly I'm missing something here, but I'm not sure what. Any
suggestions?

Thank you,
Keith

Ugh, please ignore my last post. I'm not sure why my brain was thinking that
the third GET parameter was length, when it is actually the destination
string.

Now my understanding of (this small bit of) technology has advanced enough
that it is no longer magic...
:)

Thanks again,
Keith

"Rick Rothstein (MVP - VB)" wrote in
message ...
Whenever you have a question about a keyword in VB, the first thing you
should do is click anywhere on the word and press F1 to get VB's help file
for that keyword. The Space function returns the number of spaces
(character with the ASCII value of 32) called for by the value you pass
into it (in this case, you are right, the length of the file).

Rick


"ker_01" wrote in message
...
Rick-
This looks fabulous- Just to help me learn, I understand everything
except the use of the 'space' keyword:

TotalFile = Space(LOF(FileNum))

It appears that this is getting the total file length (length of string
to load in the next line), but I'm not clear what the 'space' keyword is
doing for the statement. Can you educate me with a brief explanation?

Thank you!
Keith

"Rick Rothstein (MVP - VB)" wrote
in message ...
In case you come back to this thread, here is a general function (with
comments) that will handle any normally delimited text file (Line Feed
as from a UNIX or Linux system, Carriage Return as from a Mac, or
Carriage Feed followed by a Line Feed which is an normal Windows type
file). You pass it the full path and filename for your file and it
returns an array containing each individual line.

Function SplitFileIntoLines(PathFileName As String) As String()
Dim X As Long
Dim FileNum As Long
Dim TotalFile As String
Dim Lines() As String
' Better to let VB select the file channel number than to hard code
it.
FileNum = FreeFile
' Load entire file into the TotalFile variable in "one fell swoop".
Open PathFileName For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
' Replace CarriageReturns with Line Feeds... this will help
' handle normal Windows files AND files generated by a Mac.
TotalFile = Replace(TotalFile, vbCr, vbLf)
' Now, if it was a normar Windows file, there will be two
' Line Feeds following each other, so we reduce them to just one.
TotalFile = Replace(TotalFile, vbLf & vbLf, vbLf)
' Now that the entire file is now contained in the TotalFile
' variable as a Line Feed delimited file, let's return those
' individual lines in an array for use by the calling code.
SplitFileIntoLines = Split(TotalFile, vbLf)
End Function

To see how to use it, consider this sample macros...

Sub Test()
Dim X As Long
Dim FileLines() As String
FileLines = SplitFileIntoLines("c:\temp\Book1.txt")
For X = 0 To UBound(FileLines)
Debug.Print FileLines(X)
Next
End Sub

Rick


"ker_01" wrote in message
...
Rick-

Thank you for your response, explanation, and sample code. For this
project I will import several different files, but their sources and
formats are so different I'm making a separate sub for each. I'll adapt
the snippet you've provided, and post back if I have any problems or
questions.

Thank you!!
Keith

"Rick Rothstein (MVP - VB)" wrote
in message ...
In the VB world, the end of line marker is a Carriage Return followed
by a Line Feed... if your lines are not delimited with this, Line
Input does not see your individual lines of text as individual lines.
I'm guessing your lines are delimited by simple Line Feeds. If this is
the case, and if this code will only be used for files generated in
the same way as the one you are currently working with, you could do
away with the Do-Loop, read the entire file into a variable and then
Split the text into individual lines using the Split function with
vbLf, or Chr(10) if you prefer, as the delimiter... this will produce
a zero-based array that you can loop through element by element (which
will then be line-by-line) and do whatever you need to. For example...

Dim X As Long
Dim TotalFile As String
Dim Paragraphs() As String
Line Input #1, TotalFile
Paragraphs = Split(TotalFile, vbLf)
For X = 0 To UBound(Paragraphs)
' Do whatever you need to on each line in your file
Debug.Print Paragraphs(X)
Next

Remember, this code will only work for Line Feed delimited text... if
your code could have to handle both Line Feed delimited text and text
delimited with Windows' normal line delimiter (a Carriage Return
followed by a Line Feed), then write back and I'll generalize the code
for you.

Rick


"ker_01" wrote in message
...

In Excel, I recorded a macro while importing a text file (because
that's the easiest way I've found to capture the fixed-width values
for my fields). It imported just fine.

Now I'm using VBA to import that same text file a line at a time and
parse each line for data values. I was having trouble (after the
first line, the code would quit). I used a msgbox to display that
first line prior to parsing- and it showed me the whole file! (or as
much as could fit in a msgbox, anyway).

So somehow, on importing the text file via the menu (file/open),
Excel knew that there were multiple lines and put the data in
multiple rows. However, when I use:

Do While Not EOF(1)
Line Input #1, LineofText
MsgBox LineofText
Loop

It opens the entire file as one line.

Clearly I'm missing something here, but I'm not sure what. Any
suggestions?

Thank you,
Keith