Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate
Photo

Basic Ahk_L COM Tutorial for Excel


  • Please log in to reply
227 replies to this topic
plastikglass
  • Members
  • 93 posts
  • Last active: Apr 26 2013 01:59 PM
  • Joined: 24 Aug 2011

If you're looking to find mutiple instances of a value in a list, you have to learn to use the Find method using Loop...Until. Here's an example to try, open only a blank, new workbook and run this code:

colors=3,43
xl :=	ComObj("Excel.Application"), xlRng :=	xl.Range["A1:A20"]
MsgBox, , Excel, First we will create a number sequence.
For c in xlRng
{
	Random, out, 1, 2
	c.Value :=	out
}
MsgBox, , Excel, Next we will search for the numbers and color them.
Loop, parse, colors, `,
{
	if	f :=	xlRng.Find[A_Index]	; if the number can be found in the list
	{
		first :=	f.Address	; save the address of the first found match
		Loop
			f.Interior.ColorIndex :=	A_LoopField, f :=	xlRng.FindNext[f]	; color that found cell and move to the next found cell
		Until	(f.Address = first)	; stop looking when we're back to the first found cell
	}
	MsgBox, , Excel, %	"All " A_Index "'s should now be " (A_Index=1 ? "red." : "green.")
}
MsgBox, , Excel, And that's a wrap.


This is a great example and introduces a lot of new tricks to me such as: Address, A_Loopfield, FindNext, etc.

I got around the original problem of looking for multiple instances by switching the ranges(B cells for c, A cells for f) in the original For loop; is this not a good idea, even though it worked this time around?

Another thing is this, what if you need to look for data that spans multiple cells? For example, A1 and its adjacent A2 cell have to be a matching pair in order for them to both be colored in. Is this even possible to do?

I guess that is related with my other question which was how to color in a row of cells for when the specific cell with the data is found.

Thanks for all your help!

sinkfaze
  • Moderators
  • 6367 posts
  • Last active: Nov 30 2018 08:50 PM
  • Joined: 18 Mar 2008

I got around the original problem of looking for multiple instances by switching the ranges(B cells for c, A cells for f) in the original For loop; is this not a good idea, even though it worked this time around?


I would have to see the code since I'm not quite sure what you mean.

Another thing is this, what if you need to look for data that spans multiple cells? For example, A1 and its adjacent A2 cell have to be a matching pair in order for them to both be colored in. Is this even possible to do?


The easiest way will be to use the Offset property while using the For-loop to go through one column of cells. Run the below code on a brand new blank workbook:

xl :=	ComObj("Excel.Application"), xlRng :=	xl.Range["A1:A50"]
MsgBox, , Excel, First we will seed the range A1:B50 with either a 0 or 1.
xl.ScreenUpdating :=	False
For c in xl.Range["A1:B50"]
{
	Random, n, 0, 1	; randomly get 0 or 1
	c.Formula :=	n
}
xl.ScreenUpdating :=	True
MsgBox, , Excel, Next we will check which cells in the same row of the range have the same value`nby looping through only the values in Column A.
xl.ScreenUpdating :=	False
For c in xlRng
	if	(c.Value=c.Offset(0,1).Value)
		xl.Range[c.Address ":" c.Offset(0,1).Address].Interior.ColorIndex :=	3
xl.ScreenUpdating :=	True
MsgBox, , Excel, El fìn.
return

And you need change only one line to highlight the entire row:

;xl.Range[c.Address ":" c.Offset(0,1).Address].Interior.ColorIndex :=	3
xl.Rows[c.Row].EntireRow.Interior.ColorIndex :=	3


plastikglass
  • Members
  • 93 posts
  • Last active: Apr 26 2013 01:59 PM
  • Joined: 24 Aug 2011

I got around the original problem of looking for multiple instances by switching the ranges(B cells for c, A cells for f) in the original For loop; is this not a good idea, even though it worked this time around?


I would have to see the code since I'm not quite sure what you mean.


Well, this is what I had before:

For c in xlSheet.Range["A1:A18"]]
   if   f :=   xlSheet2.Range["B1:B53"].Find[c.Value]
      (f.Interior.ColorIndex := 3) && (c.Interior.ColorIndex := 43)

That was causing only the first instance of the matching cell in the B column to be colored in. So then I just switched it around as such:

For c in xlSheet.Range["B1:B53"]
   if   f :=   xlSheet2.Range["A1:A18"].Find[c.Value]
      (f.Interior.ColorIndex := 3) && (c.Interior.ColorIndex := 43)

After I did this, I was able to get the same result as the one example you had just shown me.

Is this proper methodology, or would this open me up to more problems down the road? Thanks.

Furthermore:

I am so sorry, Sinkfaze; I feel like such an idiot! That example you showed me with the matching pairs of cells was a great answer for my question. The only problem is that I asked the question incorrectly :(

What I meant was how to find a pair of matching cells in one sheet when corroborating in another sheet.

For example, I tried this:

For c in xlSheet.Range["B1:C53"]
   if   f :=   xlSheet2.Range["A1:B18"].Find[c.Value]
      (f.Interior.ColorIndex := 3) && (c.Interior.ColorIndex := 43)

Obviously, it didn't work. It colored in almost every cell in the first sheet since all we were looking for was any cell that matched up with any individual cell in Range A1:B18; when I really wanted to make sure to find matching A1:B1, A2:B2, A3:B3, and so on in the second sheet with the first.

But your time was not wasted because that was still a really neat trick, and now we, the community, know how to fill in entire ranges. I hope I am not exhausting you, lol.

plastikglass
  • Members
  • 93 posts
  • Last active: Apr 26 2013 01:59 PM
  • Joined: 24 Aug 2011
Yes! Finally found a way to corroborate multiple cells from one sheet with another. This code finds two exactly matching cells from Sheet 2 in Sheet 1, and colors it in:

For c in xlSheet.Range["L2:M141"] 
   if  (xlSheet2.Range["A1:A94"].Find[c.Value]) && (xlSheet2.Range["B1:B94"].Find[c.Offset(0,1).Value])
      ((xlSheet.Range[c.Address ":" c.Offset(0,1).Address].Interior.ColorIndex := 43)) 

Although this colors in the matching two cells of sheet 1, I was not able to create code that fills in the matched cells in Sheet 2. This is the closest I have gotten:

For c in xlSheet.Range["L2:M141"] 
   if  (xlSheet2.Range["A1:A94"].Find[c.Value]) && (xlSheet2.Range["B1:B94"].Find[c.Offset(0,1).Value])
	f := xlSheet2.Range["A1:A94"].Find[c.Value]
      ((xlSheet.Range[c.Address ":" c.Offset(0,1).Address].Interior.ColorIndex := 43)) && (xlSheet2.Range[f.Address ":" f.Offset(0,1).Address].Interior.ColorIndex := 3)

The strangest thing happens with this code: on Sheet 1, only the last cell at the very bottom is filled in on Sheet 1. Even then, it fills in two adjacent cells, but it skipped one column over. So instead of filling in L141 and M141, it fills in M141 and N141. Why does it do that? The good news is that the corroborating data values have been filled in with the correct color on Sheet 2, but only just those cell values from Sheet 1 that were filled in as well (the same ones that skipped over a column).

trismarck
  • Members
  • 390 posts
  • Last active: Nov 25 2015 12:35 PM
  • Joined: 02 Dec 2010
Question - why is this code:
ControlGet, hwnd, hwnd, , Excel71, ahk_class XLMAIN 
window := Acc_ObjectFromWindow(hwnd, -16)
xlBook := window.parent
xlApp := window.application

xlSheet := window.activesheet
superior to:
xlApp := ComObjCreate("Excel.Application") ; COM Excel application handle

I've tested the code a little and Excel71 is the workbook control. If this is the case though, then why do we get the handle to the actual workbook by using the "parent" property? (I don't know what Acc is really :) )
xlBook := window.parent


sinkfaze
  • Moderators
  • 6367 posts
  • Last active: Nov 30 2018 08:50 PM
  • Joined: 18 Mar 2008
Your second one for purposes of comparison should actually be this:

xlApp := ComObj[color=red]Active[/color]("Excel.Application")

I wouldn't call it superior so much as safer; jethrow generally recommends that method because sometimes people haphazardly create invisible instances of Excel and in that case ComObjActive may not necessarily grab the instance you intend to and your script will appear to be broken when it's not.

jethrow
  • Moderators
  • 2854 posts
  • Last active: May 17 2017 01:57 AM
  • Joined: 24 May 2009

I've tested the code a little and Excel71 is the workbook control. If this is the case though, then why do we get the handle to the actual workbook by using the "parent" property?


A control is a Window, hence:
window := Acc_ObjectFromWindow(hwnd, -16)

... note:
ControlGet, hwnd, hwnd, , Excel71, ahk_class XLMAIN
MsgBox % ComObjType(Acc_ObjectFromWindow(hwnd, -16), "Name")



(I don't know what Acc is really happy.png )

Microsoft Active Accessibility (MSAA) is an Application Programming Interface (API) for user interface accessibility ... The programmatic goal of MSAA is to allow Windows controls to expose basic information, such as name, location on screen, or type of control, and state information such as visibility, enabled, or selected.

AccessibleObjectFromWindow[/url]":2hl61rd6]Parameters

hwnd [in]
...

dwObjectID [in]
... Specifies the object ID. This value is one of the standard object identifier constants or a custom object ID such as OBJID_NATIVEOM, which is the object ID for the Office native object model ...

Object Identifiers[/url]":2hl61rd6]OBJID_NATIVEOM [value = -16]
In response to this object identifier, third-party applications can expose their own object model. Third-party applications can return any COM interface in response to this object identifier.



trismarck
  • Members
  • 390 posts
  • Last active: Nov 25 2015 12:35 PM
  • Joined: 02 Dec 2010
Thanks for the responses.

I've tested the code a little and Excel71 is the workbook control. If this is the case though, then why do we get the handle to the actual workbook by using the "parent" property?

A control is a Window, hence:
[color=red]window [/color]:= Acc_ObjectFromWindow(hwnd, -16)
... note:
ControlGet, hwnd, hwnd, , Excel71, ahk_class XLMAIN 
MsgBox % ComObjType(Acc_ObjectFromWindow(hwnd, -16), "Name")

OK.
window ; (1)
   control1 ; this is the workbook control [window]
   control2
(1) Why do we get the parent window of the control [window] if we already know the workbook control [window]? :0 (why the parent window and not the control window itself)


dwObjectID [in]
... Specifies the object ID. This value is one of the standard object identifier constants or a custom object ID such as OBJID_NATIVEOM, which is the object ID for the Office native object model ...

OBJID_NATIVEOM [value = -16]
In response to this object identifier, third-party applications can expose their own object model. Third-party applications can return any COM interface in response to this object identifier.

Thanks for that as I couldn't find what -16 means.

I wouldn't call it superior so much as safer; jethrow generally recommends that method because sometimes people haphazardly create invisible instances of Excel and in that case ComObjActive may not necessarily grab the instance you intend to and your script will appear to be broken when it's not.


; always get the visible Excel window's control
[color=red]DetectHiddenWindows, Off[/color] ; this should be in the script also
ControlGet, hwnd, hwnd, , Excel71, ahk_class XLMAIN
window := Acc_ObjectFromWindow(hwnd, -16)
xlBook := window.parent
xlApp := window.application
xlSheet := window.activesheet

; get the COM handle for the Excel 'active application' object
; note that COM related active Excel Application window (COM context) can be
; different from the active Excel Window (Windows OS context) *
xlApp := ComObjActive("Excel.Application")


; * an example: (by jethrow)
Loop, 2 {
   XL%A_Index% := ComObjCreate("Excel.Application")
   XL%A_Index%.Visible := true
   XL%A_Index%.Workbooks.Add
   WinMove, % "ahk_id" XL%A_Index%.hwnd, , 0, % A_Index=1? 0:300, 300, 300
}

WinActivate, % "ahk_id" XL2.hwnd
MsgBox, The second Excel Window is now active. Calling ComObjActive ...
ComObjActive("Excel.Application").Range("A1").Value := "A1"


jethrow
  • Moderators
  • 2854 posts
  • Last active: May 17 2017 01:57 AM
  • Joined: 24 May 2009

(1) Why do we get the parent window of the control [window] ...


The window object's parent is the workbook object that is displayed in the window. I tried to use informative variable names:
ControlGet, hwnd, hwnd, , Excel71, ahk_class XLMAIN
window := Acc_ObjectFromWindow(hwnd, -16)
xlBook := window.parent
xlApp := window.application

xlSheet := window.activesheet


plastikglass
  • Members
  • 93 posts
  • Last active: Apr 26 2013 01:59 PM
  • Joined: 24 Aug 2011
Hey guys,

I just wanted to make sure and find out if we are still using the older ACC library to make all of this work. Has it not been included in AHK_L natively? Thanks.

jethrow
  • Moderators
  • 2854 posts
  • Last active: May 17 2017 01:57 AM
  • Joined: 24 May 2009
There was never any plans to Natively implement the Acc Functions into AHK_L, as far as I know.

plastikglass
  • Members
  • 93 posts
  • Last active: Apr 26 2013 01:59 PM
  • Joined: 24 Aug 2011

There was never any plans to Natively implement the Acc Functions into AHK_L, as far as I know.


Thanks for the quick reply, I just wanted to make sure because I ran into a bit of a jam when sharing the code with others because I didn't realize that it was wholly dependent on that library for it to work. Maybe future versions may include it?

amnesiac
  • Members
  • 124 posts
  • Last active: May 01 2014 03:04 AM
  • Joined: 07 Nov 2010
Good tutorial for new user. I translate it to the Chinese forum (the link). Thank you very much.

ComObjActive:
Code:
Xl := ComObjCreate("Excel.Application") ;handle

Here ComObjCreate may be a error.

Click to download Chinese resource for AutoHotkey.

Recommended: AutoHotkey_L My code is based on it or similar versions, e.g. AutoHotkey_H.
Together with AutoHotkey, we grow and march forward. No matter how the future will be, this period of days is still epic.


amnesiac
  • Members
  • 124 posts
  • Last active: May 01 2014 03:04 AM
  • Joined: 07 Nov 2010
It may be better that replaces the example which uses object to the following one:
; Set the A1-I1 cells' value to 1-9
while, (A_Index < 10)
{
    cell := Chr(A_Index + 64) . "1"
    Xl.Range(cell).value := A_Index
}

Click to download Chinese resource for AutoHotkey.

Recommended: AutoHotkey_L My code is based on it or similar versions, e.g. AutoHotkey_H.
Together with AutoHotkey, we grow and march forward. No matter how the future will be, this period of days is still epic.


a4u
  • Guests
  • Last active:
  • Joined: --
; Set the A1-I1 cells' value to 1-9

for cell in xl.Range("A1:I1")

   cell.value := A_Index