Copy multiple Excel worksheets from multiple workbooks to a new workbook using PowerShell

Question

I have been at this for a while and can't seem to find anything that does exactly what I want. I was working off this post: How to use PowerShell to copy several Excel worksheets and make a new one but it doesn't do exactly what I am looking for. I am attempting to copy all worksheets from multiple workbooks and place them in a new workbook. Ideally, I would like to get the file name from one of the workbooks and use it to name the new file in a new directory.

I have found numerous examples of code that can do this, but there are a couple of key features that I am missing and not sure how to implement them. Here is an example of what I have working now:

$file1 = 'C:\Users\Desktop\TestFolder\PredictedAttritionReport.xlsx' # source's fullpath
$file2 = 'C:\Users\Desktop\TestFolder\AdvisorReport' # destination's fullpath

$xl = new-object -c excel.application
$xl.displayAlerts = $false # don't prompt the user
$wb2 = $xl.workbooks.open($file1, $null, $true) # open source, readonly
$wb1 = $xl.workbooks.open($file2) # open target
$sh1_wb1 = $wb1.sheets.item('Report') # second sheet in destination workbook
$sheetToCopy = $wb2.sheets.item('Report') # source sheet to copy

$sh1_wb1.Name = "DeleteMe$(get-date -Format hhmmss)" #Extremely unlikely to be a duplicate name
$sheetToCopy.copy($sh1_wb1)  # copy source sheet to destination workbook
$sh1_wb1.Delete()

$wb2.close($false) # close source workbook w/o saving
$wb1.close($true) # close and save destination workbook
$xl.quit()
spps -n excel

The problem that I have is that I need this to work, such that I don't have to input the actual file name since those names may be different each time they are created and there may be 3 or 4 files with more than one worksheet, where this example works off of only two named files. Additionally, I would like to be able to copy all worksheets in a file instead of just a single named worksheet, which in this case is 'Report'. The final piece is saving it as a new Excel file rather than overwriting the existing destination file, but I think I can figure that part out.

Answer 1

You could specify parameters when you call the script, and use the specified values in place of the sheet/file names as required. Read about PowerShell Parameters here. Reply if you need more info on how to implement.

Answer 2

This function will copy over sheets from one excel workbook to other. You can call with Copy-AllExcelSheet command one, it is loaded into memory.

See below in example:

Function Copy-AllExcelSheet()
{
    param($TargetXls, $sourceXls)
    $xl = new-object -c excel.application
    $xl.displayAlerts = $false

    $sourceWb = $xl.Workbooks.Open($sourceXls,$true,$false)
    $targetWB = $xl.Workbooks.Open($TargetXls)

    foreach($nextSheet in $sourceWb.Sheets)
    {

        $nextSheet.Copy($targetWB.Sheets[1])

    }
    $sourceWb.close($false)
    $targetWB.close($true)
    $xl.Quit()
}

#To call the function
Copy-AllExcelSheet "c:\Targetfile.xlsx" "c:\sourceFile.xlsx"