Clear Excel cells using Windows PowerShell

This will guide you how to use Microsoft PowerShell to clear cells in an Excel worksheet.

 

Loading the Excel file from your location :

 #####################################################################
 ## Load Excel file
 $ExcelPath = 'D:\MyInputFile.xlsx'
 $Excel = New-Object -ComObject Excel.Application
 $Excel.Visible = $false
 $ExcelWorkBook = $Excel.Workbooks.Open($ExcelPath)
 $ExcelWorkSheet = $Excel.WorkSheets.item('MySheetName')

Writing the clearing logic with the help of nested For loops for rows and columns :

 #####################################################################
 ## Clear Logic
 $RowMax = ($ExcelWorkSheet.UsedRange.Rows).count        ## get the total count of used rows
 for($i=2; $i -le $RowMax; $i++)         ## skip the header
 {
        for($j=2;$j -le 12;$j++)
        {
                $ExcelWorkSheet.Cells.Item($i, $j).clear()
        }
 }

Saving and closing all connections to Excel :

 #####################################################################
 # Close connections to Excel
 $Excel.DisplayAlerts = $false
 $Excel.ScreenUpdating = $false
 $Excel.Visible = $false
 $Excel.UserControl = $false
 $Excel.Interactive = $false        ## set interactive to false so no save buttons are shown
 $Excel.ActiveWorkbook.Save()        ## save the workbook
 $Excel.ActiveWorkbook.Close()        ## quit the workbook
 $Excel.Quit()

Clearing the object references is always a good idea. You can use the following snippet to do so :

 #####################################################################
## Close all object references
 Release-Ref($ExcelWorkSheet)
 Release-Ref($ExcelWorkBook)
 Release-Ref($Excel)

 ## Function to close all com objects
 function Release-Ref ($ref)
 {
        ([System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$ref) -gt 0)
        [System.GC]::Collect()
        [System.GC]::WaitForPendingFinalizers()
 }

Sample Input :

 

Sample Output :

Complete Script can be downloaded here.