Batch File Renaming with PowerShell

Below is an example of how Microsoft Excel files can be renamed in a batch using PowerShell, based on the contents of a particular cell within each file.

Firstly, the file path is set and a check is carried out to make sure that the path exists. If the path exists, the current working directory is changed and the names of all the Excel files within it, excluding those in sub-directories, are retrieved. This is followed by a check to make sure that there are files to process at the desired location. Each file is then processed one by one. If the value in the specified cell of the first sheet in each individual file is populated and it contains only valid characters, then the file is renamed and a message is displayed confirming each name change, otherwise a message is displayed stating that a particular file name could not be changed. Finally, a count of the number of files that have been renamed is displayed.

# Clear the console window.
Clear-Host

# File path.
$filePath = "c:\demo"

# Cell containing new file name.
$cellForFileName = "A1"

# Check to see if the file path exists.
if (Test-Path $filePath)
{

    # Change the current working directory.
    Set-Location $filePath

    # Retrieve the names of the Excel files.
    $files = Get-ChildItem -Path *.xlsx

    # Check if there are any Excel files.
    if ($files.Count -eq 0)
    {

        # Message stating no files to rename.
        Write-Host "There are no files to rename."

    }
    else
    {


        # Message stating files are being processed.
        Write-Host "Processing files...`n"

        # Excel file object.
        $excel = New-Object -ComObject Excel.Application

        # Set visibility and alerts to false.
        $excel.visible = $false
        $excel.DisplayAlerts = $false

        # Renamed file count.
        $filesRenamed = 0

        # Process the Excel files.
        foreach ($file in $files)
        {

            try
            {

                # Open the file and select the first sheet.
                $workBook = $excel.Workbooks.Open($file)
                $workSheet = $workBook.Sheets.Item(1)
                
                # Check if there is a value in the cell for the new file name.
                if ([string]::IsNullOrEmpty($workSheet.Range($cellForFileName).Text))
                {

                    # Close the workbook.
                    $excel.Workbooks.Close()

                    # Message confirming file could not be renamed.
                    Write-Host "The file `"$file`" could not be renamed."

                }
                else
                {

                    # Construct the new file name.
                    $newFileName = $workSheet.Range($cellForFileName).Text + ".xlsx"

                    # Check if the new file name is valid.
                    if ($newFileName.IndexOfAny([System.IO.Path]::GetInvalidFileNameChars()) `
                       -eq -1)
                    {

                        # Close the workbook.
                        $excel.Workbooks.Close()

                        # Rename the file.
                        Rename-Item -Path $file -NewName $newFileName

                        # Confirming file has been renamed.
                        Write-Host "The file `"$file`" has been renamed to `"$newFileName`"."

                        # Increment the files renamed count.
                        $filesRenamed += 1

                    }
                    else
                    {

                        # Close the workbook.
                        $excel.Workbooks.Close()

                        # Message stating file could not be renamed.
                        Write-Host "The file `"$file`" could not be renamed."


                    }


                }

            }
            catch
            {

                # Message stating file could not be renamed.
                Write-Host "The file `"$file`" could not be renamed."

            }

        }

        # Close Excel.
        $excel.Quit()
        Stop-Process -name EXCEL

        # Message stating the number of files renamed.
        if ($filesRenamed -eq 0)
        {

            Write-Host "No files have been renamed."

        }
        elseif ($filesRenamed -eq 1)
        {

            Write-Host "$filesRenamed file has been renamed."

        }
        else
        {

            Write-Host "$filesRenamed files have been renamed."

        }

    }

}
else
{

    # Message stating file path does not exist.
    Write-Host "File path does not exist."

}