Rename xls file without getting save prompt

PowerShell Help
1

Hello,

I am a novice to PowerShell and have been working on the following script to look through a directory for XLS and XLSX files. Afterwards, it would get the creation date of each file and rename the filename with the creation date appended to the end.

This script works fine for XLSX files. However when XLS files are encountered, the is save prompt: “Want to save your changes to xxx.xls?”

How can I get rid of this save prompt. Below is my code. Thank you:

Param(
$path = “C:\Excel”,
[array]$include = @(“.xlsx",".xls”)
)

$application = New-Object -ComObject Excel.Application
$application.Visible = $false
$binding = “System.Reflection.BindingFlags” -as [type]
[ref]$SaveOption = “microsoft.office.interop.Excel.WdSaveOptions” -as [type]

Get documents

$docs = Get-childitem -path $Path -Recurse -Include $include    #Remove -Recurse if you dont want to include subfolders.

Iterate documents

foreach($doc in $docs)
{
try
{
## Get document properties:
$document = $application.Workbooks.Open($doc.fullname)
$BuiltinProperties = $document.BuiltInDocumentProperties
$pn = [System.__ComObject].invokemember(“item”,$binding::GetProperty,$null,$BuiltinProperties,“Creation Date”)
$value = [System.__ComObject].invokemember(“value”,$binding::GetProperty,$null,$pn,$null)

    ## Clean up
        $document.close([ref]$saveOption::wdDoNotSaveChanges)
        
        [System.Runtime.InteropServices.Marshal]::ReleaseComObject($BuiltinProperties) | Out-Null
        [System.Runtime.InteropServices.Marshal]::ReleaseComObject($document) | Out-Null
        Remove-Variable -Name document, BuiltinProperties
        
    ## Rename document:
         
            $date=$value.ToString('yyyyMMdd');
            $strippedFileName = $doc.BaseName;
            $extension = $doc.Extension;
            #write-host $strippedFileName;
            $newName = "$strippedFileName" +"_" + "$date"+ "$extension";
            write-host $newName;
            Rename-Item $doc $newName
           
}
catch
{ 
    write-host "Rename failed."
        $_
}

}

$application.quit()
$application.Workbooks.Close()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($application) | Out-Null

2

Not really a PowerShell question. More of an Excel VBA question, but

Try $application.Workbooks($doc.name).Close SaveChanges:=False

https://msdn.microsoft.com/en-us/library/office/ff838613.aspx

3

Thank you for the feedback Curtis. Someone on another help board provide the following solution which works.

You can trick excel into not prompting you by setting the Saved property on the workbook to true:

$document.Saved = $true
$document.close([ref]$saveOption::wdDoNotSaveChanges)

4

Hi Tony,
Is it really necessary to use Excel.Application? I think you can achieve same result like this

Param(
$path = "C:\Excel",
[array]$include = @("*.xlsx","*.xls")
)

$docs = Get-ChildItem $path -Include $include -Recurse
foreach ($doc in $docs) {
    $creationDate = $doc.CreationTime.ToString("yyyyMMdd")
    $insertAfter = $doc.name.Length - $doc.Extension.Length
    $newName = $doc.name.Insert($insertAfter,$creationDate)
    Rename-Item -Path $doc -NewName $newName
}
5

You know @aartemjevas, I had the same thought, but decided not to pursue it due to how Creation time is changed if the file is written to a new file system. Creation time as you are capturing it, is the time the file was created in the file system, where as by opening the file and reading the document properties, they are getting the time stamp of when the document was created, not when the file was created in the file system, which could be two very different time stamps.