• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1974
  • Last Modified:

Powershell function to create an excel spreadsheet multiple sheets - and ApproveIt on workstation

The attached code is a function I am calling.  I can call the function but something won't let me create the file.  It may be approveit related.

Error: You cannot call a method on a null-valued expression.
Exception calling "Add" with "0" argument(s): "The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT))"
At :line:12 char:28
+ $Book = $Excel.Workbooks.Add <<<< ()

Anybody got any tips?  If I can't create the file, can anybody provide some assistance how to call a prefabbed excel file with multiple worksheets?
#----------------Function NEWEXCEL------------
Function NewExcel ($users2, $dir2)
{
$Excel = New-Object -Com Excel.Application
#$Excel.visible = $True
$Book = $Excel.Workbooks.Add()
$Sheet = $Book.WorkSheets.Item(1)
$Sheet.Cells.Item(1,1) = "User Name"
$Sheet.Cells.Item(1,2) = "Size (GB)"
 
$WorkBook = $Sheet.UsedRange
$WorkBook.Font.Bold = $True
$sheet.name = "$dir2"
$intRow = 2
foreach ($user in $users2) {
$sheet.Cells.Item($intRow,1) = $user.MailboxDisplayName
$sheet.Cells.Item($intRow,2) = "{0,7:n2}" -f ($user.Size / 1MB)
$Introw++
}
 
$WorkBook.EntireColumn.AutoFit()
$sheet.SaveAs("c:\$date\$date-$dir2")
$excel.quit()
}
#-----------------------------------------------

Open in new window

0
kjanicke
Asked:
kjanicke
1 Solution
 
BSonPoshCommented:
It fails WAY before the file create.

I would do it line by line in Powershell to know exactly what fails.
0
 
kjanickeAuthor Commented:
Nope.  It fails at the file create.  This is not the whole script.

It is approveit.  Rather than fiddling with the settings, I just removed ApproveIt.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now