Excel Macro file locked after script completion-CodePudding

I have a peculiar issue. I've written a script that pulls some properties of files in a destination, generates three 'reports' and comines then i a single xlsx file. After that a macro is applied to two of worksheets present and it works like a charm. The issue is that in order to use my macro I had to open personal.xlsb as a second workbook and even though I colse it with .Close() it remains locked for editing. Any ideas?

Code used

$excel = New-Object -com excel.application
$excel.SheetsInNewWorkbook = 2
$excel.displayalerts = $false
$workbook = $excel.workbooks.add()
$worksheet = $workbook.worksheets
$first = $worksheet.item(1)
$first.name = "First"
$second = $worksheet.item(2)
$second.name = "Second"
$wbPersonalXLSB = $excel.workbooks.open("$env:USERPROFILE\Application Data\Microsoft\Excel\XLSTART\PERSONAL.XLSB")
$path = "PATH"
$GCI = GCI $path -file -Recurse -ErrorAction SilentlyContinue


$hashes =
$GCI|
Get-FileHash -Algorithm MD5 -ErrorAction SilentlyContinue|
select Algorithm, Hash, @{l="File";e={$_.Path.split("\")|select -Last 1}},@{l="Path";e={$_.Path.Substring(0,$_.Path.LastIndexof('\'))}}, @{l="Link";e={$_.Path}}|
Group -property "Hash"|
Where {$_.Count -ge 2}|
select -Expand Group
$hashes|ConvertTo-Csv -NoTypeInformation -Delimiter "`t"|clip
$first.Cells.Item(1).pastespecial()|out-null
$first.activate()
$excel.run("PERSONAL.XLSB!Empty_Row_Dupes")

$filenames =
$GCI|
Select @{l='File';e={$_.PSChildName}}, @{l='Compare Filename';e={$_.BaseName.replace('_','*').replace(' ','*').replace('-','*')}},  Directory, FullName, @{l="Extension";e={$_.Extension}}|
group -Property 'Compare Filename'|
Where {@($_.Group.Extension |Sort -Unique).Count -ge 2}|
select -expand Group
$filenames|ConvertTo-Csv -NoTypeInformation -Delimiter "`t"|clip
$second.Cells.Item(1).pastespecial()|out-null
$second.Activate()
$excel.run("PERSONAL.XLSB!Empty_Row_Dupes")

$third = $worksheet.add([System.Reflection.Missing]::Value,$worksheet.Item($worksheet.count))
$third.Name = "Third"
$zero_length = 
$GCI|
? {$_.Length -eq 0}|
Select @{l='File';e={$_.PSChildName}}, Length, Directory, FullName
$zero_length|ConvertTo-Csv -NoTypeInformation -Delimiter "`t"|clip
$third.cells.item(1).pastespecial()|out-null
$third.range("A1:D1").Interior.Color = 8454080


$wbPersonalXLSB.Close()
$save = "XLSX_PATH"
$workbook.saveas($save)
$workbook.close()
$excel.quit()

        while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($first)){}
        while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($second)){}
        while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($third)){}
        while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet)){}
        while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook)){}
        while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)){}

CodePudding user response：

As ridiculous as it may sound, changing personal.xlsb to personal_2.xlsb and updating code accordingly seems to have resolved the issue.

CodePudding user response：

The problem is that your code to release the COM object is missing to call to [System.GC]::Collect(), so these objects may linger for longer than needed in memory, keeping the files locked.

Also, don't forget to release $wbPersonalXLSB as well..

End your code with:

$excel.quit()

# release the COM objects from memory, so the files lose their lock
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($wbPersonalXLSB)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($first)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($second)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($third)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()