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
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
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.
Regards,
--------------------------------------------------------------------------------------------------------
Shrivallabha
--------------------------------------------------------------------------------------------------------
Using Excel 2016 in Home / 2010 in Office
--------------------------------------------------------------------------------------------------------
Thanks mate,
You are right (It hasn't worked in my Excel 2010)
Kindly advice how to use FileSystemObject..
Maybe help.
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
Regards,
--------------------------------------------------------------------------------------------------------
Shrivallabha
--------------------------------------------------------------------------------------------------------
Using Excel 2016 in Home / 2010 in Office
--------------------------------------------------------------------------------------------------------
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
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.
Regards,
--------------------------------------------------------------------------------------------------------
Shrivallabha
--------------------------------------------------------------------------------------------------------
Using Excel 2016 in Home / 2010 in Office
--------------------------------------------------------------------------------------------------------