Something SAP Consultants and/or SAP Business Analysts quickly discover is that using Excel is a large part of our lives.  For most of us any data you receive to mass load will be in Excel file and pretty much any data dump you do for business is expected in Excel.

This Blog focuses on merging multiple Excel files into a single Excel file.  Something that seems obvious but that I will say anyway is that all the files must be in the same format and in the same folder.

Where I have found the merge functionality useful is during SAP implementation &/or roll outs.  Specifically when given multiple Excel files that need combined together for analysis or loading purposes.

As I have mentioned in the past my goal is that any knowledgeable SAP person can successfully use the following information, however this is not a step by step instruction manual and does require a good understanding of SAP and for this Blog Excel.

Important Notes:

• This macro will only copy the first worksheet of each file.
• The macro is currently setup to only copy a (large) section of the worksheet.  See end of Blog for instructions on increasing size.
• If you use Excel regularly you know it has a lot of quirks / bugs.
  • When running this macro you may get pop up messages.  Just enter past them.
  • Every once in a while when you run the macro it may not work.  Close all Excel files and try again.

Steps – Merging Multiple Excel files into Single File

1. The Files

• All files being merged must be in the same format.
• All files being merged must be in same folder.
• Only files being merged can be in folder.

Close all open Excel files.

2. The Merge

• Open new Excel file
• Open Microsoft Visual Basic editor (ALT+F11)
• Double Click on ThisWorkBook (1 in below screenshot).
• Paste below macro code in screen on right (2 in below screenshot).
• Change menu path where Excel files are located. (3 in below screenshot – Highlighted yellow).
• Hit run icon. (4 in below screenshot).

Screen Shot: ThisWorkBook

---

Macro Code (copy / paste)

Sub simpleXlsMerger()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject(“Scripting.FileSystemObject”)

‘Important!! change folder path of excel files here
Set dirObj = mergeObj.Getfolder(“C:\Merge\Folder”)
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)

Range(“A2:IV” & Range(“A65536”).End(xlUp).Row).Copy

ThisWorkbook.Worksheets(1).Activate

‘Do not change the following column!! It’s not the same column as above
Range(“A65536”).End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close

Next

End Sub

---

Current macro is designed for Excel files with header descriptions at top (Row A1)Macro Notes:

• Current macro is setup for files that do not go past Column IV
• Current macro is setup for files that do not go past Row A65536

If file is larger than Column IV and A65536 changes can be made to following section of macro.

Range(“A2:IV” & Range(“A65536“).End(xlUp).Row).Copy

If you have question on this or any other PortSAP Consulting blog please feel free to contact us at Blog@PortSAP.com.  Or if you are looking for Top Quality SAP Consultants please feel free to contact us.

The author, Ray Hornbrook, has over 18 years of SAP functional and technical experience.  Ray started his career in SAP as a Subject Matter Expert (SME) for a SAP implementation in 1998 and is now a Senior Level SAP Consultant.  Since Ray has worked both sides of SAP, business end user and IT professional, he is able to communicate effectively with both IT and Business team members.  Having a background as an SAP business end user has helped Ray greatly in his consulting career.  The business background helps him better communicate with the business members of the team.  As well as helping bridge gaps in communication between the IT and Business team members.

To find out more about Ray Hornbrook please check out his LinkedIn profile by clicking HERE.

End of document – www.portsap.com

Neither PortSAP Consulting nor the author of this Blog is associated with SAP AG or with any of its subsidiary or alliance company. Trademarks used in our website are registered trademarks of respective companies and neither PortSAP Consulting nor the author of this Blog is associated affiliated with these companies.