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.
On Apr 1, 10:27 am, dlisterjr <dliste...@discussions.microsoft.com> wrote:
> 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.
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.
> On Apr 1, 10:27 am, dlisterjr <dliste...@discussions.microsoft.com> > wrote: > > 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.
> 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.
> On Apr 1, 10:27 am, dlisterjr <dliste...@discussions.microsoft.com> > wrote: > > 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.
> 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.
> 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:
> > On Apr 1, 10:27 am, dlisterjr <dliste...@discussions.microsoft.com> > > wrote: > > > 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.
> > 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- 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:
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.
> On Apr 1, 3:48 pm, dlisterjr <dliste...@discussions.microsoft.com> > wrote: > > 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:
> > > On Apr 1, 10:27 am, dlisterjr <dliste...@discussions.microsoft.com> > > > wrote: > > > > 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.
> > > 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- 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:
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.
>>> 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.
> >>> 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.
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.
