PowerShell Script Refreshing Excel with MS Query Not Unlocking File

Hello everyone!

I am a rather novice user of PowerShell and do most of my scripting by finding what I need online and tweaking it for my needs. I have been using this script for a few years that will open an Excel workbook, refresh it, print the first page to a PDF and close it. Recently, I changed that first page so that I could generate some dynamic tables using MS Query that pulls data from another tab. The excel file works fine if you open it manually and let it refresh. However, the script now causes the file to be locked for editing and keeps the excel processes open in the background without clearing them up. Something appears to be hanging up on the MS Query running in the workbook and I can’t figure out what it is.

Here is my code:

#UPDATE DATA IN EXCEL FILES
#THEN CREATE PDF FILE

[string]$path = "M:\temp"  #Path to Excel spreadsheets to save to PDF
[string]$savepath = "M:\temp\archives"
[string]$dToday = Get-Date -Format "yyyyMMdd"

$xlFixedFormat = "Microsoft.Office.Interop.Excel.xlFixedFormatType" -as [type] 
$excelFiles = Get-ChildItem -Path $path -include Daily.xlsx -recurse 
$excelFiles2 = Get-ChildItem -Path $path -include DailyTotals.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()

	 Start-Sleep -Seconds 5
	 
	 # Save Workbook
	 #$workbook.Saved = $true 
	"saving $filepath" 
	 #Export as PDF
	 $worksheet.ExportAsFixedFormat($xlFixedFormat::xlTypePDF, $filepath) 
	 $objExcel.Workbooks.close() 
	} 
$objExcel.Quit()

[GC]::Collect()

# 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 $path -ChildPath ($wb.BaseName + ".pdf") 
	 # Open workbook - 3 refreshes links
	 $workbook = $objExcel.workbooks.open($wb.fullname, 3)
	 $worksheet = $objExcel.worksheets.item(1)
	 $workbook.RefreshAll()

	 Start-Sleep -Seconds 5
	 
	 # Save Workbook
	 $workbook.Saved = $true 
	"saving $filepath" 
	 #Export as PDF
	 $worksheet.ExportAsFixedFormat($xlFixedFormat::xlTypePDF, $filepath) 
	 $objExcel.Workbooks.close() 
	} 
 $objExcel.Quit()


[GC]::Collect()

Have you tried releasing the object?

…
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)
…

I have. I put one before each [GC]::Collect(). The file pops a box that says the file is now unlocked for editing. I have to click cancel so the file opens. None of that happened before I added the MS Query in excel that generates the tables.

Bumping this hoping it catches someone’s eye today that can help. Happy Friday!

Excel is probably spawning child processes for the query or RefreshAll and the script is just waiting 5 seconds. You should look for more control of the refresh to confirm it’s done versus a sleep. Like this:

excel - Wait until ActiveWorkbook.RefreshAll finishes - VBA - Stack Overflow

Another option is adjusting the sleep, try 30 or 60 seconds and see if you have the same issue, but refreshing everything and then giving it 5 seconds is most likely the biggest issue.

1 Like

I read the article and the main thing I tried to do was disable background refresh in the excel workbook. That seems to help but there is still a straggling excel process. There is still also a prompt when the script finishes running that the file is now available for editing. I think they are related but I don’t want that prompt to happen.

I also tried adjusting the sleep timers to 30 and 60 seconds and it didn’t make a difference. Is there something else in that article I should try and how would I integrate it with my code?

Have you tried just killing excel?


Get-Process | Where-Object {$_.Name -Match 'excel'} | Stop-Process

1 Like

If you execute the code up to $workbook.RefreshAll(), look at the processes with Get-Process. Then execute the refresh and look at the processes and validate that the refresh is spawning those child processes. It’s still better to actually validate that the Refresh is complete:

Wait for the excel refresh to complete before executing save (next) step (microsoft.com)

2 Likes

I thought about it but I was worried that it would kill ALL excel processes running. I sometimes have multiple automated scripts running at the same time with different workbooks so I want to make sure only the processes for the current script are stopped. Maybe this does that? I wasn’t sure.

Ok, I will take a look and give this a try.

You are correct, what I posted will nuke all instances of Excel :frowning: It seems that even with several instances open, there is only one “excel.exe” process, at least on my system.

1 Like

@rob-simmers I have verified that the processes appear to being spawned by the refresh all. Where would I implement this code from the article you posted into mine?

foreach ($Sheet in $excelworkbook.Worksheets) {
    foreach ($QTable in $Sheet.QueryTables) {
        $QTable.BackgroundQuery = $false
    }
}

The article indicates to put it before the .RefreshAll. The idea is if the backgroundquery is allow, you can do other thing in Excel while the query is executing. This turns that off, so excel should remain busy until it completes.