Jump to content
DrewSS

ObjGet/With/Excel highlighting

Recommended Posts

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

 

Share this post


Link to post
Share on other sites
water
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 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

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 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


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

Share this post


Link to post
Share on other sites
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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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 by 232showtime
  • Like 1

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.

Share this post


Link to post
Share on other sites
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

 

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

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.

  • Like 2

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
DrewSS

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

 

Share this post


Link to post
Share on other sites
232showtime

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.

Share this post


Link to post
Share on other sites
water

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

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
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

  • Similar Content

    • ajblandford
      By ajblandford
      I have embeded an Excel file in my autoit Script and want to use it as part of my GUI. I want to be able to select a range of cells and add the cell data to an edit box. Then dump that data to an array. I am using GUICtrlCreateObj to embed the spreadsheet. I cannot find any way to let my program see what cells are active so I can add that data to the edit box. 
       
      #include <WindowsConstants.au3> #include <GUIConstantsEx.au3> #include <excel.au3> $FileName = 'C:\VLog\book1.xlsx' If Not FileExists($FileName) Then MsgBox(0, "ERROR", "File not found") Exit EndIf ;Basic GUI $oExcelDoc = ObjGet($FileName) ; Get an excel Object from an existing filename If IsObj($oExcelDoc) Then $mainGUI = GUICreate("Production Room 2", 1800, 1200, 10, 10, $WS_MINIMIZEBOX + $WS_SYSMENU + $WS_CLIPCHILDREN) $GUI_ActiveX = GUICtrlCreateObj($oExcelDoc, 10, 70, @DesktopWidth - 250, @DesktopHeight - 260) $data = GUICtrlCreateEdit("", @DesktopWidth - 225, 75, 100) $btn = GUICtrlCreateButton( "GO", @DesktopWidth - 100, 75, 75) Else MsgBox(0, "", "failed") EndIf ;------------------ ;Turns off all command bars in excel to prevent user from making changes For $Bar In $oExcelDoc.CommandBars If $Bar.Enabled = True Then $Bar.Enabled = False If $Bar.Visible = True Then $Bar.Visible = False Next $oExcelDoc.Application.DisplayFormulaBar = False $oExcelDoc.Application.CommandBars("Shadow Settings").Visible = False $oExcelDoc.Application.DisplayScrollBars = True $oExcelDoc.Application.DisplayStatusBar = False GUISetState()  
    • Skeletor
      By Skeletor
      Hi Guys,
      Can anyone point me in the right direction. I'm trying to accomplished conditional formatting with arrows, but the code I have is wrong... 
      .Range("=$A3:$A4000").FormatConditions.Add = (xlCellValue, xlGreater, "=$A:$A") .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets I also tried it like this:
      .Range("=$A3:$A4000").FormatConditions _ .Add(xlCellValue, xlGreater, "=$A:$A") .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets  
    • Skeletor
      By Skeletor
      Hi Guys,
      How would you use _Excel_FilterSet to filter the excel sheet from largest number to smallest number?
      _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">20", 1, "<40") ; OR _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">", 1, "<") Excel Sheet attached. Trying to filter on the last column "I2"
      Inventory.xlsx
    • Skeletor
      By Skeletor
      Hi All,
      Has anyone come across this before?
      Code below:
      $SheetList = _Excel_SheetList($oWorkbook) _FileWriteFromArray("C:\" & $SheetListOutput, $SheetList, 1) Result:
      ResultABC| ResultDEF| ResultGHI| ResultJKL| ResultMNO| It created these "|" vertical bars?
    • Skeletor
      By Skeletor
      Hi All,

      I would like to know how you would take a FileLineRead and insert it into an array which then inserts it into Excel?
      One thing to know is the files content is broken up, so I only use half of the content within $FileRead1.
      So its imperative that the $value1, $value2, etc variables be used. 
      Code below:
      $FileRead1 = FileReadLine("C:\temp\sample.txt",1) For $count = 1 To _FileCountLines($FileRead1) Step 1 $string = FileReadLine($FileRead1, $count) $input = StringSplit($string, ",", 1) $value1 = $input[1] $value2 = $input[2] $value3 = $input[3] $value4 = $input[4] _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value1, "A1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value2, "B1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value3, "C1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value4, "D1") Next  
×