Help with cleaning Excel worksheets

by Jun 11, 2020

I am trying to emulate a series of VBA commands to clean unprintable characters from Excel cells and re-format some dates with Powershell. While the code below doesn't fail, if I save the workbook after processing it doesn't appear to work. If I uncomment the line to clearformats() – that will work but then all date columns will be numeric. I'm probably doing something really dumb.

$Excel = New-Object -ComObject Excel.Application   
$Excel.visible = $false   
$Excel.DisplayAlerts = $false   
$WorkBook = $Excel.Workbooks.Open($file) 

ForEach ($sht in $Workbook.Worksheets) {  
   $r = $sht.UsedRange.Select
   $Excel.Selection.Columns.EntireColumn.Hidden = 0
   $Excel.Selection.HorizontalAlignment = -4131
   $Excel.Selection.VerticalAlignment = -4107
   $Excel.Selection.MergeCells = 0
   #$Excel.Selection.ClearFormats()
   ForEach ($c In $r.Cells) {
       $c.Select
	   If ($c.NumberFormat -NotLike "/") {$Excel.WorksheetFunction.CLEAN($c.Address) } 
          else { $c.NumberFormat = "mm/dd/yyyy" }
     }
}  
$Workbook.save()
$Workbook.close()  
$Excel.Quit()   
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($WorkBook) | Out-Null   
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) | Out-Null   
[System.GC]::Collect()   
[System.GC]::WaitForPendingFinalizers()