Help with cleaning Excel worksheets

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()

  • I figured out getting the data to flatten

  • Still cannot exclude the date cells and have them formatted as mm/dd/yyyy.

    ForEach ($sht in $Workbook.Worksheets) { 
       $sht.Activate()
       $r = $Workbook.Activesheet.UsedRange
       $r.Select()
       $Excel.Selection.Columns.EntireColumn.Hidden = 0
       $Excel.Selection.HorizontalAlignment = -4131
       $Excel.Selection.VerticalAlignment = -4107
       $Excel.Selection.MergeCells = 0
       ForEach ($c In $r.Areas) {
           $c.Select
           $Excel.WorksheetFunction.CLEAN($c.Address)
           If ([string]$c.NumberFormat -Like "/yy") {$c.NumberFormat = "mm/dd/yyyy"}
              else {$c.NumberFormat = "@"}
    
    	}
       #$Workbook.Activesheet.Columns("D:D").NumberFormat = "mm/dd/yyyy"
       #$Workbook.Activesheet.Columns("I:I").NumberFormat = "mm/dd/yyyy"
    }  

    something just not right with the line below. Everything processes with the else clause... So dates appear as numerics as formatting is removed.

    If ([string]$c.NumberFormat -Like "/yy") {$c.NumberFormat = "mm/dd/yyyy"}

    else {$c.NumberFormat = "@"}