CODEWITHSUNDEEP
excelpowershellsave
Kwagga
Kwagga

Reputation: 101

Excel, save and close after run

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()

Upvotes: 5

Views: 84083

Answers (6)

Babak Bandpey
Babak Bandpey

Reputation: 910

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

Upvotes: 0

Tomasz Wieczorkowski
Tomasz Wieczorkowski

Reputation: 289

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

Upvotes: 0

Sumant
Sumant

Reputation: 77

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.

Upvotes: 2

expirat001
expirat001

Reputation: 2195

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

Upvotes: 10

Kory Gill
Kory Gill

Reputation: 7163

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

Upvotes: 6

Kwagga
Kwagga

Reputation: 101

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()

Upvotes: 3

Related Questions