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
Perfect, that's exactly what I was looking for! This thread is just getting better and better. Thank you!

As long as you know the tab's name or which tab it is in the order, you can select a specific sheet that way:

; if the tab "Ooga Booga" is the 3rd tab in a workbook
xlSheet :=	xlBook.Worksheets("Ooga Booga")
; or
xlSheet :=	xlBook.Worksheets(3)



PWCGuy
  • Members
  • 9 posts
  • Last active: Nov 10 2013 03:46 PM
  • Joined: 20 Dec 2010
I have been following this thread as I have a long standing issue with not getting connected to the correct active worksheet object when multiple excel spreadshseets are open using :

oExcel := ComObjActive("Excel.Application")


so I try'd this code:

ControlGet, hwnd, hwnd, , Excel71, ahk_class XLMAIN 
window := Acc_ObjectFromWindow(hwnd, -16) 
xlBook := window.parent 
xlApp := window.application 
xlSheet := window.activesheet

But the I get an error : "Call to nonexistent function." and the line number points to this line: "window := Acc_ObjectFromWindow(hwnd, -16)".

Where do I find this function to include ?

sinkfaze
  • Moderators
  • 6367 posts
  • Last active: Nov 30 2018 08:50 PM
  • Joined: 18 Mar 2008
It's in the Acc Library.

PWCGuy
  • Members
  • 9 posts
  • Last active: Nov 10 2013 03:46 PM
  • Joined: 20 Dec 2010
That works great !

Thanks sinfaze

plastikglass
  • Members
  • 93 posts
  • Last active: Apr 26 2013 01:59 PM
  • Joined: 24 Aug 2011
Hey, ran into a jam.

Question is two-parts.

Now that we can assign a specific sheet to an object, is there simply a way to just go to that sheet without changing anything?

I guess it makes more sense to ask, how can you find a value of a cell in sheet one in sheet two?

My original plan was to move to the second sheet, then Send CTRL+F, paste the value, and hit "Find".

Is there a specific code for that function as well, something convenient where we can just search up a specific variable in the sheet we want?

plastikglass
  • Members
  • 93 posts
  • Last active: Apr 26 2013 01:59 PM
  • Joined: 24 Aug 2011
Found this from another thread, thought it would be nice to have here:

Here is the code for AHK (Thanks to Maul.esel)

Code:

   _oXls := ComObjActive("Excel.Application") 
   _oExcel := _oXls.ActiveWorkbook.ActiveSheet   _oXls.FindFormat.Interior.ColorIndex := 4 ;Green
  _oXls.ReplaceFormat.Interior.ColorIndex := -4142 ;nothing
  _oXls.Selection.Replace(What := "OK", Replacement := "OK-", LookAt := 2, SearchOrder := 1,MatchCase := 1,MatchByte := True,  ComObjParameter(0xB, -1) , ComObjParameter(0xB, -1))


It's a bit difficult to make out and apply in our context. I will try to play around with it in the meantime.

sinkfaze
  • Moderators
  • 6367 posts
  • Last active: Nov 30 2018 08:50 PM
  • Joined: 18 Mar 2008
The easiest way will be to just create a pointer to each page, I do this quite frequently. The below is a same page example but you'll get the gist of how it relates to finding data in one place for another place. Before you run this make sure all instances of Excel are closed:

eh_hoser :=	["And a beer, in a tree","Two turtlenecks","Three French toast","Four pounds of back bacon","Five golden toques","Six packs of two-four","Seven packs of smokes","Eight comic books"]
xl :=	ComObjCreate("Excel.Application"), xl.Workbooks.Add, xl.Visible :=	True
MsgBox, First we create the base data set.
For c in xl.Range["A1:B8"]
	if	(c.Column=1)
		c.Value :=	c.Row
	else	c.Formula :=	eh_hoser[c.Row]
MsgBox, Next, we create a list to search for.
For c in xl.Range["E1:E8"]
{
	Random, out, 1, 8
	c.Value :=	out
}
MsgBox, Now, we match up each item in the list with its appropriate line.
For c in xl.Range["E1:E8"]
	if	f :=	xl.Range["A1:A8"].Find[c.Value]
		c.Offset(0,1).Formula :=	f.Offset(0,1).Formula


plastikglass
  • Members
  • 93 posts
  • Last active: Apr 26 2013 01:59 PM
  • Joined: 24 Aug 2011
Wow, it's amazing how AHK_L can go through each cell in a for loop just like that.

That is the first time I have seen an offset in action. So AHK_L can bump a cell from E1 to F1 just like that? How does it do that?

UPDATE: Nevermind that question, I realized that that is a special VBA function that allows it to work that well with Excel.

But thanks for the "Find" code; I am having a terribly difficult time finding the VBA code for a specific Excel operation and then using it with AHK_L, as you can see.

The easiest way will be to just create a pointer to each page, I do this quite frequently. The below is a same page example but you'll get the gist of how it relates to finding data in one place for another place. Before you run this make sure all instances of Excel are closed:

eh_hoser :=	["And a beer, in a tree","Two turtlenecks","Three French toast","Four pounds of back bacon","Five golden toques","Six packs of two-four","Seven packs of smokes","Eight comic books"]
xl :=	ComObjCreate("Excel.Application"), xl.Workbooks.Add, xl.Visible :=	True
MsgBox, First we create the base data set.
For c in xl.Range["A1:B8"]
	if	(c.Column=1)
		c.Value :=	c.Row
	else	c.Formula :=	eh_hoser[c.Row]
MsgBox, Next, we create a list to search for.
For c in xl.Range["E1:E8"]
{
	Random, out, 1, 8
	c.Value :=	out
}
MsgBox, Now, we match up each item in the list with its appropriate line.
For c in xl.Range["E1:E8"]
	if	f :=	xl.Range["A1:A8"].Find[c.Value]
		c.Offset(0,1).Formula :=	f.Offset(0,1).Formula



PWCGuy
  • Members
  • 9 posts
  • Last active: Nov 10 2013 03:46 PM
  • Joined: 20 Dec 2010
I have run into a strange situation were the modify time stamp on my Excel file is changing just beacuse it was opend via COM.
Result := ComObjError(0) 
oExcel := ComObjCreate("Excel.Application")  
oExcel.Workbooks.Open(User_File)

After running those fe lines the User_File modify date changes to the current time even if no other lcommends are executed. This was not happening previously but my company recently upgarded us to office 2010 and I think this has something to do with it. Has anyone else come across this issue ?

Mickers
  • Members
  • 1239 posts
  • Last active: Sep 25 2015 03:03 PM
  • Joined: 11 Oct 2010
Excel 2010, which I don't have much experience with, added a LOT more features. It's probably as much of a change from 2003 to 2007.

I don't have anything to test it with but have you checked the MSDN reference for the time stamp property?

PWCGuy
  • Members
  • 9 posts
  • Last active: Nov 10 2013 03:46 PM
  • Joined: 20 Dec 2010

Excel 2010, which I don't have much experience with, added a LOT more features. It's probably as much of a change from 2003 to 2007.

I don't have anything to test it with but have you checked the MSDN reference for the time stamp property?

Mickers, Thanks for the offer but after alot of testing the only reliable way I found to avoid 2010 from changing the file modify time when opening a .xls was to make a copy of the file first and then open the copy.

So a little more to the story .... based on the testing I did Excel 2010 changes the modify time on the file if is is .xls when it is opened via COM or by the front end application ( NOTE: this is not the case for .xlsx files ). If you then close the .xls file the modify date gets restored. However when using SaveAs the to a different name or type ( suchas .txt) then the modify time does not always get restored on the orginal file ( sometimes its does and other times it doesn't). For me the difference was dependent on the orginal file being located on a network drive verses local drive. For me it was important to retain the original modify time so I had to use the copy approach before opening.

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

So after some helpful advice from sinkfaze, I was able to get somewhere.

I was able to find values from one sheet, corroborate them with the values of another sheet, and then highlight the cell; great success!

But I also had a few hiccups along the way that I thought you guys could help out me with:

Although the corroborating value is found, it only fills it in once and does not the do the same for the duplicate values, why is this so?

A minor thing, but... once I find the cell of the corroborating value, how would I get AHK_L to highlight not just that specific cell, but its row as well. Such as, if the cell with the corroborating value found is "B4", how would I also highlight B3, B4, B5, B6, and so on?

Here is the code I used:

Settitlematchmode, 2


ControlGet, hwnd, hwnd, , Excel71, ahk_class XLMAIN
window := Acc_ObjectFromWindow(hwnd, -16)
xlBook := window.parent
xlApp := window.application
xlSheet := xlBook.Worksheets(1)
xlSheet2 :=   xlBook.Worksheets(2)


For c in xlSheet2.Range["C1:C43"]
   if   f :=   xlSheet.Range["B1:B141"].Find[c.Value]
      f.Interior.ColorIndex := 43

Thanks for the help guys!!

UPDATE: Okay, so I found out why it is doing that: it is checking that value in Sheet 2 only once, and then moves on to the next cell.

That's all, I haven't really solved the problem.

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

So I found out the answer to my question, at least the first part.

You don't know how much unnecessary crap I was trying before I got to the simple realization: I simply switched the for loop.

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

Now, the program goes through each cell in the first sheet and sees if it matches up with anything in the second sheet without skipping a beat. I feel stupid just knowing easy the solution really was, haha.

I still don't know how to fill in the row of that cell that was matched up; I can only fill in that single cell.

Mickers
  • Members
  • 1239 posts
  • Last active: Sep 25 2015 03:03 PM
  • Joined: 11 Oct 2010
@PWCGuy

I did a quick google search and found thispost. I'm not sure if it will help or not but it seems if you disable events the time stamp may not change.

Hopefully you'll read this as it's been a few days since your original post.

sinkfaze
  • Moderators
  • 6367 posts
  • Last active: Nov 30 2018 08:50 PM
  • Joined: 18 Mar 2008
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.