Powershell Worksheet.saveAs saving wrong Sheet

Question

I am trying to save a specific Excel Sheet from a Macro Enabled Excel Workbook (xlsm) via Powershell to csv to upload it into a database. This is done via Powershell since it needs to be automated along with some more data processing etc.

The Situation:

1. I have a list of excel files in a directory, each having the same structure/sheets.
2. I parse each file to a Powershell function which creates a new Excel Object and opens the workbook.
3. In the next step I am trying to save a specific sheet (here Sheet with Index 2)

The Problem:

1. Iterating through each Worksheet and saving them gives me all Sheets including the one I am looking for (Sheet 2)
2. Accessing Sheets 2 via $ws = $wb.Worksheets(2) also gives me the right Sheet (according $ws.name) but saving $ws via $ws.SaveAs("$destinationDirectory" + $File.BaseName + ".csv", 6) results in a csv file containing Sheet 1.

I have saved Worksheets with basically exactly the same code successfully before (except instead of xlsm I was dealing with xlsx).

Code

Function ExcelToCsv ($File) {
    echo "Converting $($File.Name) to csv..."

    $Excel = New-Object -ComObject Excel.Application;
    $Excel.DisplayAlerts = $False;

    $wb = $Excel.Workbooks.Open($File)
    $ws = $wb.Worksheets(2)
    
    echo "ws is:" + $ws.name # Correctly printing Worksheet name of Sheet 1

    $ws.SaveAs("$destinationDirectory" + $File.BaseName + ".csv", 6) # Saving Sheet 1 instead of Sheet 2

    $wb.Close($True);
    }
}

foreach ($file in $files){
  ExcelToCsv -File $file; 
}

Workaround

My current workaround is to iterate through the sheets via foreach

$n = 1
foreach($ws in $wb.Worksheets){
    $ws.SaveAs("$destinationDirectory" + $File.BaseName + "-$($n).csv", 6)
    $n = $n+1
}

And deleting any unwanted sheets (recognized by $n != 2) via

Remove-Item "$($destinationDirectory)*-[13456789].csv";

Which works but is not really optimal. Also: checking for $n -eq 2 in the foreach and only saving that sheet also does not work since it will simply save sheet 1 again.

And ideas are greatly appreciated!

Answer 1

After a frustrating afternoon I eventually worked this one out - you need to call Activate against the sheet you want to save e.g.

$sheet = $book.sheets.item('User_Specified_Report')
$Sheet.Activate()
$sheet.SaveAs($newName,6)