0

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.

jomacm04
  • 11
  • 5

2 Answers2

0

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.

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
  • Thanks Ashleedawg. I have not used parameters in PowerShell before, but thanks to Krisz's example, I see how they work. If I use the parameters though, wouldn't I still have to specify the filenames that I am looking for? – jomacm04 Oct 31 '17 at 19:07
0

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"
Krisz
  • 701
  • 7
  • 23
  • Thanks Krisz. I really appreciate the help. The problem that I am facing is that I don't want to have to name all the files, since the file names will be changing constantly. Additionally, I am not sure how to get this working such that it creates a new file, rather than saving over the target file. I tried using $xl.Workbooks.Add, but I keep getting errors about a COMException. – jomacm04 Oct 31 '17 at 19:05
  • PowerShell gives you a lot of option to built up file list, and once if you have the file list you can pipeline it to this function... e.g.: ls *.xlsx -recursive | where-object name -like *MyExcelFile* – Krisz Oct 31 '17 at 20:10