Use powershell to merge many excel files as one

What Will I Learn?

• How to use powershell to merge many excel files as one excel file

  

Requirements

• Windows or other system like linux,macos

• Powershell

  

• Coding IDE

  

• .NET framework

Difficulty

• Intermediate

Tutorial Contents

Before contents:

If you do not know any about powershell ,you had better reading this post firstly as well as consulting more from the books

some powershell foundation

---

Create excel application object

$ExcelObject=New-Object -ComObject excel.application
$ExcelObject.visible=$true

• New-Object : Create new excel application com object
• visible : Set it visible so we can see the running process

---

Read the excel files

$ExcelFiles=Get-ChildItem -Path C:\Users\Administrator\Desktop\excel

• Get-ChildItem: Get all the files in the folder
• Path : Set the folder path to read

---

Show all the excel files in the folder

foreach($ExcelFile in $ExcelFiles){
 
$ExcelFile.FullName

 
}

• FullName :Get the files full name ,which contains the url path and file name

  ​

---

Create Workbookin the excel application to save the data

$Workbook=$ExcelObject.Workbooks.add()
$Worksheet=$Workbook.Sheets.Item("Sheet1")

• Workbook : Add new workbook to write data

• Sheets : Among the three sheets ,just use the first one worksheet

  ​

---

Run a foreach function to load all the excel files

foreach($ExcelFile in $ExcelFiles){
 
$Everyexcel=$ExcelObject.Workbooks.Open($ExcelFile.FullName)
$Everysheet=$Everyexcel.sheets.item(1)
$Everysheet.Copy($Worksheet)
$Everyexcel.Close()
 
}

• ExcelFile :Get every excel file in the folder
• Copy :Copy every excel file data to our workbook
• Close : After copying ,close the excel file

---

Save our excel workbook as a excel file then quit running

$Workbook.SaveAs("C:\Users\Administrator\Desktop\excel\merge.xlsx")
$ExcelObject.Quit()

• SaveAs: Save the excel application workbook to a excel file

---

The whole program

$ExcelObject=New-Object -ComObject excel.application
$ExcelObject.visible=$true
$ExcelFiles=Get-ChildItem -Path C:\Users\Administrator\Desktop\excel

$Workbook=$ExcelObject.Workbooks.add()
$Worksheet=$Workbook.Sheets.Item("Sheet1")

foreach($ExcelFile in $ExcelFiles){
 
$Everyexcel=$ExcelObject.Workbooks.Open($ExcelFile.FullName)
$Everysheet=$Everyexcel.sheets.item(1)
$Everysheet.Copy($Worksheet)
$Everyexcel.Close()
 
}
$Workbook.SaveAs("C:\Users\Administrator\Desktop\excel\merge.xlsx")
$ExcelObject.Quit()

​

---

Check the effect of the program

---

Posted on Utopian.io - Rewarding Open Source Contributors

---