Skip to content
AutoHotKey
2011.08.02 17:03

[AHK_B&AHK_L] 엑셀 제어 비교.

조회 수 18090 추천 수 0 댓글 0
?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄
?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄

[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

 

원하는 엑셀문서파일 열기

FileSelectFile, Path
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

 

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. Smile

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
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:
Code (Copy):
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.
Code (Copy):
FileSelectFile, Path
Xl := ComObjCreate("Excel.Application")
Xl.Workbooks.Open(Path) ;open an existing file
Xl.Visible := True
ComObjActive:
Code (Copy):
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.
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
You can also set a variable to data inside a cell:
Code (Copy):
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:
Code (Copy):
while (Xl.Range("A" . A_Index).Value != "") {
Xl.Range("A" . A_Index).Value := value
}
And how to loop through a row:
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
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:
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)
2 Save Methods:
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
Sort sheet by column:
Code (Copy):
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. Smile
로그인 후 댓글쓰기가 가능합니다.

?

List of Articles
번호 분류 제목 날짜 조회 수
86 AutoHotKey 클릭해서 새창열리는 페이지에 클릭 또는 값설정 가능한가요? 2011.02.22 12625
85 회로도전자부품 주문한 부품 리스트 file 2013.10.26 19359
84 AutoHotKey 정보수집 2011.03.30 14133
83 컴퓨터잡담 인터넷 익스플러러 속도 향샹을 위한 팁 1 file 2012.02.25 22987
82 AutoHotKey 웹페이지의 내용을 변수에 넣기 2011.02.17 12335
81 [Docs]스프레드시트 스프레드시트 autohotkey html gmail 스마트폰 이용하여 핑로스 즉시 알림받기 file 2018.01.25 3462
80 AutoHotKey 부팅완료 메시지 프로그램 file 2011.12.17 10433
79 AutoHotKey 부팅 완료 체크 2011.02.09 15652
78 AutoHotKey [COM] 자바스크립트 / DOM / HTML 웹페이지 컨트롤 3 2011.02.12 22372
77 컴퓨터잡담 [Autohotkey] 인터넷 창을 여러개 띄우고 컨트롤 할때 ahk_id 알아내기 1 3 2009.12.19 17253
76 컴퓨터잡담 [autohotkey] 시스템 레지스트리 수정, 삭제 2010.08.14 6844
75 컴퓨터잡담 [autohotkey] 시스템 레지스트리 수정, 삭제 1 3 2010.08.14 6757
74 AutoHotKey [autohotkey] FTP-업로드 예제분석 2 file 2011.02.05 15757
73 AutoHotKey [AUTOHOTKEY] FTP 제어 file 2011.02.04 21045
72 AutoHotKey [AHK_L] 현재 열려진 인터넷 창 값 가져오기 4 2011.08.02 14406
71 AutoHotKey [ahk_l] 섬세한 인터넷 자동검색 2011.02.16 15990
70 AutoHotKey [ahk_l] 구글의 Gmail 자동로그인 소스 3 2011.02.11 19582
» AutoHotKey [AHK_B&AHK_L] 엑셀 제어 비교. 2 2011.08.02 18090
68 AutoHotKey [AHK_B&AHK_L] 익스플로러 HTML 문서정보 알아내기(IE HTML Element Spy) 2011.08.08 13441
67 AutoHotKey [ahk]웹페이지가 띄워진 창 내용을 추출하여 로딩이 완료되었는지를 확인할 수 있는 소스 2011.02.25 13128
Board Pagination Prev 1 2 3 4 5 Next
/ 5

http://urin79.com

우린친구블로그

sketchbook5, 스케치북5

sketchbook5, 스케치북5

나눔글꼴 설치 안내


이 PC에는 나눔글꼴이 설치되어 있지 않습니다.

이 사이트를 나눔글꼴로 보기 위해서는
나눔글꼴을 설치해야 합니다.

설치 취소