Hi,
Yes, this should be possible in PowerShell - I don't have the facility
to test it here, but when usually when I need to do something like
this, I record a macro in Excel of the actions I want to automate,
then I view the VBA code in the editor IDE and translate/adapt it to
powershell. You'll need an entry point into the Excel object model,
which starts with:
$excel = new-object -com excel.application
..and should end with:
$excel.Quit()
If you're still stuck on this later, drop a note back here and I'll
help you finish it off.
- Oisin
http://richardsiddaway.spaces.live.com/blog/cns!43CFA46A74CF3E96!290.entry
--
Richard Siddaway
Please note that all scripts are supplied "as is" and with no warranty
Blog: http://richardsiddaway.spaces.live.com/
PowerShell User Group: http://www.get-psuguk.org.uk
"Oisin (x0n) Grehan [MVP]" wrote:
I'm not sure I understand what you mean when you say "the macro didn't
really help with that?" Did you record a macro of the actions you need
to do in order to learn which parts of the object model you need to
use?
First, open a sample spreadsheet in Excel, then:
1) Start recording a Macro
2) CTRL+F search for your value
3) Stop recording the Macro
4) Examine the VBA code that was generated for searching the Excel
sheet for a value by opening the VB editor in the macros menu and
examining the Macro that you just recorded.
5) Adapt for PowerShell.
Now, repeat the same actions for selecting a row in your spreadsheet
and changing the background colour. If you need to know how to load a
spreadsheet into Excel, you can record a macro for that too and
examine the VBA code for hints.
So now, you know which properties and methods to call on the Excel
object model in order to a) find a value, and b) change the background
colour of a row. It remains to use the $excel object above to perform
the actions you need.
Additionally, PowerShell lets you examine methods on any object by
using the following syntax:
ps> $excel = new-object -com excel.application
ps> $excel | get-member
TypeName: Microsoft.Office.Interop.Excel.ApplicationClass
Name MemberType Definition
---- ---------- ----------
ActivateMicrosoftApp Method System.Void
ActivateMicrosoftApp(XlMSApplication Index)
AddChartAutoFormat Method System.Void AddChartAutoFormat(Object
Chart, String Name, Object Description)
AddCustomList Method System.Void AddCustomList(Object
ListArray, Object ByRow)
Nobody said it would be easy! :-) If you're still completely confused
by this, let me know.
- Oisin
--
sluice
Awesome tip, Oisin! We're going to cover it in an upcoming podcast episode.
--
Hal Rottenberg
Blog: http://halr9000.com
Webmaster, Psi (http://psi-im.org)
Co-host, PowerScripting Podcast (http://powerscripting.net)
Cool, and don't forget to mention the new VSTO PowerTools 1.0 that
include a really nice extension library to the shipping Office Interop
shims:
Office Interop API Extensions
A set of C# classes for handling parameterized properties and
optional/named parameters, as well as for LINQ-enabling Office
collection objects.
http://blogs.msdn.com/andreww/archive/2008/02/21/vsto-vsta-power-tools-v1-0.aspx
It might make interop a little bit easier in PowerShell too.
- Oisin
PowerShell MVP
http://www.nivot.org/