Rename xls file without getting save prompt

Welcome Forums General PowerShell Q&A Rename xls file without getting save prompt

This topic contains 4 replies, has 3 voices, and was last updated by

 
Participant
1 year, 7 months ago.

  • Author
    Posts
  • #69079

    Participant
    Points: 0
    Rank: Member

    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

    Participant
    Points: 0
    Rank: Member

    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

      Participant
      Points: 0
      Rank: Member

      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

    Participant
    Points: 0
    Rank: Member

    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

    Participant
    Points: 0
    Rank: Member

    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.

The topic ‘Rename xls file without getting save prompt’ is closed to new replies.