最新WPS办公软件学习教程_免费企业办公软件下载_金山WPS Office官网论坛

 找回密码
 
查看: 7268|回复: 12

[VBA/VBS教程] 转:采用Autohotkey_L操作wps表格或Excel的Com接口的程序样例

[复制链接]

154

主题

27

听众

5098

积分

解答支持团员

Rank: 14Rank: 14Rank: 14Rank: 14

签到天数: 1 天

[LV.1]初来乍到

金币
2784
威望
11087
帖子
2620
精华
0

技术分享团 解答支持团 测试体验团 优秀会员奖 活跃会员奖 乐于助人奖 技巧教程分享达人

发表于 2014-6-27 10:59 |显示全部楼层
分享到: 新浪微博 腾讯微博
转载:6 O( R2 |: ^. h4 @' b
Basic Ahk_L COM Tutorial for Excel - Tutorials - AutoHotkey Community
! k  J+ f5 v9 I- H* l; G) d# @http://www.autohotkey.com/board/topic/69033-basic-ahk-l-com-tutorial-for-excel/
/ ^4 v% s, R5 C' T$ b9 [
  _$ E' H5 R. J% {! B9 @! [' s
I see a lot of questions in the forums on Excel sheets and how to pull orsend data to them. Although there are plenty of posts on the forum withexamples for almost any task, most of them written in basic, I thought it wouldbe a good idea to put as many as I have had personal experience with in oneplace.6 f2 v+ z3 @+ n1 t9 B

' b, d0 s, `9 w4 S9 D$ JI am by no means an expert at COM or Excel but Ido my best. 7 Z: |, U+ o5 a

) {- ]8 r3 q# k2 L* X) NQ: What is COM?
! k/ Q$ ~: c9 a: E% s9 C; X7 ~  BThe Component Object Model is a collection ofautomation objects that allows a user to interface with various methods andproperties of an application. % I& @5 j0 _( u' L! S4 J7 y8 F, x

- F) w9 j: J( C) `% V: h5 {$ pQ: How do I use it in my script?
! c" L) N% @0 aThere is no easy answer to this question. Why?Because there are different commands to every type of COM object. For instancethe methods for Internet Explorer are completely different from MS Office.
6 w8 x$ K% L$ q- x( D: j( c! s8 p1 u
In this tutorial I will focus on using COM toscript 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 tothe application. There are 2 ways to do this:
. u# A* l' K$ n5 e/ }
, e9 X1 N+ W' |4 o# |7 l
Xl := ComObjCreate("Excel.Application") ;createa handle to a new excel application
Xl := ComObjActive("Excel.Application");creates a handle to your currently active excel sheet
. T1 {0 p: ~/ |
Use one or the other depending on what kind of script you wish to puttogether. For example if I'm testing new excel code I like to use the activeCOM object so I don't have to wait on a new instance to open each time., Z. O% A3 x9 y3 S% Z8 F% w
: I5 \. O9 k. M8 Y, r6 K: B
Let's look at each type individually:
0 V3 _0 M' J/ N; K0 y+ ^! H/ ~2 ^ComObjCreate:
Xl := ComObjCreate("Excel.Application") ;handle
Xl.Visible := True ;by default excel sheets are invisible
Xl.Workbooks.Add ;add a new workbook

7 y$ B# k+ X" G6 y! o! d3 z* L
Simple by accessing the object does not give us anything to work with orsee. We need to create a workbook and make it visible to do anything. (don'task me why Microsoft made the default invisible...)
+ s) j& E2 {) \: N* k5 S1 I  T/ w: w) d( {
You can also have the user choose a document toopen.
FileSelectFile, Path
Xl := ComObjCreate("Excel.Application")
Xl.Workbooks.Open(Path) ;open an existing file
Xl.Visible := True
5 C& ]# k4 l, q# M- S
ComObjActive:
Xl := ComObjCreate("Excel.Application") ;handle
8 e5 a2 z( p  K
It's fairly simple to set up an active sheet since it's already visibleand the workbook is already open.* R1 _, M1 \' Y) m4 J8 l

" K8 ^" c1 B5 f- f2 E7 V. zSo far we have only retrieved a handle to a newsheet, a user specified sheet, or an active sheet. Now we can manipulate somedata!; V0 q# D# G+ d4 `3 G' s

% |% o1 h3 e7 ^9 S6 q, gThe most basic and useful way to use COM is toset individual cells in the sheet.
Xl.Range("A1").Value := "helloworld!" ;set cell 'A1' to a string
helloworld := "hello world!"
Xl.Range("A1").Value := helloworld ;set cell toa variable

* l5 d- ]: R1 J( k7 }
You can also set a variable to data inside a cell:
helloworld := Xl.Range("A1").Value
* E0 K; D/ S* O9 \1 D8 |
It's not too useful to set specific cells in a script especially in largefiles. 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
}
+ W0 e7 Z$ s1 j6 d( S- F$ C' a
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 eachcell in a row
5 g/ {6 I6 U3 \' b8 a- J
I'm not going to go into how object work. That's for another time.$ t% V- L* j5 j- y
However in this example i'm using an object tostore the column letters so i can loop through them. The 'key' is the positionin the object the string is located. 'value' is what is stored in thatposition. So columns[1] = "A", columns[2] = "B", ect.
9 w* r) G+ E" I+ G5 `  {
% n/ ^; B. W1 }4 M# D2 F1 VNow that you can use the basic's of excel let'slook at a few more specific
% B( r# l9 g  z8 t# ]( g! @7 ]% {' E
3 e3 ?, M; Y) g! ^' n' ZExamples:
Xl.Range("A:A").Copy ;copy cell to clipboard
Xl.Range("A:A").PasteSpecial(-4163) ;'-4163' isthe constant for values only
Xl.Range("A:A").NumberFormat := "@";change the column format to 'text'
Xl.CutCopyMode := False ;deselect cells (use this withcopy)

" @1 }9 [! ?! I/ x" K% B$ H
2 Save Methods:
XL.ActiveWorkbook.SaveAs(BookName) ;'bookname' is avariable with the path and name of the file you desire
Xl_Workbook := Xl.Workbooks.Open(Path) ;handle tospecific workbook
Xl_Workbook.Save() ;quick save already existing file
, y# r2 D& o7 W4 _* a% G: o! c
Sort sheet by column:
Xl.Range("A1:Q100").Sort(Xl.Columns(1), 1);sort sheet by data in the 'a' column
5 l  C& k5 ~% F! S
That about completes this simpletutorial.
. g$ J- ~9 O! b: D( d6 R5 l/ ^+ w
" o' U& ^) o: D' aIf you are interested in learning how to doother things with Excel the easiest way is to use the macro button in the Viewtab on any 2007 Excel sheet. This handy feature records your actions withinexcel and then see the VBA (Visual Basic for Applications) code which is fairlyeasy to translate to autohotkey. Or you can post it in this topic for help.
* y+ ^! a7 f, C
: D: u) |; \( M" G4 s2 O3 ASpecial Thanks:
- \  J" f/ G7 _7 U6 b+ \a4u
) g+ K3 |7 Q( l2 m7 vSinkfaze
0 m% i/ O6 u. G7 m" w  Fjethrow
, l! l8 @# r+ |) i/ SFor helping a hard headed ninCOMpoop withsome difficult code. :wink:

53

主题

60

听众

6715

积分

版主

Rank: 20Rank: 20Rank: 20Rank: 20Rank: 20

签到天数: 126 天

[LV.7]常住居民III

金币
634
威望
13773
帖子
3973
精华
2

WPS粉丝团 技术分享团 乐于助人奖 技巧教程分享达人 优秀会员奖 活跃会员奖 测试体验团 版主勋章 勤奋版主奖 最佳教程奖 测试体验官 优秀模板奖

发表于 2014-6-28 14:06 |显示全部楼层
虽然不明白,但是也要支持

点击了解最新动态:【轩少】__实用教程索引(2015-9-6更新)
http://bbs.wps.cn/forum.php?mod= ... amp;fromuid=2404273
回复

使用道具 举报

9

主题

22

听众

1万

积分

测试体验团员

Rank: 14Rank: 14Rank: 14Rank: 14

签到天数: 1172 天

[LV.10]以坛为家III

金币
2144
威望
26426
帖子
6176
精华
0

优秀会员奖 活跃会员奖 乐于助人奖 测试体验团

发表于 2014-6-28 15:03 |显示全部楼层

! d8 p: b% f0 w$ e* M- f9 l9 r好帖子,来支持了!
回复

使用道具 举报

1

主题

23

听众

8235

积分

LV.15

Rank: 15Rank: 15Rank: 15Rank: 15Rank: 15

签到天数: 1192 天

[LV.10]以坛为家III

金币
1821
威望
22302
帖子
2304
精华
0

WPS粉丝团 优秀会员奖

发表于 2014-7-2 08:26 |显示全部楼层
这是神马仪式
回复

使用道具 举报

1

主题

0

听众

15

积分

LV.1

Rank: 1

签到天数: 1 天

[LV.1]初来乍到

金币
1
威望
30
帖子
11
精华
0
发表于 2014-8-17 15:45 |显示全部楼层
好帖子,来支持了!好帖子,来支持了!
回复

使用道具 举报

4

主题

1

听众

416

积分

LV.5

Rank: 5Rank: 5

签到天数: 1 天

[LV.1]初来乍到

金币
13
威望
700
帖子
384
精华
0
发表于 2015-6-22 16:06 |显示全部楼层

( T! `) l9 f) s% O我是来打酱油的
回复

使用道具 举报

0

主题

0

听众

-2

积分

LV.0

该用户从未签到

金币
3
威望
-3
帖子
0
精华
0
发表于 2016-3-10 17:36 |显示全部楼层
WPS的干嘛也要用英文
回复

使用道具 举报

3

主题

0

听众

146

积分

LV.3

Rank: 3Rank: 3

签到天数: 24 天

[LV.4]偶尔看看III

金币
26
威望
332
帖子
70
精华
0
发表于 2016-3-10 19:51 |显示全部楼层
打酱油的~~
回复

使用道具 举报

15

主题

28

听众

1万

积分

活动策划团员

Rank: 14Rank: 14Rank: 14Rank: 14

签到天数: 1211 天

[LV.10]以坛为家III

金币
3598
威望
40663
帖子
11277
精华
0

WPS粉丝团 活跃会员奖 活动策划团 测试体验团 重阳节勋章 优秀会员奖

发表于 2016-3-13 08:39 |显示全部楼层

8 g+ k1 y$ t0 I来学习教程!支持!
回复

使用道具 举报

112

主题

18

听众

2万

积分

LV.18

Rank: 18Rank: 18Rank: 18Rank: 18Rank: 18

签到天数: 1206 天

[LV.10]以坛为家III

金币
2099
威望
67811
帖子
4131
精华
0

优秀会员奖 活跃会员奖

发表于 2017-12-3 05:50 |显示全部楼层
都是英文                       
回复

使用道具 举报

*滑动验证:
您需要登录后才可以回帖 登录 | 更多账号登录:

快速回帖:

fastpost

WPS论坛更新日志|WPS Office官方论坛     

GMT+8, 2017-12-15 14:16

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部