Whenever I see a post from Kiron I know it will be good.


This copies the 'sheetToCopy' sheet from a source workbook (Book1.xlsx) to a destination workbook (Book2.xlsx).
I've commented the steps but if you'd like a better description of the process let me know and I'll try my best...

$file1 = 'C:\Book1.xlsx'                            # source's fullpath
$file2 = 'C:\Book2.xlsx'                       # destination's fullpath
$xl = new-object -c excel.application
$xl.displayAlerts = $false                      # don't prompt the user
$wb1 = $xl.workbooks.open($file1, $null, $true) # open source, readonly
$wb2 = $xl.workbooks.open($file2)                         # open target
$sh1_wb2 = $wb2.sheets.item(1)    # first sheet in destination workbook
$sheetToCopy = $wb1.sheets.item('sheetToCopy')   # source sheet to copy
$sheetToCopy.copy($sh1_wb2) # copy source sheet to destination workbook
$wb1.close($false)                   # close source workbook w/o saving
$wb2.close($true)                 # close and save destination workbook
$xl.quit()
spps -n excel

--
Kiron

 

So someone hands you list of servers and they want to know everything there is to know about said servers by COB today.

Yet another fine example from Kiron on the Powershell discussion board.
http://groups.google.com/group/microsoft.public.windows.powershell/topics?hl=en


You can use the BorderAround() Method to set a border on a range:

# some Excel Constants used in the sample 

# for more Constants you can look here

# http://cid-c2db05eefa6c21a1.skydrive.live.com/browse.aspx/Public

# line styles
$xlLineStyleNone = -4142
$xlContinuous = 1
$xlDash = -4115

# line weight
$xlThin = 2
$xlMedium = -4138
$xlThick = 4

# color index
$xlColorIndexBlue = 5 # <-- depends on default palette

# border index
$xlEdgeLeft = 7
$xlEdgeTop = 8
$xlEdgeBottom = 9
$xlEdgeRight = 10

# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - #

$xl = new-object -c excel.application
$wb = $xl.workbooks.add()
$sh = $wb.worksheets.item('Sheet1')
$xl.visible = $true
$range1 = $sh.range('b2:d4')
$range2 = $sh.range('f2:h4')
$range3 = $sh.range('b6:d8')
$range4 = $sh.range('f6:h8')

# thin blue border on a $range1; [void] suppresses the method's output
[void]$range1.borderAround($xlContinuous,$xlThin,$xlColorIndexBlue)

# set a medium border on $range2 (automatic color index)
[void]$range2.borderAround($xlContinuous,$xlMedium)

# medium blue dash border on $range3
[void]$range3.borderAround($xlDash,$xlMedium,$xlColorIndexBlue)

# set a thick line border on $range4 (automatic color index)
[void]$range4.borderAround($xlContinuous,$xlThick)

# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - #

# to clear a range's borders, set its LineStyle to $xlLineStyleNone

# clear $range2's borders, quick but not the safest way because it'll
# also clear all borders on every cell within the range
$range2.borders.lineStyle = $xlLineStyleNone

# clear $range4's borders, safer, it clears specific borders one at
# a time
$xlEdgeLeft, $xlEdgeTop, $xlEdgeBottom, $xlEdgeRight | % {
 $range4.borders.item($_).lineStyle = $xlLineStyleNone

# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - #

# close and release resources
$wb.close($false)
$xl.quit()
spps -n excel

--
Kiron

If I get an error, Access denied or RPC service is unavailable, how
would I Print the error message out to  $sh.Cells.Item($row, 2)

And the answer is:

$cred = Get-Credential 'YourAdminName' #ad\name or name@domain

$row = 2

$xl = New-Object -c excel.application

$xl.visible = $true

$wb = $xl.workbooks.add()

$sh = $wb.sheets.item(1)

$strComputer = "dogbert"

$Var3 = gwmi -Class "Win32_OperatingSystem" -namespace "root\CIMV2" -computername $strComputer -cred $cred -ea continue

# the above is all one line

if ($Var3) {$sh.Cells.Item($row, 3) = $Var3.Caption }
Else
{ $sh.Cells.Item($row, 2) = $error[0].tostring() }

 

Alex, over at the Power Shell group in Google talked about Excel not shutting down when told.

Excel is generally the worst offender this way - there have been problems
with it not shutting down when told to that go back a very long time - but
there definitely are other ActiveX applications that exhibit this kind of
behavior. Since ActiveX applications are generally rare and each have their
own quirks, I usually test whether they shut down in different situations
when using them from PowerShell by keeping an eye on the process list, then
release the object if they don't shut down correctly.
Unfortunately, this isn't all that useful if you're keeping an application
around to use during a PowerShell session. In those cases, I've noticed that
even some of the better-behaved ActiveX applications don't work the way we
might want. For example, some of the better-behaved ActiveX applications
will indeed shut down automatically if they don't have an open, modified
document when you close PowerShell by typing exit, but will hang around if
you close PS with the "X" at the top of the shell window. My general rule of
thumb is to always unhide ActiveX applications and minimize them. This at
least ensures that I can see and close them if I exit PS the wrong way or
lose connection to the app somehow.

One way in Power Shell to kill the process is;

Stop-Process -name EXCEL

Another is to try to release the com object after quiting:

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

Or this;

$xl.quit()
spps -n excel

I am sure that there are others.