How Can I Use Windows PowerShell to Automate Microsoft Excel?

re: How Can I Use Windows PowerShell to Automate Microsoft Excel?

Meister1867 — Thu, 29 Nov 2012 04:03:00 GMT

For Windows 8, I had to do a slight modification... I can't save to the C-drive in Windows 8 using PowerShell, so....

# Create directory if does not exist

if ((Test-Path -Path "C:\Scripts") -ne $True)

{

New-Item C:\Scripts -type directory

}

$a = New-Object -comobject Excel.Application

$a.Visible = $True

$a.DisplayAlerts = $false

$b = $a.Workbooks.Add()

$c = $b.Worksheets.Item(1)

$c.Cells.Item(1,1) = "A value in cell A1."

$b.SaveAs("C:\Scripts\Test.xlsx")

$a.Quit()

re: How Can I Use Windows PowerShell to Automate Microsoft Excel?

powersheller — Wed, 15 Dec 2010 09:06:45 GMT

If I have my PowerShell running to do Excel automation using Windows Scheduler, how do I avoid conflict with other Excel users on the same machine when my job is running? The problem is other Excel users will get affect by my job when they are using Excel while the job is running. Thanks in advance.

re: How Can I Use Windows PowerShell to Automate Microsoft Excel?

arnold — Mon, 23 Aug 2010 07:40:07 GMT

hello,

pls, how i can us the methode : .Calculate, .CalculateFull.....

thx

my best regard

re: How Can I Use Windows PowerShell to Automate Microsoft Excel?

5lb_Bass — Wed, 09 Jun 2010 15:17:09 GMT

I'm running the code in your example,

$a = New-Object -comobject Excel.Application

$a.Visible = $True

$b = $a.Workbooks.Add()

$c = $b.Worksheets.Item(1)

$c.Cells.Item(1,1) = "A value in cell A1."

$b.SaveAs("C:\Scripts\Test.xls")

$a.Quit()

and generate the following error. Any ideas?

PS H:\> c:\data\ACE-IT\projects\TabletConfiguration\test.ps1

Exception calling "Add" with "0" argument(s): "The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT))"

At C:\data\ACE-IT\projects\TabletConfiguration\test.ps1:5 char:22

+ $b = $a.Workbooks.Add <<<< ()

+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException

+ FullyQualifiedErrorId : ComMethodTargetInvocation

You cannot call a method on a null-valued expression.

At C:\data\ACE-IT\projects\TabletConfiguration\test.ps1:6 char:24

+ $c = $b.Worksheets.Item <<<< (1)

+ CategoryInfo : InvalidOperation: (Item:String) [], RuntimeException

+ FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.

At C:\data\ACE-IT\projects\TabletConfiguration\test.ps1:8 char:14

+ $c.Cells.Item <<<< (1,1) = "A value in cell A1."

+ CategoryInfo : InvalidOperation: (Item:String) [], RuntimeException

+ FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.

At C:\data\ACE-IT\projects\TabletConfiguration\test.ps1:9 char:10

+ $b.SaveAs <<<< ("C:\temp\Test.xls")

+ CategoryInfo : InvalidOperation: (SaveAs:String) [], RuntimeException

+ FullyQualifiedErrorId : InvokeMethodOnNull

re: How Can I Use Windows PowerShell to Automate Microsoft Excel?

shivkhare — Wed, 17 Mar 2010 13:09:57 GMT

I am running below code -

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Interop.Excel")

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Core")

[Microsoft.Office.Interop.Excel.Application]$xl = new-object Microsoft.Office.Interop.Excel.ApplicationClass

$file="D:\test\test.csv"

$xl.visible = $true

$wbk = $xl.Workbooks.Open("$file")

$wks = $wbk.worksheets.item(1)

$r1=[Microsoft.Office.Interop.Excel.IRange]$wks.Range("A1:D1")

$r2=[Microsoft.Office.Interop.Excel.IRange]$wks.Range("A2:D2")

#Commented code

#$mis = [System.Reflection.Missing]::Value;

#$xl.Union($r5, $r6, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis, $mis)

.............

It throws below error while I try to typecast _ComObject returned to IRange

Error message -

$r1=[Microsoft.Office.Interop.Excel.IRange]$wks.Range <<<< ("A1:D1")

+ CategoryInfo : NotSpecified: (:) [], RuntimeException

+ FullyQualifiedErrorId : RuntimeException

Typecasting is required otherwise the next command to union two range (commented code) fails with same error.

I am stuck on it. Please advice.

re: How Can I Use Windows PowerShell to Automate Microsoft Excel?

coderover — Fri, 26 Feb 2010 19:17:07 GMT

How can I use PowerShell to clean up Carriage Returns and slashes from an Excel spreadsheet before converting it to a CSV?

Perhaps using the clean function? or invoking something similar to CTRL+H and passing the CTRL+J carriage return representation?

Your assistance is greatly appreciated.