Jump to content

ObjGet/With/Excel highlighting


Recommended Posts

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

 

Link to comment
Share on other sites

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

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

This line

If $b >  "." & $percentage Then

does a string comparison.
Best would be

If $b >  $percentage/100 Then

 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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 by DrewSS
Link to comment
Share on other sites

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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 by 232showtime

ill get to that... i still need to learn and understand a lot of codes graduated.gif

Correct answer, learn to walk before you take on that marathon.

Link to comment
Share on other sites

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

 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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

ill get to that... i still need to learn and understand a lot of codes graduated.gif

Correct answer, learn to walk before you take on that marathon.

Link to comment
Share on other sites

:)
I hope you both got a raise from your boss ;)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...