Powershell SaveAs with Excel 2007

[Ted Wagner]

I thought I was doing something simple here. I tried this on a separate
machine (that has Office 2003 installed) and it works fine. But, I'm opening
a CSV in Powershell, autofit the entire worksheet and then save the workbook
as excel instead of CSV. This used to work just fine on this workstation,
until I installed Office 2007. I'm almost certain it's related to the com
object. But, I'm unable to find any references contrary to how I'm applying
the SaveAs method.

Here's the Powershell script (one version):

$Excel = New-Object -Com Excel.Application
$Excel.visible = $False
$Excel.displayalerts=$False
$WorkBook = $Excel.Workbooks.Open("C:\audit\Users.csv")
$WorkSheet = $Excel.WorkSheets.Item(1)
$WorkBook = $WorkSheet.UsedRange
$WorkBook.EntireColumn.AutoFit()
$Workbook.SaveAs("C:\audit\Users.xls")
$Excel.quit()

Here's another version:

$xlExcel8 = 56
$Excel = New-Object -Com Excel.Application
$Excel.visible = $False
$Excel.displayalerts=$False
$WorkBook = $Excel.Workbooks.Open("C:\audit\Users.csv")
$WorkSheet = $Excel.WorkSheets.Item(1)
$WorkBook = $WorkSheet.UsedRange
$WorkBook.EntireColumn.AutoFit()
$Workbook.SaveAs("C:\audit\Users.xls",$xlExcel8)
$Excel.quit()

As far as I can tell, the method should still be valid.

Here's the error I receive:
"Method invocation failed because
[System.__ComObject#{00020846-0000-0000-c000-000000000046}] doesn't contain a
method named 'SaveAs'.

Has anyone else run across anything like this?

Thanks

Ted

[Marco Shaw [MVP]]

> $WorkBook = $Excel.Workbooks.Open("C:\audit\Users.csv")

> $WorkBook = $WorkSheet.UsedRange

> As far as I can tell, the method should still be valid.
>
> Here's the error I receive:
> "Method invocation failed because
> [System.__ComObject#{00020846-0000-0000-c000-000000000046}] doesn't contain a
> method named 'SaveAs'.

I've not tried it fully, but at first glance, you're using the same
variable twice, thus your original object is no longer valid, and will
support different members (methods, properties, etc.).

Marco

--
Microsoft MVP - Windows PowerShell
http://www.microsoft.com/mvp

PowerGadgets MVP
http://www.powergadgets.com/mvp

Blog:
http://marcoshaw.blogspot.com

[Ted Wagner]

I changed the script to this:

$xlExcel8 = 56
$Excel = New-Object -Com Excel.Application
$Excel.visible = $False
$Excel.displayalerts=$False

$Excel.Workbooks.Open("C:\audit\Users.csv")

$WorkSheet = $Excel.WorkSheets.Item(1)
$WorkBookNew = $WorkSheet.UsedRange
$WorkBookNew.EntireColumn.AutoFit()
$WorkbookNew.SaveAs("C:\audit\Users.xls",$xlExcel8)
$Excel.quit()

But, after looking at that, I modified it to this:

$xlExcel8 = 56
$Excel = New-Object -Com Excel.Application
$Excel.visible = $False
$Excel.displayalerts=$False

$Excel.Workbooks.Open("C:\audit\Users.csv")

$WorkSheet = $Excel.WorkSheets.Item(1)
$WorkBook = $WorkSheet.UsedRange
$WorkBook.EntireColumn.AutoFit()
$Workbook.SaveAs("C:\audit\Users.xls",$xlExcel8)
$Excel.quit()

Same error though. If I change $Excel.visible = $True I can see all lines
process properly.... until I get to the saveas method.

Ted

[Marco Shaw [MVP]]

Try this is a similar example:
http://groups.google.com/group/microsoft.public.opsmgr.powershell/browse_thread/thread/2b1d3f4d0b82fa94/9f4a805f45415aa7?lnk=gst&q=excel#9f4a805f45415aa7

[Ted Wagner]

Thanks for the great hint Marco!

Here's my final revised script. A bit of tweaking here.

$xlExcel8 = 56
$Excel = New-Object -ComObject excel.application

$Excel.visible = $False
$Excel.displayalerts=$False
$WorkBook = $Excel.Workbooks.Open("C:\audit\Users.csv")

$WorkSheet = $Workbook.worksheets.Item(1)
$Resize = $WorkSheet.UsedRange
$Resize.EntireColumn.AutoFit()
$WorkBook.SaveAs("C:\audit\Users.xls", $xlExcel8)
$Excel.quit()

I combine this with a Powershell AD query. Query AD for info, export to
csv, convert the file, auditors are happy with a nice clean Excel spreadsheet
they don't have to resize columns or save as, etc.

Very nice.

Many thanks Marco for the great link.

Ted