ObjGet/With/Excel highlighting

[DrewSS]

Hello,

 

Can someone please try to guide me to using ObjGet or With the correct way? I have a feeling I am doing something wrong.

I am trying to open an excel document and highlight certain cells based on their values and some calculations.

 

The following works fine to make these changes on a new workbook - but I have not been able to use With when opening an already existing outlook workbook; perhaps I'm opening it incorrectly.

$oExcel = ObjCreate("Excel.Application")
With $oExcel ; open new workbook
    .Visible = True
    .WorkBooks.Add
    .ActiveWorkbook.Sheets(1).Select()
EndWith
$oExcel.ActiveSheet.Range("A1").Interior.ColorIndex = 3

 

Here is an example of what I am trying to do (this does not work):

Local $oApplM = _Excel_Open()
Local $oExcelM = _Excel_BookOpen($oApplM, $morning_insights)
Local $aArrayM = _Excel_RangeRead($oExcelM, Default, $oExcelM.ActiveSheet.Usedrange.Columns("A:N"), 1, True)
$percentage = InputBox("Capping percentage", "Enter percentage to calculate")

For $a = 3 To UBound($aArrayM) -1
Local $b = $aArrayM[$a][1]
If $b >  "." & $percentage Then
$oExcelM.ActiveSheet.Range("B1").Interior.ColorIndex =3
EndIf
Next

 

[water]

  On 5/1/2015 at 5:12 PM, DrewSS said:

Here is an example of what I am trying to do (this does not work)

​What do you mean by "does not work"?

[water]

This line

If $b >  "." & $percentage Then

does a string comparison.
Best would be

If $b >  $percentage/100 Then

 

[DrewSS]

Hi water!

Sorry I should have been more specific.

 

The Interior.ColorIndex is not working when called with the second set of code, but it does work with when used with ObjCreate.

Local $oApplM = _Excel_Open()
Local $oExcelM = _Excel_BookOpen($oApplM, $report)
Local $aArrayM = _Excel_RangeRead($oExcelM, Default, $oExcelM.ActiveSheet.Usedrange.Columns("A:N"), 1, True)
With $oApplM.ActiveWorkbook.Sheets(1)
    .Range("A4").Interior.ColorIndex =3
EndWith

What is missing? It opens the document but does not change the color. I cant find where to find the list of the commands that With uses, (i.e. .Range, .Interior, etc...)

 

Your formula makes more sense also - i'll use that too! But my primary challenge is to highlight cells.

 

Edited May 1, 2015 by DrewSS

[water]

With is not needed in this case. It is used to improve performance because a part of the expression (e.g. "$oApplM.ActiveWorkbook.Sheets(1)") only needs to be evaluated one time.
Can't tell at the moment what is wrong with your example (no Excel here). Will test tomorrow and post the result.

Edited May 4, 2015 by water

[232showtime]

your script works fine with me, added some line to work in my side, I put random nos. in input box and ROWS COLOR CHANGED..

#include <Excel.au3> ;added line

Local $oApplM = _Excel_Open()
Local $morning_insights = "C:\CheckList.xlsx" ;added line
Local $oExcelM = _Excel_BookOpen($oApplM, $morning_insights)
Local $aArrayM = _Excel_RangeRead($oExcelM, Default, $oExcelM.ActiveSheet.Usedrange.Columns("A:N"), 1, True)
$percentage = InputBox("Capping percentage", "Enter percentage to calculate")

With $oApplM.ActiveWorkbook.Sheets(1)
    .Range("A4").Interior.ColorIndex =3
EndWith

 

Edited May 2, 2015 by 232showtime

[water]

This works fine for me and displays cell A4 in red:

#include <Excel.au3>
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookNew($oExcel)
$oWorkbook.Sheets(1).Range("A4").Interior.ColorIndex = 3

 

[water]

  On 5/2/2015 at 11:27 AM, 232showtime said:

Hi  just want to ask quick and simple question, where did you get this .Interior.ColorIndex and this ObjGet("", "Excel.Application"), is there a list fo excel Obj Classname/INstance like

ObjCreate("Excel.Application")

im totally confused, Im wondering where did you get those command

.Visible

.workbooks.add

.Close

​This can be found on MSDN (Microsoft Developer Network). Excel Developer Reference for Excel 2010.

[DrewSS]

  On 5/2/2015 at 9:28 AM, water said:

This works fine for me and displays cell A4 in red:

#include <Excel.au3>
$report = @ScriptDir & "\book1.xlsx"
Local $oApplM = _Excel_Open()
Local $oExcelM = _Excel_BookOpen($oApplM, $report) -edited
$oExcelM.Sheets(1).Range("A4").Interior.ColorIndex =3

 

 

This is working as intended -- very much appreciated!

Great source too: Excel Developer Reference for Excel 2010.

 

 

 

​Thanks again (and again.. and again...and again...) water!! :-D

So far with your help over the past year or two I've automated and saved many hundreds of hours!

 

 

And thank you 232showtime

 

[232showtime]

yeah water's UDF for msoffice is really great, i save time because of waters excel udf.. thank you water...

[water]

I hope you both got a raise from your boss