Rename xls file without getting save prompt

Tagged: 

This topic contains 4 replies, has 3 voices, and was last updated by Profile photo of Curtis Smith Curtis Smith 19 hours, 36 minutes ago.

  • Author
    Posts
  • #69079
    Profile photo of tony p
    tony p
    Participant

    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

  • #69087
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    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

    • #69091
      Profile photo of tony p
      tony p
      Participant

      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)

  • #69162

    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
    }
    
  • #69439
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    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.