Powershell - Get Worksheets from several excel files and save them into one excel file -with origina-CodePudding

I have searched the following code in the net, that works, but keep the original name of the worksheets when adding them in new excel file. I would need to set the original file name as the worksheet names.

Do you think that's possible? how?

Thanks!!

$ExcelObject=New-Object -ComObject excel.application

$ExcelObject.visible=$true

$ExcelFiles=Get-ChildItem -Path "C:\Users\XX/XX"

$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\XX\XX\merge.xlsx")

$ExcelObject.Quit()

CodePudding user response：

Setting names is easy, the worksheet has a .Name property that you can set, and when you copy it into your new workbook it is automatically the active sheet in that workbook, so we add one line in your loop:

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