Excel merge files 1

[jjatcal]

I have 300 Excel files that each contain 5-10 rows of data. All the columns in each of the 300 files are identical. How can I combine all 300 files into one?

Thanks for your help!

 

[Mikeb1970]

Hi jjatcal,

I'm afraid i can't help you at this point, because i don't know enough about the task you wanna perform.

it this a task you wanna perform once, or regular?

and more important, is there any logic in the filenames?

please lett me know, maybe we could help

Mike

PS a question well asked, brings the answer a footstep away

 

[KenWright]

Are they all on the first sheet in each file. Is there more than one sheet per file. What are the ranges in each file (Generally anyway), ie how many columns. Does the data all start in the same place or on the same row. Do you want all the data on a single sheet, or would you like a single file with 300 sheets???

Regards
Ken.............


----------------------------------------------------------------------------
Seasons Greetings and Very Best wishes to all

----------------------------------------------------------------------------

 

[jjatcal]

All the data is in one sheet. There are 3 sheets (default Excel) and I only need the first one. I would like all 300 sheets (5-10 rows each) on one sheet.

Range is up to column AH.

This is a one time thing, just tyring to making it easier rather than cutting and pasting from all 300 sheets.

Logic in filenames? You mean code in filenames? No, they are just named 001.csv 002.csv etc.

Yes, data is exactly the same on every sheet. The first column is the same in every sheet, so is the second and so on.

 

[KenWright]

OK, lastly then, whereabouts is the data on the sheets. Does it all start from Row 1 on each sheet, or do you have headings in Row 1 or row whatever on each sheet. Do they all start on the same row, and do you want those headings if they exist to be pulled in as well?

Regards
Ken..............

----------------------------------------------------------------------------
Seasons Greetings and Very Best wishes to all

----------------------------------------------------------------------------

 

[jjatcal]

There is a header row. The data starts from the second row and first column. It is the same on all the sheets.

It'd be great if I could keep the header row, but is not required. Whatever works easiest.

And say if I did run across this situation again (to answer a previous question), how would I do that?

Thanks!!!

 

[KenWright]

OK. header row is not a problem, and you get the option to lose or keep it in the routine. It does though, assume that if you have a header row, that is immediately above your data with no blank lines in between (Only important if you tell it not to include headers). I have also set the routine to give you a running count of how many files and what file it is dealing with in the statusbar bottom left of your screen, and lastly it will also put the filename of the file it is copying to the right of your data on each line, so you can do some checking if you want. This routine assumes that all your files are in a single folder, and it will start pasting the data at Row 3 on whatever sheet you are on when you start this. It is currently set to a test folder on my drive, so you need to change the folder path in the routine, ie change the line that says

Set objFolder = objFSO.GetFolder("c:\4 test\&quot to whatever your path is. make sure you don't lose the quotes when you do this.

This also uses the Microsoft Scripting Runtime library, so you need to set a reference to that using Tools / References. To do this you need to be in the VB Editor, so Hit ALT+F11 and this will open the VBE (Visual Basic Editor)

Top left you will hopefully see an explorer style pane. Within this pane you need to search for
your workbook's name, and when you find it you may need to click on the + to expand it. Within
that you should see the following:-

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
ThisWorkbook
Modules
Module1
Module2
etc etc (You may have just 1 or none of these)

If you have named your sheets then those names will appear in the brackets above as opposed to
what you see at the moment in my note.

If you have no modules, then right click on the project name for your file and do Insert / Module and a big white space will open up. Copy and then paste in here the entire piece of code from the note below starting 'Sub GetMydata()' and finishing 'End Sub'

Now click on Tools / References and look for an option called Microsoft Scripting Runtime and tick it. Then just hit File / Close and return to Microsoft Excel and save the file

Now simply click on Tools / Macro / Macros / GetMyData, answer the prompt and away it goes

Sub GetMyData()

Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
Dim objSubfolder As Scripting.Folder
Dim objFile As Scripting.File
Dim irow As Long
Dim frow As Long
Dim hrow As Long
Dim lrow As Long
Dim nrows As Long
Dim numfiles As Long
Dim FileNo As Long
Dim ColW As Long
    
Application.ScreenUpdating = False
    
'Index or starting row has been set to row 3
    irow = 3
resp = MsgBox(Prompt:="Does your data have headers you do NOT want to pull", Buttons:=vbYesNo)
            If resp = vbNo Then
               hrow = 0
            Else: hrow = 1
            End If
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder("c:\4 test\")
    numfiles = objFolder.Files.Count
    FileNo = 0
    For Each objFile In objFolder.Files
      FileNo = FileNo + 1
      Application.StatusBar = "Processing File " & FileNo & " of " & numfiles
        If objFile.Type = "Microsoft Excel Worksheet" Then
            Workbooks.Open Filename:=objFolder.Path & "\" & objFile.Name
            
            frow = ActiveWorkbook.Worksheets(1).UsedRange.Row
            lrow = ActiveWorkbook.Worksheets(1).UsedRange.Row - 1 + _
               ActiveWorkbook.Worksheets(1).UsedRange.Rows.Count
            ColW = ActiveWorkbook.Worksheets(1).UsedRange.Column - 1 + _
               ActiveWorkbook.Worksheets(1).UsedRange.Columns.Count
            nrows = lrow - frow - hrow + 1
           
            With ActiveWorkbook.Worksheets(1)
               .Range(Cells(frow + hrow, 1), Cells(lrow, ColW)).Copy _
                  Destination:=ThisWorkbook.Worksheets(1).Cells(irow, 1)
            End With
            
            ThisWorkbook.Worksheets(1).Cells(irow, ColW + 1).Resize _
               (nrows, 1).Value = objFile.Name
            
            ActiveWorkbook.Close savechanges:=False
            
            irow = irow + nrows
        
        End If
    Next
    
Application.ScreenUpdating = True
Application.StatusBar = False
    
End Sub

Hopefully this should be easy enough to follow, but if push comes to shove I can always mail you a workbook. You will get more out of it though if you can work through the above and get it going - Should take 5/10 min max.

Regards
Ken....................

----------------------------------------------------------------------------
Seasons Greetings and Very Best wishes to all

----------------------------------------------------------------------------

 

[KenWright]

Doh - Just reread your notes. Are they Excel files, or are they csv files? I just saw your filenames and am not sure if that is the name AND the extension, or just the names?

Regards
Ken.............

----------------------------------------------------------------------------
Seasons Greetings and Very Best wishes to all

----------------------------------------------------------------------------

 

[KenWright]

If so then you will simply need to comment out two of the lines in the routine

Where it says

If objFile.Type = "Microsoft Excel Worksheet" Then

and then the

End If that follows it.

Simply put an apostrophe at the start of each line, and they should turn green (Denotes commentary text as opposed to code). Then run the routine.

Regards
Ken.............

----------------------------------------------------------------------------
Seasons Greetings and Very Best wishes to all

----------------------------------------------------------------------------

 

[KenWright]

Another question - is the data in the 300 files hardcoded data, or formulas, and if it is formulas, what do you want returend to your single sheet, formulas or hardcoded data?

Regards
Ken.............

----------------------------------------------------------------------------
Seasons Greetings and Very Best wishes to all

----------------------------------------------------------------------------

 

[jjatcal]

Ken, it comes as a spit out of the main system. So it is all hard coded. Thanks!

 

[KenWright]

OK, you should be able to go with the code just as posted then. Just make the slight edit in commenting out those two lines if the files are actually csv files.

On a reasonable spec machine, I would expect it to take about two minutes or so to consolidate all 300 files.

Let me know if it works OK for you, or whether you have any problems with it.

Regards
Ken....................

----------------------------------------------------------------------------
Seasons Greetings and Very Best wishes to all

----------------------------------------------------------------------------

 

[dukeslater]

Top notch performance, functionality, and instructions!!!!

 

[KenWright]

Thank You

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------