Hello. I have a script that I have been tweaking and trying to get to work without leaving any excel processes open. No matter what I try, there seems to be one that stays open. This causes my workbook to pop a message that the file is unlocked and can be used. I want the process to be fully killed so this error doesn’t happen.
The script is taking 2 different excel files and printing them to PDF with a date and then archiving them.
Here is my code. I am sure there are much better ways to accomplish what I am trying to do but I am very new with powershell and have pieced this together from what I could find on the web. Any help or suggestions to make this better would be much appreciated.
#UPDATE DATA IN EXCEL FILES
#THEN CREATE PDF FILE
[string]$path = "C:\Test\Report files" #Path to Excel spreadsheets to save to PDF
[string]$savepath = "C:\Test\Archive Files"
[string]$dToday = Get-Date -Format "yyyyMMdd"
$webclient = New-Object System.Net.WebClient
$xlFixedFormat = "Microsoft.Office.Interop.Excel.xlFixedFormatType" -as [type]
$excelFiles = Get-ChildItem -Path $path -include TestFile1.xlsx -recurse
$excelFiles2 = Get-ChildItem -Path $path -include TestFile2.xlsx -recurse
# Create the Excel application object
$objExcel = New-Object -ComObject excel.application
$objExcel.visible = $false #Do not open individual windows
foreach($wb in $excelFiles)
{
# Path to new PDF with date
$filepath = Join-Path -Path $savepath -ChildPath ($wb.BaseName + "_" + $dtoday + ".pdf")
# Open workbook - 3 refreshes links
$workbook = $objExcel.workbooks.open($wb.fullname, 3)
$worksheet = $objExcel.worksheets.item(1)
$workbook.RefreshAll()
# Give delay to save
Start-Sleep -Seconds 10
# Save Workbook
$workbook.Saved = $true
"saving $filepath"
#Export as PDF
$worksheet.ExportAsFixedFormat($xlFixedFormat::xlTypePDF, $filepath)
$objExcel.Workbooks.close()
$objExcel.Quit()
}
$objExcel = $workbook = $worksheet = $null
[GC]::Collect()
Start-Sleep -Seconds 10
# Create the Excel application object
$objExcel = New-Object -ComObject excel.application
$objExcel.visible = $false #Do not open individual windows
foreach($wb in $excelFiles2)
{
# Path to new PDF with date
$filepath = Join-Path -Path $savepath -ChildPath ($wb.BaseName + "_" + $dtoday + ".pdf")
# Open workbook - 3 refreshes links
$workbook = $objExcel.workbooks.open($wb.fullname, 3)
$worksheet = $objExcel.worksheets.item(1)
$workbook.RefreshAll()
# Give delay to save
Start-Sleep -Seconds 10
# Save Workbook
$workbook.Saved = $true
"saving $filepath"
#Export as PDF
$worksheet.ExportAsFixedFormat($xlFixedFormat::xlTypePDF, $filepath)
$objExcel.Workbooks.close()
$objExcel.Quit()
}
$objExcel = $workbook = $worksheet = $null
[GC]::Collect()
[string]$archpath = "C:Test\Archive Files\TestFile1_$dToday.pdf"
[string]$archpath2 = "C:Test\Archive Files\TestFile2_$dToday.pdf"
[string]$sharepath = "\\spbi.sharepoint.net\sites\BRB\Home\Reports"
Copy-Item -Path $archpath -Destination $sharepath -PassThru
Copy-Item -Path $archpath2 -Destination $sharepath -PassThru
Hi, welcome back 
,
Your script as written does close all the processes when I test. However, when working with COM objects, it’s always a good idea to explicitly release them. Try adding
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)
before [GC]::Collect().
Thanks Matt!
So I made the change you suggested and I am getting a couple of errors. It’s as if releasing the com object prevents it from finding what it needs to continue.
Here is the updated code:
#UPDATE DATA IN EXCEL FILES
#THEN CREATE PDF FILE
[string]$path = "C:\Test\Report files" #Path to Excel spreadsheets to save to PDF
[string]$savepath = "C:\Test\Archive Files"
[string]$dToday = Get-Date -Format "yyyyMMdd"
$webclient = New-Object System.Net.WebClient
$xlFixedFormat = "Microsoft.Office.Interop.Excel.xlFixedFormatType" -as [type]
$excelFiles = Get-ChildItem -Path $path -include TestFile1.xlsx -recurse
$excelFiles2 = Get-ChildItem -Path $path -include TestFile2.xlsx -recurse
# Create the Excel application object
$objExcel = New-Object -ComObject excel.application
$objExcel.visible = $false #Do not open individual windows
foreach($wb in $excelFiles)
{
# Path to new PDF with date
$filepath = Join-Path -Path $savepath -ChildPath ($wb.BaseName + "_" + $dtoday + ".pdf")
# Open workbook - 3 refreshes links
$workbook = $objExcel.workbooks.open($wb.fullname, 3)
$worksheet = $objExcel.worksheets.item(1)
$workbook.RefreshAll()
# Give delay to save
Start-Sleep -Seconds 10
# Save Workbook
$workbook.Saved = $true
"saving $filepath"
#Export as PDF
$worksheet.ExportAsFixedFormat($xlFixedFormat::xlTypePDF, $filepath)
$objExcel.Workbooks.close()
$objExcel.Quit()
}
$objExcel = $workbook = $worksheet = $null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)
[GC]::Collect()
Start-Sleep -Seconds 10
# Create the Excel application object
$objExcel = New-Object -ComObject excel.application
$objExcel.visible = $false #Do not open individual windows
foreach($wb in $excelFiles2)
{
# Path to new PDF with date
$filepath = Join-Path -Path $savepath -ChildPath ($wb.BaseName + "_" + $dtoday + ".pdf")
# Open workbook - 3 refreshes links
$workbook = $objExcel.workbooks.open($wb.fullname, 3)
$worksheet = $objExcel.worksheets.item(1)
$workbook.RefreshAll()
# Give delay to save
Start-Sleep -Seconds 10
# Save Workbook
$workbook.Saved = $true
"saving $filepath"
#Export as PDF
$worksheet.ExportAsFixedFormat($xlFixedFormat::xlTypePDF, $filepath)
$objExcel.Workbooks.close()
$objExcel.Quit()
}
$objExcel = $workbook = $worksheet = $null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)
[GC]::Collect()
[string]$archpath = "C:Test\Archive Files\TestFile1_$dToday.pdf"
[string]$archpath2 = "C:Test\Archive Files\TestFile2_$dToday.pdf"
[string]$sharepath = "\\spbi.sharepoint.net\sites\BRB\Home\Reports"
Copy-Item -Path $archpath -Destination $sharepath -PassThru
Copy-Item -Path $archpath2 -Destination $sharepath -PassThru
Exception calling "ReleaseComObject" with "1" argument(s): "Object reference not set to an instance of an object."
At C:\Test\Powershell Files\PrintPDF.ps1:38 char:1
+ [System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : NullReferenceException
That’s because you’re setting the variable to $null on the line above.
$objExcel = $workbook = $worksheet = $null
When I comment those 2 lines out, an excel process hangs and I get the notification I am trying to prevent.
OK, that I can replicate. Try it like this:
Remove-Variable 'workbook','worksheet'
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)
[GC]::Collect()
Unfortunately I am still getting the hung process and the pop up as well.
Both processes close without problem for me. Could it be a problem with the spreadsheets? I notice you’re refreshing links, for example, could something running in Excel itself be preventing them closing?
Does it work with simpler documents? My test documents just have ‘abc’ in cell A1.
I’ll have to test on some smaller documents. These excel files are running a MS Power Query inside so I have to refresh the files in order for that query to run and give me the results I want on the first tab. Other tabs are what the query is running against.