5

how can I save the below script after it has run?

Script is from: Powershell Disk Usage Report

$erroractionpreference = "SilentlyContinue"
$a = New-Object -comobject Excel.Application
$a.visible = $True 

$b = $a.Workbooks.Add()
$c = $b.Worksheets.Item(1)

$c.Cells.Item(1,1) = "Server Name"
$c.Cells.Item(1,2) = "Drive"
$c.Cells.Item(1,3) = "Total Size (GB)"
$c.Cells.Item(1,4) = "Free Space (GB)"
$c.Cells.Item(1,5) = "Free Space (%)"

$d = $c.UsedRange
$d.Interior.ColorIndex = 19
$d.Font.ColorIndex = 11
$d.Font.Bold = $True

$intRow = 2

$colComputers = get-content "c:\servers.txt"
foreach ($strComputer in $colComputers)
{
$colDisks = get-wmiobject Win32_LogicalDisk -computername $strComputer -Filter "DriveType = 3" 
foreach ($objdisk in $colDisks)
{
$c.Cells.Item($intRow, 1) = $strComputer.ToUpper()
$c.Cells.Item($intRow, 2) = $objDisk.DeviceID
$c.Cells.Item($intRow, 3) = "{0:N0}" -f ($objDisk.Size/1GB)
$c.Cells.Item($intRow, 4) = "{0:N0}" -f ($objDisk.FreeSpace/1GB)
$c.Cells.Item($intRow, 5) = "{0:P0}" -f ([double]$objDisk.FreeSpace/[double]$objDisk.Size)
$intRow = $intRow + 1
}
}

According to https://social.technet.microsoft.com/Forums/windowsserver/en-US/919459dc-3bce-4242-bf6b-fdf37de9ae18/powershell-will-not-save-excel-file, this will work, but I am unable to:

Add-Type -AssemblyName Microsoft.Office.Interop.Excel
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault
$Excel = New-Object -comobject Excel.Application

$Excel.Visible = $true

################
$Excel.workbooks.OpenText($file,437,1,1,1,$True,$True,$False,$False,$True,$False)
$Excel.ActiveWorkbook.SaveAs($env:tmp + "\myfile.xls", $xlFixedFormat)

$Excel.Workbooks.Close()
$Excel.Quit()
Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
Kwagga
  • 101
  • 2
  • 2
  • 5
  • 2
    Unable to integrate this code? Would this not just be a matter of including the code after the ###'s in the second snippet at the end . You should only have to change the `$Excel` to `a$` in your case. – Matt Jan 06 '15 at 14:09
  • Related: https://stackoverflow.com/q/55423220/45375 – mklement0 Aug 08 '19 at 21:42

6 Answers6

10

This worked for me :

$workbook.Close($false)
$excel.Quit()

[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workSheet)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)

Remove-Variable -Name excel
expirat001
  • 2,125
  • 4
  • 30
  • 40
  • I'm seeing the same behaviour. Can we log this as a bug with excel somewhere? Is there anywhere I can get more details on the specifics of the bug? I'm guessing it has something to do with .net references keeping the com object alive, but when the script ends and the process shuts down, shouldn't that automatically decrement any com-object-reference-counting and free it appropriately? – Groostav Dec 02 '18 at 21:02
  • Move the `[GC]::Collect()` call _after_ the `[System.Runtime.Interopservices.Marshal]::ReleaseComObject()]` calls for it to be effective in speeding up the release. The `[System.GC]::WaitForPendingFinalizers()` is pointless, because the runtime doesn't fully control the actual timing of when a COM component is released. The `Remove-Variable` call isn't strictly necessary (except if you want to prevent access to the variable afterwards, but then you should also remove `$worksheet`). – mklement0 Aug 08 '19 at 21:47
  • @Groostav: The only call in this answer that is truly _needed_ is `$excel.Quit()` - the references are _eventually_ freed automatically, after the variables have gone out of scope and garbage collection eventually runs, which can take a while. Explicitly releasing references and explicit garbage collection is only needed if you want to _speed up_ the release; for more information, see [this answer](https://stackoverflow.com/a/55423449/45375). – mklement0 Aug 08 '19 at 21:53
6

To properly and completely close Excel, you also need to release COM references. In my own testing have found removing the variable for Excel also ensures no remaining references exist which will keep Excel.exe open (like if you are debugging in the ISE).

Without performing the above, if you look in Task Manager, you may see Excel still running...in some cases, many copies.

This has to do with how the COM object is wrapped in a “runtime callable wrapper".

Here is the skeleton code that should be used:

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$workbook = $excel.Workbooks.Add()
# or $workbook = $excel.Workbooks.Open($xlsxPath)

# do work with Excel...

$workbook.SaveAs($xlsxPath)
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
# no $ needed on variable name in Remove-Variable call
Remove-Variable excel
Kory Gill
  • 6,993
  • 1
  • 25
  • 33
3

Got it working! - Special thanks to @Matt

Complete script that is working:

$erroractionpreference = "SilentlyContinue"
$a = New-Object -comobject Excel.Application
$a.visible = $True 
Add-Type -AssemblyName Microsoft.Office.Interop.Excel
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault


$a.Visible = $true

$b = $a.Workbooks.Add()
$c = $b.Worksheets.Item(1)

$c.Cells.Item(1,1) = "Server Name"
$c.Cells.Item(1,2) = "Drive"
$c.Cells.Item(1,3) = "Total Size (GB)"
$c.Cells.Item(1,4) = "Free Space (GB)"
$c.Cells.Item(1,5) = "Free Space (%)"

$d = $c.UsedRange
$d.Interior.ColorIndex = 19
$d.Font.ColorIndex = 11
$d.Font.Bold = $True

$intRow = 2

$colComputers = get-content "c:\servers.txt"
foreach ($strComputer in $colComputers)
{
$colDisks = get-wmiobject Win32_LogicalDisk -computername $strComputer -Filter "DriveType = 3" 
foreach ($objdisk in $colDisks)
{
$c.Cells.Item($intRow, 1) = $strComputer.ToUpper()
$c.Cells.Item($intRow, 2) = $objDisk.DeviceID
$c.Cells.Item($intRow, 3) = "{0:N0}" -f ($objDisk.Size/1GB)
$c.Cells.Item($intRow, 4) = "{0:N0}" -f ($objDisk.FreeSpace/1GB)
$c.Cells.Item($intRow, 5) = "{0:P0}" -f ([double]$objDisk.FreeSpace/[double]$objDisk.Size)
$intRow = $intRow + 1
}
}

$a.workbooks.OpenText($file,437,1,1,1,$True,$True,$False,$False,$True,$False)
$a.ActiveWorkbook.SaveAs("C:\Users\Username\Desktop\myfile.xls", $xlFixedFormat)

$a.Workbooks.Close()
$a.Quit()
Kwagga
  • 101
  • 2
  • 2
  • 5
2

As mentioned in MSDN documentation here, the ReleaseComObject call only decrements the reference counter of that COM object by 1. If your scripts has multiple references of the same COM object, It will not release the object.

The documentation recommends using FinalReleaseComObject method to completely release the COM object and close the Excel process once in for all.

Just be sure to call this method only when you are done with the COM reference, as not doing so may lead to bugs which are hard to debug.

Sumant
  • 77
  • 6
0

Creating the Excel file:

$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $True 
......

Closing down the Excel:

$Excel.Close()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
spps -n Excel
Tomasz Wieczorkowski
  • 163
  • 2
  • 9
0

This solves my issue when $excel.Quit() does not quit and OneDrive won't upload the file. In my case I just need some automation and after the job is done it is quite fine that all the Excel processes are killed.

$excel.Quit()

# Check and you will see an excel process still exists after quitting
# Remove the excel process by piping it to stop-process
# Warning: This Closes All Excel Processes


Get-Process excel | Stop-Process -Force
Babak Bandpey
  • 900
  • 1
  • 12
  • 20
  • 1
    Thank you for this code snippet, which might provide some limited, immediate help. A [proper explanation](https://meta.stackexchange.com/q/114762/9193372) would greatly improve its long-term value by showing why this is a good solution to the problem and would make it more useful to future readers with other, similar questions. Please edit your answer to add some explanation, including the assumptions you’ve made. – Syscall Mar 12 '21 at 14:57