1

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!

jmeier
  • 25
  • 5
  • My guess is to first copy the worksheet to a new workbook... similar to this VBA approach [here](https://stackoverflow.com/questions/48941546/macro-to-save-only-the-active-worksheet) or [here](https://stackoverflow.com/questions/43568810/how-to-save-a-sheet-in-vba). – BigBen Aug 02 '21 at 12:30

1 Answers1

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)
AndrewP-S
  • 26
  • 1
  • Thank you! I will give this a shot when I come around to using this script again. I have not thought about using Activate here. – jmeier Dec 15 '21 at 19:01