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
Mickers
  • Members
  • 1239 posts
  • Last active: Sep 25 2015 03:03 PM
  • Joined: 11 Oct 2010
I see a lot of questions in the forums on Excel sheets and how to pull or send data to them. Although there are plenty of posts on the forum with examples for almost any task, most of them written in basic, I thought it would be a good idea to put as many as I have had personal experience with in one place.

I am by no means an expert at COM or Excel but I do my best. :)

Q: What is COM?
The Component Object Model is a collection of automation objects that allows a user to interface with various methods and properties of an application.

Q: How do I use it in my script?
There is no easy answer to this question. Why? Because there are different commands to every type of COM object. For instance the methods for Internet Explorer are completely different from MS Office.

In this tutorial I will focus on using COM to script simple commands that will be used to automate Microsoft Office Excel. Before you can do anything with the Excel DOM you have to create a handle to the application. There are 2 ways to do this:
Xl := ComObjCreate("Excel.Application") ;create a handle to a new excel application
Xl := ComObjActive("Excel.Application") ;creates a handle to your currently active excel sheet
Use one or the other depending on what kind of script you wish to put together. For example if I'm testing new excel code I like to use the active COM object so I don't have to wait on a new instance to open each time.

Let's look at each type individually:
ComObjCreate:
Xl := ComObjCreate("Excel.Application") ;handle
Xl.Visible := True ;by default excel sheets are invisible
Xl.Workbooks.Add ;add a new workbook
Simple by accessing the object does not give us anything to work with or see. We need to create a workbook and make it visible to do anything. (don't ask me why Microsoft made the default invisible...)

You can also have the user choose a document to open.
FileSelectFile, Path
Xl := ComObjCreate("Excel.Application")
Xl.Workbooks.Open(Path) ;open an existing file
Xl.Visible := True
ComObjActive:
Xl := ComObjCreate("Excel.Application") ;handle
It's fairly simple to set up an active sheet since it's already visible and the workbook is already open.

So far we have only retrieved a handle to a new sheet, a user specified sheet, or an active sheet. Now we can manipulate some data!

The most basic and useful way to use COM is to set individual cells in the sheet.
Xl.Range("A1").Value := "hello world!" ;set cell 'A1' to a string
helloworld := "hello world!"
Xl.Range("A1").Value := helloworld ;set cell to a variable
You can also set a variable to data inside a cell:
helloworld := Xl.Range("A1").Value
It's not too useful to set specific cells in a script especially in large files. Here's how to loop through the cells in a column:
while (Xl.Range("A" . A_Index).Value != "") {
Xl.Range("A" . A_Index).Value := value
}
And how to loop through a row:
Row := "1"
Columns := Object(1,"A",2,"B",3,"C",4,"D",5,"E",6,"F",7,"G",8,"H",9,"I",10,"J",11,"K",12,"L",13,"M",14,"N",15,"O",16,"P",17,"Q") ;array of column letters
For Key, Value In Columns
XL.Range(Value . Row).Value := value ;set values of each cell in a row
I'm not going to go into how object work. That's for another time.
However in this example i'm using an object to store the column letters so i can loop through them. The 'key' is the position in the object the string is located. 'value' is what is stored in that position. So columns[1] = "A", columns[2] = "B", ect.

Now that you can use the basic's of excel let's look at a few more specific

Examples:
Xl.Range("A:A").Copy ;copy cell to clipboard
Xl.Range("A:A").PasteSpecial(-4163) ;'-4163' is the constant for values only
Xl.Range("A:A").NumberFormat := "@" ;change the column format to 'text'
Xl.CutCopyMode := False ;deselect cells (use this with copy)
2 Save Methods:
XL.ActiveWorkbook.SaveAs(BookName) ;'bookname' is a variable with the path and name of the file you desire
Xl_Workbook := Xl.Workbooks.Open(Path) ;handle to specific workbook
Xl_Workbook.Save() ;quick save already existing file
Sort sheet by column:
Xl.Range("A1:Q100").Sort(Xl.Columns(1), 1) ;sort sheet by data in the 'a' column
That about completes this simple tutorial.

If you are interested in learning how to do other things with Excel the easiest way is to use the Macro button in the View tab on any 2007 Excel sheet. This handy feature records your actions within excel and then see the VBA (Visual Basic for Applications) code which is fairly easy to translate to autohotkey. Or you can post it in this topic for help. :)

Special Thanks:
a4u
Sinkfaze
jethrow
For helping a hard headed ninCOMpoop with some difficult code. :wink:

Mickers
  • Members
  • 1239 posts
  • Last active: Sep 25 2015 03:03 PM
  • Joined: 11 Oct 2010
Reserved. :wink:

jethrow
  • Moderators
  • 2854 posts
  • Last active: May 17 2017 01:57 AM
  • Joined: 24 May 2009
Yes - an Excel Com Tutorial. I had a similar idea...

Xl := ComObjActive("Excel.Application") ;creates a handle to your currently active excel sheet

It gets a handle to the Application object, not the sheet object. Also, how do you define active? Actually, it would be the first excel application object/process registered on the Running Object Table. That's why I use the following:
ControlGet, hwnd, hwnd, , Excel71, ahk_class XLMAIN
window := Acc_ObjectFromWindow(hwnd, -16)
xlBook := window.parent
xlApp := window.application
xlSheet := window.activesheet
However, this will cause a Com Error if a cell is being modified. Also, just a thought ...
Columns := Object(1,"A",2,"B",3,"C",4,"D",5,"E",6,"F",7,"G",8,"H",9,"I",10,"J",11,"K",12,"L",13,"M",14,"N",15,"O",16,"P",17,"Q") ;array of column letters 
Columns := ["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q"] ;array of column letters
Columns := SplitStr("ABCDEFGHIJKLMNOPQ") ;array of column letters


SplitStr(str, delim="", omit="") {
	array := []
	Loop, Parse, str, %delim%, %omit%
		array.insert(A_LoopField)
	return, array
}


Mickers
  • Members
  • 1239 posts
  • Last active: Sep 25 2015 03:03 PM
  • Joined: 11 Oct 2010
ControlGet, hwnd, hwnd, , Excel71, ahk_class XLMAIN
window := Acc_ObjectFromWindow(hwnd, -16)
xlBook := window.parent
xlApp := window.application
xlSheet := window.activesheet
Where's the Like button? :lol:

I define active as the last sheet the user brought to the foreground. The reason is that if i do a simple test with 2 excel sheets:
test=1
Xl := ComObjActive("Excel.Application")
t::
XL.Range("A1").Value := test
test++
return
If I jump between the sheets it will update cell A1 in the sheet in the foreground no matter which sheet I started with.

I would have thought that this would return a pointer to a single worksheet that happend to be active when the script was launched. Months ago I used a similer piece of code tank provided to the one you just posted to get a handle to 1 active sheet.

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

I define active as the last sheet the user brought to the foreground.

This will work best if there is no pre-existing EXCEL processes running:
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"
... this can be very puzzling if the Active Excel object isn't even visible.

... return a pointer to a single worksheet ...

MsgBox, % ComObjType(ComObjActive("Excel.Application"), "Name")


gregariousgreg
  • Members
  • 4 posts
  • Last active: Aug 12 2011 02:58 PM
  • Joined: 15 Jul 2011
Hey, so I have very basic TRUE BASIC programming knowledge and not much else, and I have not really got around to learning the ins and outs of autohotkey.

I think I understand how to copy data from specific cells to the clipboard, but I was wondering how I would go about pasting that data in a web browser. Specifically, I need to copy url's from and excel sheet and paste them in individual browser tabs. Does that make sense? If so, does anyone know how to go about that?

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

Hey, so I have very basic TRUE BASIC programming knowledge and not much else, and I have not really got around to learning the ins and outs of autohotkey.

I think I understand how to copy data from specific cells to the clipboard, but I was wondering how I would go about pasting that data in a web browser. Specifically, I need to copy url's from and excel sheet and paste them in individual browser tabs. Does that make sense? If so, does anyone know how to go about that?

Yes I do that everyday here at the office. It's how I got my start with COM for IE and Excel almost 8 months ago.

What you need to do is download a tool off the forum such as iWeb Browser2 Learner that can show you the form names in IE. Once you have those all you need to do is "cell x,y -> form z" in a manner of speaking. Once you get the tool and establish some form names post them here and there are plenty of people that will be willing to help you peice what you need together. :wink:

JDK
  • Members
  • 8 posts
  • Last active: Oct 12 2011 08:15 AM
  • Joined: 07 Aug 2011
as title

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

Good stuff, good tutorial.

I just had a question on the CutCopyMode.

When I utilize it in this manner:

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



xlSheet.Range("A:A").NumberFormat := "@"

while (xlSheet.Range("A" . A_Index).Value != "") 
{
xlSheet.Range("A" . A_Index).Value := "0268"
}



Row := "5"
Columns := Object(1,"A",2,"B",3,"C",4,"D",5,"E",6,"F",7,"G",8,"H",9,"I",10,"J",11,"K",12,"L",13,"M",14,"N",15,"O",16,"P",17,"Q")
For Keys, Value In Columns
XlSheet.Range(Value . Row).Value := "hello!"

xlSheet.CutCopyMode := False

xlSheet.Range("A:A").Copy


xlSheet.Range("C:C").PasteSpecial(-4163)

I am hit with an Error: 0x80020006 - Unknown name.

I know that using the ControlGet() would cause a COM error if the cell is being modified, but is that what's going on here? Maybe I am using it incorrectly? I tried using it after .PasteSpecial with the same end result.

The CutCopyMode := False is used to deselect cells so you can prepare to select another cell right?

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

I just had a question on the CutCopyMode.

It's a member of the Application object, not the Sheet object - hence the Com Error.

I know that using the ControlGet() would cause a COM error if the cell is being modified

ControlGet is a command, and it does not use Com - so it wouldn't cause a Com Error.

The CutCopyMode := False is used to deselect cells so you can prepare to select another cell right?

That will take Excel out of CutCopyMode - ie, when you copy a cell, it has running-ants around it to show you it is in CutCopyMode.

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

It's a member of the Application object, not the Sheet object - hence the Com Error.


Ohhh, okay. Makes sense now. So is there a certain advantage to creating three separate objects between the sheet, application, and workbook? For example, what are some of things I could between them?

ControlGet is a command, and it does not use Com - so it wouldn't cause a Com Error.


My bad, I guess I misunderstood the original author when he came up with this solution, something about a crash occurring if a cell was being modified. Thanks for the clearup!

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

So is there a certain advantage to creating three separate objects between the sheet, application, and workbook?


It allows specific operations within the program to be exclusive to a certain object in the program, which also acts as a safeguard. For example, certain manipulations need to be done only to the application object, so it doesn't make sense to access them (or allow access to them) through an unrelated portion of the program like the Workbooks or Worksheets objects. Here's a page directed towards other developers but it articulates these ideas well enough for others to learn from them (as well as learning the four primary objects: Application, Workbooks, Worksheets and Range).

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

It allows specific operations within the program to be exclusive to a certain object in the program, which also acts as a safeguard. For example, certain manipulations need to be done only to the application object, so it doesn't make sense to access them (or allow access to them) through an unrelated portion of the program like the Workbooks or Worksheets objects. Here's a page directed towards other developers but it articulates these ideas well enough for others to learn from them (as well as learning the four primary objects: Application, Workbooks, Worksheets and Range).


Okay, that makes sense. I guess it was a good idea to get into some C# beforehand. Can be useful with COM and AHK. So basically we just take VB code and translate it to work with AHK right?

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

VBScript (Visual Basic Scripting Edition) is an Active Scripting language developed by Microsoft that is modeled on Visual Basic. It is designed as a “lightweight” language with a fast interpreter for use in a wide variety of Microsoft environments. VBScript uses the Component Object Model to access elements of the environment within which it is running; for example, the FileSystemObject (FSO) is used to create, read, update and delete files.

So basically we just take VB code and translate it to work with AHK right?

That's an effective way to look at it for working with COM in AHK.

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

So basically we just take VB code and translate it to work with AHK right?


We just take VBA code and translate it to work with AHK. The differences between VB and VBA are often very subtle, but they will be enough to cause you trouble in certain spots if you don't keep the distinction in mind.