Rename xls file without getting save prompt


This topic contains 4 replies, has 3 voices, and was last updated by  Curtis Smith 5 months ago.

  • Author
  • #69079

    tony p


    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:

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

    $application = New-Object -ComObject Excel.Application
    $application.Visible = $false
    $binding = "System.Reflection.BindingFlags" -as [type]
    [ref]$SaveOption = "" -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)
    ## 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

    [System.Runtime.InteropServices.Marshal]::ReleaseComObject($BuiltinProperties) | Out-Null
    [System.Runtime.InteropServices.Marshal]::ReleaseComObject($document) | Out-Null
    Remove-Variable -Name document, BuiltinProperties

    ## Rename document:

    $strippedFileName = $doc.BaseName;
    $extension = $doc.Extension;
    #write-host $strippedFileName;
    $newName = "$strippedFileName" +"_" + "$date"+ "$extension";
    write-host $newName;
    Rename-Item $doc $newName

    write-host "Rename failed."

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

  • #69087

    Curtis Smith

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

    Try $application.Workbooks($ SaveChanges:=False

    • #69091

      tony p

      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

  • #69162

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

    $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.Extension.Length
        $newName = $$insertAfter,$creationDate)
        Rename-Item -Path $doc -NewName $newName
  • #69439

    Curtis Smith

    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.

You must be logged in to reply to this topic.