Excel Row Format

[dlisterjr]

Is there a way to search a spreadsheet and then change the font color of the
row if the cell contains a string? IE if a cell in the row contains the word
"closed" (it won't be the only word in the cell), I want the whole row font
color red.

[Oisin (x0n) Grehan [MVP]]

On Apr 1, 10:27 am, dlisterjr <dliste...@discussions.microsoft.com>
wrote:

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

[RichS]

If Excel is using anything but US English you may well see an error message.
There is code to get round it on my blog at

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

[dlisterjr]

The problem is that I need to search each row for data and then format that
row base on if the data exist. The macro really didn't help with that.

"Oisin (x0n) Grehan [MVP]" wrote:

[Oisin (x0n) Grehan [MVP]]

On Apr 1, 3:48 pm, dlisterjr <dliste...@discussions.microsoft.com>
wrote:

> > - Oisin- Hide quoted text -
>
> - Show quoted text -

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

[dlisterjr]

When I record the macro there is a find method, but that would only run once.
Recording a macro would work if I was trying to do the action once, but I
need to check each row of data for the string.

[sluice]

First define the worksheet you are working on. Then define a Range
object, as you would in VBA and walk through the range with a foreach
{$cell in $Range} loop. Each $cell in the loop will represent a cell
object in your Range, so if $cell.value="required",
$cell.Interior.ColorIndex=3 for red.

--
sluice

[Hal Rottenberg]

Oisin (x0n) Grehan [MVP] wrote:
>>> 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

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)

[Oisin (x0n) Grehan [MVP]]

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/