Sleeper: Consolidate files

[justdream]

Dears,

Please I wonder if there's any macro that could consolidate all:

- text files in folder to be below each other in one excel sheet
- Excel files in folder to be below each other in one excel sheet

[shrivallabha]

Here is one code that I had written for my friend for combining text files in a folder. It had a custom action based on the row number which you can ignore.

One word of caution: will run on Excel 2003 but won't on 2007 higher as application.filesearch is obsolete. You will have to use FileSystemObject.

[justdream]

Thanks mate,
You are right (It hasn't worked in my Excel 2010)

Kindly advice how to use FileSystemObject..

[omnibuster]

Maybe help.

[shrivallabha]

I had not tried anything in this regard in 2007. See if the below code helps you.


Private Sub Combine()
Dim FSO As Object
Dim ShApp As Object
Dim StartRow As Long
Dim Data As String
StartRow = 4
Set ShApp = CreateObject("Shell.Application")
Set Fldr = ShApp.BrowseForFolder(0, "Select Folder", 1, "C:\")
If Not Fldr Is Nothing Then
Set FSO = CreateObject("Scripting.FileSystemObject")
Set PFldr = FSO.GetFolder(Fldr.self.Path)
For Each ftext In PFldr.Files
Open ftext For Input As #1
Do While Not EOF(1)
Line Input #1, Data
Cells(StartRow, 1).Value = Data
StartRow = StartRow + 1
Loop
'To create space between 2 Text Files
Close #1
StartRow = StartRow + 2
Next
Else
Exit Sub
End If
'Releasing the objects
Set ShApp = Nothing
Set FSO = Nothing
Set Fldr = Nothing
Set PFldr = Nothing
End Sub

[justdream]

Dear Omnibuster,
your macro just gives me the name of the excel and text files "not the content"

Dear Shrivallabha,
your code gives me what I want, just there;s an error message if we could solve it

due to this command :Open ftext For Input As #1:

Thanks all guys for your help, too much appreciated

[shrivallabha]

Tried and Tested. I have attached file.
A word of caution: The folder shall have Text files only. No other files. I guess that is the reason it is erroring out.