MS Excel appending several files

Hello,
I searched the internet for a solution, but could not find one that works.  I do not have MS Query.
And I do not want to use;  copy *.csv ConsolidatedFile.csv  I would have to save every file as a csv (now xlsx files).
I have several excel files, some of these files have several "sheets" to them.
I wish to have file "1" to be consolidated/placed in "sheet1" of the master consolidation file, and file "2" in "sheet2," etc.
This might be easy for xlsx files that have only one sheet.
It could be that for those files that have more than one sheet, they would simply be copied into the next sheet.  This would be acceptable.
Is there an easy way of doing this?  There are too many files to do it manually.
chimaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamMicrosoft Excel ExpertCommented:
assuming that all of the individual workbooks have only one sheet in them, then use code below

change the mypath to the folder where your workbooks exist

Sub MergeMultiWorkbooks()
    Dim wbDst As Workbook
    Dim wbSrc As Workbook
    Dim wsSrc As Worksheet
    Dim MyPath As String
    Dim strFileName As String

    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    MyPath = "C:\ProfessorJimJam" ' change to suit
    Set wbDst = Workbooks.Add(xlWBATWorksheet)
    strFileName = Dir(MyPath & "\*.xlsx", vbNormal)

    If Len(strFileName) = 0 Then Exit Sub

    Do Until strFileName = ""

            Set wbSrc = Workbooks.Open(Filename:=MyPath & "\" & strFileName)

            Set wsSrc = wbSrc.Worksheets(1)

            wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count)
               wbDst.Worksheets(wbDst.Worksheets.Count).Name = strFileName
           wbSrc.Close False

        strFileName = Dir()

    Loop
    wbDst.Worksheets(1).Delete

    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub

Open in new window

ElrondCTCommented:
To allow more than one worksheet in an incoming file, put the copy in a loop:

    Dim iSheet, iSheetCount as Integer
    iSheetCount = wbsrc.Worksheets.Count
    For iSheet = 1 to iSheetCount 
            Set wsSrc = wbSrc.Worksheets(iSheet)
            wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count)
            wbDst.Worksheets(wbDst.Worksheets.Count).Name = strFileName & iSheet
    Next iSheet

Open in new window

chimaAuthor Commented:
ProfessorJimJam and ElrondCT I will try your both of your code.  I am embarrassed to have to ask; how would  I execute these code?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ProfessorJimJamMicrosoft Excel ExpertCommented:
@Chima,

to use this code, i have put the code into a workbook attached "EE.xlsm

first off,  create a folder in C drive and name it TEST  then download the two attachments Book1.xlsx and Book2.xlsx and put them in that folder C:\TEST  

then open download the EE.xlsm attachment and open it, then wile EE.xlsm open  in your keyboard press "Ctrl" & "q" and then the macro will run by it self and you will see a new workbook opened with all the data appended to it.

let me know how it goes.
Book1.xlsx
Book2.xlsx
EE.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ElrondCTCommented:
No need to be embarrassed; this is the place to learn. This code needs to go in a module, where you can enter VBA (Visual Basic for Applications) code. To open a module, press Alt+F11. You'll see three boxes: in the top left, a Project box; in the bottom left, a Properties box; on the right, a code box.

In the Project box, right-click on the bold line which says "VBAProject" then the name of your worksheet in parentheses. From the context menu, choose Insert, then Module. The code box will change from gray to white. You can now enter code there. Enter the entire macro, which I'll show below combining ProfessorJimJam's original and my additional code to make it less confusing.

Once it's there, and you've adjusted it as needed (note the folder name that needs to be changed at line 11), click anywhere in the macro and press F5 to run it.

One other little complication: If you're using the new format of Excel file (with a .xlsx extension), you'll need to change the extension to .xlsm if you want to save the macro with the worksheet. For security, Excel won't save a macro in a .xlsx file; by changing the extension to .xlsm, you're indicating that you understand there are macros, which can, of course, do all sorts of things, good or bad. If you're using an old format .xls file, you don't have to change its extension.
Sub MergeMultiWorkbooks()
    Dim wbDst As Workbook
    Dim wbSrc As Workbook
    Dim wsSrc As Worksheet
    Dim MyPath As String
    Dim strFileName As String
    Dim iSheet, iSheetCount as Integer

    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    MyPath = "C:\ProfessorJimJam" ' change to suit
    Set wbDst = Workbooks.Add(xlWBATWorksheet)
    strFileName = Dir(MyPath & "\*.xlsx", vbNormal)

    If Len(strFileName) = 0 Then Exit Sub

    Do Until strFileName = ""

            Set wbSrc = Workbooks.Open(Filename:=MyPath & "\" & strFileName)

            iSheetCount = wbsrc.Worksheets.Count
            For iSheet = 1 to iSheetCount 
                Set wsSrc = wbSrc.Worksheets(iSheet)
                wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count)
                wbDst.Worksheets(wbDst.Worksheets.Count).Name = strFileName & iSheet
            Next iSheet
           wbSrc.Close False

        strFileName = Dir()

    Loop
    wbDst.Worksheets(1).Delete

    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub

                                          

Open in new window

ElrondCTCommented:
Prof & I were writing simultaneously; I didn't see his message until after I posted mine...
chimaAuthor Commented:
Professor, I executed your instructions and the result was a merged file with Book1 and Book2.  The only exception was that the merged file got three tab from two files.  I looked at book1 and book2 and both had three tabs with 1s in all 3 tabs of book1 and 2s in all 3 tabs of book2.  I ended up deleting the extra tabs and now I do get one merged file with two tabs;
book1 and 2
ElrondCT
I created a new xlsm file with your code (which looks to be the same code as that of Prof's code.  I have not done a diff on them), and I changed line 11 to "C:/TEST"  When I do a "Ctrl+q" on this new .xlsm it does not execute the macro.  I appreciate your efforts to teach me, I do have some programming experience with Java, C++, but not with Visual Basic.  I did not get the boxes you mentioned, but I managed to work with what you and Prof wrote.  I will continue to troubleshoot my setup;
book1 and 2Book1andBook2.PNG
chimaAuthor Commented:
Darn book1 and book2 png did not get embedded where I wanted it to be.
ProfessorJimJamMicrosoft Excel ExpertCommented:
if you do not need the extra worksheets in destination workbook from the source workbooks

Then use my very first code that I provided.
chimaAuthor Commented:
From the "Visual Basic for Application" app I executed "Run" and my xlsm file (with your code) worked.

So why doesn't "Ctrl+q" work from my xlsm file?
chimaAuthor Commented:
Prof, I'll have to study both sets of code, or try your first set.  Thank you for the help.
Points coming shortly.
chimaAuthor Commented:
Prof, I tried your EE.xlsm code with the "modified" book1 and book2 and the results are different from your initial book1 and book2.  The tabs on yours where "related/connected" to each other.  In my files the tabs are not.  I say this because your EE.xlsm only worked (picked up) book2 and added the all of the three tabs into the merge file;
Book2 and its tabs/sheets
chimaAuthor Commented:
Oops! sorry, it does pick book1 also.  So now I have one merged file with 6 tabs/sheets.  That works well.
chimaAuthor Commented:
The EE.xlsm code generates an error when the file names are larger than 30 characters or so.  Had to rename my files.  Otherwise works well.  Now I will try some much larger files.
ProfessorJimJamMicrosoft Excel ExpertCommented:
You need consider that there is a limitation of maximum char a worksheet name can accept.

It will work with no problem with larger file too.
chimaAuthor Commented:
Prof, seems to me that if the file can be named as larger as desired, then the code should be able to handled them.  I now have some files with names like;
(Seller) (Non-Energy transaction Type) Non-Energy transaction Sales to (Buyer)
78 characters!
Is there any remedy for this.  I wish not to have to change the names of 1000 files.
ProfessorJimJamMicrosoft Excel ExpertCommented:
There is a remedy to cut short the names to the level of max accepted char in worksheet with code , not manual.
Would that be ok if your workbooks names will appear in appended file as short names?
chimaAuthor Commented:
Prof, I reckon I have no other choice.  Could this be done with a macro as well?  Unfortunately I would have to rely on you for this as well.
ProfessorJimJamMicrosoft Excel ExpertCommented:
Yes, I will include few more code in the existing code.
chimaAuthor Commented:
Thank you.  I'll submit another question for the other part.
ProfessorJimJamMicrosoft Excel ExpertCommented:
Chima,

you do not need to open another question, here is the solution for files more than 30 char

Sub MergeMultiWorkbooks()
    Dim wbDst As Workbook
    Dim wbSrc As Workbook
    Dim wsSrc As Worksheet
    Dim MyPath As String
    Dim strFileName As String
   Dim iSheet, iSheetCount As Integer

    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    MyPath = "C:\TEST" ' change to suit
    Set wbDst = Workbooks.Add(xlWBATWorksheet)
    strFileName = Dir(MyPath & "\*.xlsx", vbNormal)

    If Len(strFileName) = 0 Then Exit Sub

    Do Until strFileName = ""

            Set wbSrc = Workbooks.Open(Filename:=MyPath & "\" & strFileName)

    iSheetCount = wbSrc.Worksheets.Count
    For iSheet = 1 To iSheetCount
            Set wsSrc = wbSrc.Worksheets(iSheet)
            wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count)
            wbDst.Worksheets(wbDst.Worksheets.Count).Name = Left(strFileName, 20) & iSheet
    Next iSheet

           wbSrc.Close False

        strFileName = Dir()

    Loop
    wbDst.Worksheets(1).Delete

    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub

Open in new window

chimaAuthor Commented:
Prof, I feel that your initial response was/is worth the points I gave to you.  I wanted to give you more for the final code.  Thank you, I'll give it a test run, if I find problems, I'll open another question.
ProfessorJimJamMicrosoft Excel ExpertCommented:
Thanks chima
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.