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