CODEWITHSUNDEEP
excelpowershell
João Ciocca
João Ciocca

Reputation: 866

PowerShell + Excel - $workbook.save() hangs up on me

I started messing around with Excel on PowerShell, specially because I had to go through 138 files changing every instance of $B$1 to TEXT($B$1,"0000") and I didn't want to do that manually.

Found a couple of resources online and changed the formula but when I try .Save(), PowerShell seems to just sit there, waiting for something I have no idea what.

PS C:\> $excel = New-Object -com Excel.Application
PS C:\> Get-ChildItem '\\UNC\Path\to\folder' <Common file string>*.xls -Recurse | Select-Object -First 1 | % {
>> $workbook = $excel.Workbooks.Open($_.Fullname)
>> $workbook.Save()
>> }

Any ideas on what could be the problem?

Upvotes: 2

Views: 3255

Answers (1)

Matt
Matt

Reputation: 46710

When Excel hangs in this regard I would suspect it is waiting for you to do something. The easiest was to confirm this is setting the visibility to True.

$excel.Visible = $true

That would just show you the dialog that was hidden from you previously. As discussed in comments it appears you got a message from the Compatibly Checker. Once you know what it is and are willing to suppress it then just have the following 

$excel.Visible = $false
$excel.displayAlerts = $false

Upvotes: 2

Related Questions