[AHK_B&AHK_L] 엑셀 제어 비교.
Basic Ahk_L COM Tutorial for Excel
엑셀 새문서 및 시트 열기
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
엑셀 새문서 열기 & 워크시트 추가.
Xl := ComObjCreate("Excel.Application") ;handle
Xl.Visible := True ;by default excel sheets are invisible
Xl.Workbooks.Add ;add a new workbook
Xl.Visible := True ;by default excel sheets are invisible
Xl.Workbooks.Add ;add a new workbook
원하는 엑셀문서파일 열기
FileSelectFile, Path
Xl := ComObjCreate("Excel.Application")
Xl.Workbooks.Open(Path) ;open an existing file
Xl.Visible := True
Xl := ComObjCreate("Excel.Application")
Xl.Workbooks.Open(Path) ;open an existing file
Xl.Visible := True
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
helloworld := "hello world!"
Xl.Range("A1").Value := helloworld ;set cell to a variable
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:
| Code (Copy): |
|
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 |
Let's look at each type individually:
ComObjCreate:
| Code (Copy): |
|
Xl := ComObjCreate("Excel.Application") ;handle Xl.Visible := True ;by default excel sheets are invisible Xl.Workbooks.Add ;add a new workbook |
You can also have the user choose a document to open.
| Code (Copy): |
|
FileSelectFile, Path Xl := ComObjCreate("Excel.Application") Xl.Workbooks.Open(Path) ;open an existing file Xl.Visible := True |
| Code (Copy): |
|
Xl := ComObjCreate("Excel.Application") ;handle |
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.
| Code (Copy): |
|
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 |
| Code (Copy): |
|
helloworld := Xl.Range("A1").Value |
| Code (Copy): |
|
while (Xl.Range("A" . A_Index).Value != "") { Xl.Range("A" . A_Index).Value := value } |
| Code (Copy): |
|
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 |
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:
| Code (Copy): |
|
Xl.Range("A:A").Copy ;copy cell to clipboard |
| Code (Copy): |
|
Xl.Range("A:A").PasteSpecial(-4163) ;'-4163' is the constant for values only |
| Code (Copy): |
|
Xl.Range("A:A").NumberFormat := "@" ;change the column format to 'text' |
| Code (Copy): |
|
Xl.CutCopyMode := False ;deselect cells (use this with copy) |
| Code (Copy): |
|
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 |
| Code (Copy): |
|
Xl.Range("A1:Q100").Sort(Xl.Columns(1), 1) ;sort sheet by data in the 'a' column |
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.