Make one excel sheet from multiple text files

[cosva]

Hi,

I have 200 text files with 3 lines of text and I want to make one
sheet of all those files, so it would be a sheet with 600 lines - all
the text from those files.
Any help would be great!

Marko Svaco

 

[Guest]

This code needs to be modified, but works pretty well for excel spreadsheets.
Some people have had problems with the FileSearch function. Put all you
files in one directory and change Lookin directory. and Filetype (to text).
The .COPY statement will also need tto be modified.

Sub GetData()

Set fs = Workbooks.Application.FileSearch
With fs
.LookIn = "c:\Temp"
.FileType = msoFileTypeExcelWorkbooks
.Execute
End With

For i = 1 To fs.FoundFiles.Count

MyfileName = fs.FoundFiles(i)
Workbooks.Open Filename:=MyfileName, ReadOnly:=True

' Removed pathname from file name so it can be referenced in this
program.
'Basic doesn't like the full pathname???? stupid microsoft
Do While (1)
CharPosition = InStr(MyfileName, "\")
If CharPosition > 0 Then
MyfileName = Mid(MyfileName, CharPosition + 1)
Else
Exit Do
End If
Loop



Workbooks(MyfileName).Worksheets("Sheet1").Range("B2224").Copy _

Destination:=Workbooks(ThisWorkbook.Name).Worksheets("Sheet1"). _
Range("A10").Offset(rowOffset:=0, columnOffset:=0)

Workbooks(MyfileName).Close SaveChanges:=False

Next i


End Sub

 

[Guest]

this will get around the problem of having the sheet name and more closely
aligns to the OP's original request. To the best of my knowledge, there is
no Filetype argument for txt, so you would need to use the method illustrated
here.

Sub GetData()
Dim bk as Workbook, fs as FileSearch
Dim i as Long
Set fs = Workbooks.Application.FileSearch
With fs
.NewSearch
.LookIn = "c:\Temp"
.FileName = ".txt"
.SearchSubFolders = False
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count

set bk = Workbooks.Open(fs.FoundFiles(i), ReadOnly:=True)
Filename:=MyfileName, ReadOnly:=True



bk.Worksheets(1).Range("1:3").Copy _
Destination:=ThisWorkbook.Worksheets("Sheet1"). _
Cells(rows.count,1).End(xlup) _
.Offset(1,0)

bk.Close SaveChanges:=False

Next i
End if
End With

End Sub