How can I make Excel read a value from a textfile and assign it to a variable in my VBA code??
 

I have a bunch of textfiles that I import to excel with some macros I
have recorded/written. There are 4 different types of textfiles, so
the macro reads the content of cell M1, e.g. "TYPE 1", and then
imports the textfile with the appropriate formatting for textfile type
1. This kind of works, but it would be much more clever if I could
make the following work: Line 58 of each textfile is specific to the
type of textfile. I therefore need to make Excel read the contents of
line 58 before importing the textfile, and assign the contents to a
variable. I could use this variable to define the necessary
formatting. So something like this:

read line 58 of textfile.txt - assign the contents of the line to
variable TYPE

Select case TYPE
case "this is a type1 file"
formatoptions 1
case "this is a type2 file"
formatoptions 2
..
case else
msgbox "unrecognized file"
End Select

So long story short: How do I say in VBA code that the contents of
line 58 in a specified file is to be assigned to a variable?

How can I make Excel read a value from a textfile and assign it to
 

Sub ReadStraightTextFile()
Dim sStr as String
Dim LineofText As String
Dim rw as Long
Dim fName as Variant
chDrive "C"
ChDir "C:\MyTextfiles"
fname = Application.GetOpenfilename( _
FileFilter:="Text Files (*.txt),*.txt")
If fName = False then exit sub

rw = 0
Open fName For Input As #1
Do While Not EOF(1)
Line Input #1, LineofText
rw = rw + 1
if rw = 58 then
sStr = LineofText
exit do
end if
Loop
Close #1
Select Case sStr
Case "this is type1 file"

.. . .


End Sub


--
Regards,
Tom Ogilvy

"haakon" wrote:

I have a bunch of textfiles that I import to excel with some macros I
have recorded/written. There are 4 different types of textfiles, so
the macro reads the content of cell M1, e.g. "TYPE 1", and then
imports the textfile with the appropriate formatting for textfile type
1. This kind of works, but it would be much more clever if I could
make the following work: Line 58 of each textfile is specific to the
type of textfile. I therefore need to make Excel read the contents of
line 58 before importing the textfile, and assign the contents to a
variable. I could use this variable to define the necessary
formatting. So something like this:

read line 58 of textfile.txt - assign the contents of the line to
variable TYPE

Select case TYPE
case "this is a type1 file"
formatoptions 1
case "this is a type2 file"
formatoptions 2
..
case else
msgbox "unrecognized file"
End Select

So long story short: How do I say in VBA code that the contents of
line 58 in a specified file is to be assigned to a variable?

How can I make Excel read a value from a textfile and assign it to
 

Open fName For Input As #1
Do While Not EOF(1)
Line Input #1, LineofText
rw = rw + 1
if rw = 58 then
sStr = LineofText
exit do
end if
Loop
Close #1

This worked perfectly, cheers :)