I have a folder with about 150 text files. I would like a macro that
would return the first line of each of those files into a spreadsheet.

The files do share a comman name with a sequence number at the end.
For example, the first filename would be:

210SHyyyy.mm.ddx.txt

where x starts at 0 and goes to 150

Is this possible?

You can use the following code to open each file and put the first
line of the text file on a worksheet, one row per file. Change the
FolderName value to the appropriate folder name, and change the
Destination value to the worksheet name and cell address where the
first text will be written.

Sub AAA()
Dim FolderName As String
Dim Destination As Range
Dim FName As String
Dim FNum As Integer
Dim S As String

'<<< CHANGE FOLDER NAME
FolderName = "C:\Test"
'<<< CHANGE DESTINATION
Set Destination = Worksheets("Sheet1").Range("A1")

ChDrive FolderName
ChDir FolderName
FName = Dir("*.txt")
Do Until FName = vbNullString
FNum = FreeFile()
Open FName For Input Access Read As #FNum
Line Input #FNum, S
Close #FNum
Destination.Value = S
Set Destination = Destination(2, 1) ' move down
FName = Dir()
Loop
End Sub

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Sat, 6 Dec 2008 06:46:36 -0800 (PST), willwonka
wrote:

I have a folder with about 150 text files. I would like a macro that
would return the first line of each of those files into a spreadsheet.

The files do share a comman name with a sequence number at the end.
For example, the first filename would be:

210SHyyyy.mm.ddx.txt

where x starts at 0 and goes to 150

Is this possible?

The code is looking at all *.txt files in ther directory FOLDER. Change
Folder as required.


Sub GetFirstLine()

Folder = "C:\temp\"


Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Set fsread = CreateObject("Scripting.FileSystemObject")


FName = Dir(Folder & "*.txt")
RowCount = 1
Do While FName < ""
Set fread = fsread.GetFile(Folder & FName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

InputLine = tsread.ReadLine
Range("A" & RowCount) = FName
Range("B" & RowCount) = InputLine

tsread.Close
FName = Dir()

RowCount = RowCount + 1
Loop
End Sub


"willwonka" wrote:

I have a folder with about 150 text files. I would like a macro that
would return the first line of each of those files into a spreadsheet.

The files do share a comman name with a sequence number at the end.
For example, the first filename would be:

210SHyyyy.mm.ddx.txt

where x starts at 0 and goes to 150

Is this possible?

Thanks... worked like a charm.


On Dec 6, 5:19*pm, Joel wrote:
The code is looking at all *.txt files in ther directory FOLDER. *Change
Folder as required.

Sub GetFirstLine()

Folder = "C:\temp\"

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Set fsread = CreateObject("Scripting.FileSystemObject")

FName = Dir(Folder & "*.txt")
RowCount = 1
Do While FName < ""
* *Set fread = fsread.GetFile(Folder & FName)
* *Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

* *InputLine = tsread.ReadLine
* *Range("A" & RowCount) = FName
* *Range("B" & RowCount) = InputLine

* *tsread.Close
* *FName = Dir()

* *RowCount = RowCount + 1
Loop
End Sub



"willwonka" wrote:
I have a folder with about 150textfiles. *I would like a macro that
would return thefirstlineof each of those files into a spreadsheet.

The files do share a comman name with a sequence number at the end.
For example, thefirstfilename would be:

210SHyyyy.mm.ddx.txt

where x starts at 0 and goes to 150

Is this possible?- Hide quotedtext-

- Show quotedtext-