Excel VBA宏精简二.docx

Excel VBA宏精简二.docx

《Excel VBA宏精简二.docx》由会员分享，可在线阅读，更多相关《Excel VBA宏精简二.docx（10页珍藏版）》请在冰豆网上搜索。

ExcelVBA宏精简二

ExcelVBA（宏）精简

（二）

文件的操作

1）Excel文件

1.1新建与打开 

一.新建Workbooks.Add

二.打开

Workbooks.Open"路径"&"文件名.xls"

三.文件打开对话框的使用

Application.GetOpenFilename（fileFilter:

="Excelfiles（*.xls）,*.xls,Allfiles（*.*）,*.*"）

示例:

flag=FalseDoWhileNotflag'对话框打开已有Excel文件fm=Application.GetOpenFilename（fileFilter:

="Excelfiles（*.xls）,*.xls,Allfiles（*.*）,*.*"）

IffmFalseThenWorkbooks.OpenfmSetbb=ActiveWorkbook'把bb变量设为当前活动工作簿对象,打开一工作簿时,该工作簿自动为当前活动工作簿flag=TrueEndIfLoop

1.2保存与关闭 

一.保存Workbooks（"文件.xls"）.Save

二.另存对话框的使用

Application.GetSaveAsFilename（fileFilter:

="Excelfiles（*.xls）,*.xls,Allfiles（*.*）,*.*"）

示例:

flag=FalseDoWhileNotflag'循环要求必须输入文件名或选择文件名fm=Application.GetSaveAsFilename（fileFilter:

="Excelfiles（*.xls）,*.xls,Allfiles（*.*）,*.*"）

IffmFalseThenactiveworkbook.SaveAsfm'当前活动工作簿另存flag=TrueEndIfLoop

三.关闭

1.单个文件关闭Workbooks（"filename.xls"）.Close2.所有该Excel程序打开的文件都关闭Workbooks.Close

如果文件使用中改动过内容,那么该命令使用时会弹出提示对话框,询问是否保存.

如果要想不弹出提示对话框,可以使用第三或第四种文件关闭方法,或者如下示例.

示例:

关闭工作簿"Book1.xls",但不提示用户保存所作修订."Book1.xls"中的所有修订都不 会保存.Application.DisplayAlerts=False'信息警告关闭

Workbooks（"BOOK1.XLS"）.Close

Application.DisplayAlerts=True'信息警告开启

四.关闭同时保存

Workbooks（"filename.xls"）.Closesavechanges:

=True

五.关闭同时不保存

Workbooks（"filename.xls"）.Closesavechanges:

=False

六.Excel程序的关闭Application.Quit

说明:

使用本方法时,如果有未保存的工作簿处于打开状态,则MicrosoftExcel将弹出一个对话 框,询问是否要保存所作修改.

为防止这一情况出现,可在使用Quit方法前保存所有的工作簿或将DisplayAlerts属性设 置为False.如果该属性为False,则MicrosoftExcel退出时,即使存在未保存的工作簿 退出,也不会显示对话框,而且不保存就退出.如果将一个工作簿的Saved属性设置为True,但是并没有将其保存到磁盘上,则MicrosoftExcel在退出时不会提示保存该工作簿.

七.工作簿的密码保护与撤销 保护工作簿使其不至被修改.Workbooks（"工作簿名"）.Protect（Password,Structure,Windows）

PasswordVariant类型,可选.为一个字符串,该字符串为工作表或工作簿指定区分大小写的密码.如 果省略本参数,不用密码就可以取消对该工作表或工作簿的保护.否则,必须指定密码,通过密码来取消 对该工作表或工作簿的保护.如果忘记了密码,就无法取消对该工作表或工作簿的保护.最好在安全的地 方保存一份密码及其对应文档名的列表.StructureVariant类型,可选.如果为True,则保护工作簿结构（工作表的相对位置）.默认值为False.

WindowsVariant类型,可选.如果为True,则保护工作簿窗口.如果省略本参数,则窗口不受保护 如:

Workbooks（"学生档案"）.protect"1234"

Workbooks（"学生档案"）.Unprotect（Password）

PasswordVariant类型,可选.指定用于解除工作表或工作簿的保护的密码,此密码是区分大小写的.如果工作表或工作簿不设密码保护则忽略本参数.如果对工作表省略此参数,而该工作表又设有密码保护,MicrosoftExcel将提示您要输入密码.如果对工作簿省略此参数,而该工作簿又设有密码保护,本方法将 失败.

1.3示例

示例:

（使用了4个文件,宏程序文件操作了另外三个文件,还涉及到文件打开另存对话框）

一.在test.xls文件中编写一个宏程序test,完成以下内容.打开当前目录下test1.xls文件,计算sheet1工作表上单元格a1到a10的数据剩上（0-1）的随机数,计算结果保存在一个新建工作簿的sheet1的a1到a10上,且结果还要保存在一个任意打开的Excel文件下,示例中打开test2.xls文件,完成后关闭Excel程序.

test.xls模块中Subtest（）

DimiAsInteger,flagAsBoolean,fm

Dimaa,bb,cc,temp

Application.ScreenUpdating=False'屏幕刷新关闭

Application.DisplayAlerts=False'信息警告关闭

Workbooks.OpenThisWorkbook.Path&"test1.xls"'打开文件test1.xls

Setaa=ActiveWorkbook.Sheets（"Sheet1"）

flag=False

DoWhileNotflag'对话框打开已有Excel文件

fm=Application.GetOpenFilename（fileFilter:

="Excelfiles（*.xls）,*.xls,_Allfiles（*.*）,*.*"）IffmFalseThen

Workbooks.Openfm

Setbb=ActiveWorkbook

flag=True

EndIf

Loop

Workbooks.Add

Setcc=ActiveWorkbook

Withcc.Sheets（"Sheet1"）

Fori=1To10

temp=aa.Cells（i,1）*Int（（10*Rnd）+1）'生成1到10之间的随机数值

.Cells（i,1）=temp

bb.Sheets

（1）.Cells（i,1）=temp

Next

EndWith

flag=False

DoWhileNotflag'循环要求必须输入文件名或选择文件名

fm=Application.GetSaveAsFilename（fileFilter:

="Excelfiles（*.xls）,*.xl_s,Allfiles（*.*）,*.*"）IffmFalseThen

cc.SaveAsfm

flag=True

EndIf

Loop

bb.Save'保存

Setaa=Nothing:

Setbb=Nothing:

Setcc=Nothing'设置对象变量为空

Application.Quit'关闭Excel

Application.ScreenUpdating=True'屏幕刷新关闭

Application.DisplayAlerts=True'信息警告开启 

EndSub

2）文本文件

2.1打开与新建Open语句 能够对文件输入/输出（I/O）.语法OpenpathnameFormode[Accessaccess][lock]As[#]filenumber[Len=reclength]

Open语句的语法具有以下几个部分:

部分 描0pathname必要.字符串表达式,指定文件名,该文件名可能还包括目录,文件夹及驱动器.mode必要.关键字,指定文件方式,有Append,Binary,Input,Output,或Random方 式.如果未指定方式,则以Random访问方式打开文件.access可选.关键字,说明打开的文件可以进行的操作,有Read,Write,或ReadWrite操 作.lock可选.关键字,说明限定于其它进程打开的文件的操作,有Shared,LockRead,LockWrite,和LockReadWrite操作.filenumber必要.一个有效的文件号,范围在1到511之间.使用FreeFile函数可得到下一个 可用的文件号.reclength可选.小于或等于32,767（字节）的一个数.对于用随机访问方式打开的文件,该值 就是记录长度.对于顺序文件,该值就是缓冲字符数.

说明

对文件做任何I/O操作之前都必须先打开文件.Open语句分配一个缓冲区供文件进行I/O之用,

并决定缓冲区所使用的访问方式.

[新建]如果pathname指定的文件不存在,那么,在用Append,Binary,Output,或Random方

式打开文件时,可以建立这一文件.

如果文件已由其它进程打开,而且不允许指定的访问类型,则Open操作失败,而且会有错误发生.

如果mode是Binary方式,则Len子句会被忽略掉.

重要

在Binary,Input和Random方式下可以用不同的文件号打开同一文件,而不必先将该文件关闭.

在Append和Output方式下,如果要用不同的文件号打开同一文件,则必须在打开文件之前先关闭该 文件.

2.2读入与写出

2.2.1读入

Input#filenumber,varlist从已打开的顺序文件中读出数据并将数据指定给变量

Get[#]filenumber,[recnumber],varname将一个已打开的磁盘文件读入一个变量之中2.2.2写入Write#filenumber,[outputlist]

将数据写入顺序文件,以双引号"数据"逗号,分隔数据

Print#filenumber,[outputlist]将格式化显示的数据写入顺序文件中

Put[#]filenumber,[recnumber],varname将一个变量的数据写入磁盘文件中.

2.3关闭

Close[filenumberlist]关闭Open语句所打开的输入/输出（I/O）文件

注意:

如果今后想用Input#语句读出文件的数据,就要用Write#语句而不用Print#语句将数据写入文件.因为在使用Write#时,将数据域分界就可确保每个数据域的完整性,因此可用Input#再将数据读出来.使用Write#还能确保任何地区的数据都被正确读出.Write与Print#语句不同,当要将数据写入文件 时,Write#语句会在项目和用来标记字符串的引号之间插入逗号.Write#语句在将outputlist中的最后 一个字符写入文件后会插入一个新行字符,即回车换行符,（Chr（13）+Chr（10））

2.4其他文件函数

LOF（filenumber）返回一个Long,表示用Open语句打开的文件的大小,该大小以字节为 单位.EOF（filenumber）返回一个Integer,它包含Boolean值True,表明已经到达为Random或顺序Input打开的文件的结尾.Loc（filenumber）返回一个Long,在已打开的文件中指定当前读/写位置

Seek（filenumber）返回一个Long,在Open语句打开的文件中指定当前的读/写位置

2.5示例

要求:

打开一文本文件test1.txt,已知其内容为空格分隔,要求把其中每行首个数据写入Excel宏程序文件 的表1中,再把数据写入新建文件test2.ini中

Subtest（）

DimFm,iAsLong,jAsLong,kAsLong

DimTT,T1

OnErrorResumeNext

Fm=Application.GetOpenFilename（"TextFiles（*.txt）,*.txt"）

IfFm=FalseThenExitSub'取消选择文件则退出k=FreeFile

OpenFmForInputAs#k'以随机只读的方式打开文件

f2=FreeFile

Open"c:

test2.ini"ForOutputAs#f2'以随机方式新建一个不存在的文件

j=1

WithWorksheets（"sheet1"）

DoWhileNotEOF（k）'循环读至文件最后一行LineInput#k,TT'读入一行数据并将其赋予某变量

T1=Split（TT）'以文本中空格来分开这个字符串并赋值给变量,请参考split函数帮助.Cells（j,1）=T1（0）

Print#f2,T1（0）

j=j+1

Loop

EndWith

Close#k'关闭文件

Close#f2'关闭文件

EndSub

3）Access文件

使用VBA来访问数据库,其实就是通过一定方法借助数据库引擎来访问,关键是使用什么方法来使用引擎.目前访问ACCESS数据库常用的有DAO和ADO方法,DAO就是DatabaseAccessObject（数据库 访问对象）的英文缩写,DAO是老式的,它目前还在使用的原因是向下兼容和ADO在一些地方还没有取代DAO的功能.ADO是ActiveXDataObject（数据控件对象）的英文缩写,是目前较新和功能较强的方法.

通过DAO或ADO可以访问多种类型的数据库,包括Access,SQLServer,Oracle等,也可访问应用程序的文件,如Excel文件,文本文件及Email和NEWS的文件.实际上利用控件来链接数据库,其概念 大致相同,首先都需创建链接,其后用Open方法产生各种类型的数据集对象,再对数据集对象操作来使 用数据库内容.

3.1DAO使用的步骤及方法

（1）.引用DAO类型库

从VBE的"工具/引用"菜单中选择可引用"MicrosoftDAO3.6ObjectLibrary"项.

设置DAO数据类型变量

（2）.定义DAO对象变量

1）DimdbAsDatabase,Database（数据库）变量对应于Access数据库.

2）DimrsAsRecordSet,RecordSet（记录集）变量对应于Access数据库的一个表或子表.多定义为全局变量,以供程序多处使用.

（3）.打开数据库

Setdb=OpenDatabase（Thisworkbooks.path&"数据库名0.mdb"）'用DAO怎么样打开有密码的ACCESS数据库SetdbTemp=OpenDatabase（"C:

db.mdb",False,False,";PWD=12345"）

（4）.打开表或建立表的子表A.建立表的子表

Setrs=db.OpenRecordset（"select*from表名"）

B.打开表

SetRS=DB.OpenRecordset（"表名",dbOpenDynaset）

Opendatabase方法是打开数据库并返回此数据库的database对象,其语法如下:

Setdatabase的对象变量=opendatabase（[路径及数据库名0],[除外性],[只读]）除外性:

由true和false值所构成,当值为true时代表仅允许唯一的使用者使用数据库.只读:

由true和false值所构成,为true代表数据库仅提供读取的服务

Openrecordset方法用来创建一个新的recordset对象,语法为:

Setrecordset对象变量=数据库变量.openrecordset（来源,种类）recordset种类有5种,分别为:

表（table）,动态集（dynaset）,快照集（snapshot）,动态（dynamic）,正 向（forward-only）,其中常用的时动态集（dynaset）实际上是引用一个或多个表中数据记录的集合,是功 能最强的数据记录集合类型,也是默认值.

（5）.操作数据库记录

对记录的操作就是使用记录集的对象方法和属性来实现,特附录常用属性和方法如下.

记录集对象的属性和方法:

rs.Recordcount属性 用来记录目前数据记录的数量,如判断数据库是否为空rs.EOF属性 是否是记录的尾rs.BOF属性 是否是记录的头rs.Nomatch属性 返回上次查找成功与否

rs.Moven方法 移动到第n条记录rs.Movenext方法 移动到下一条记录rs.MovePrevious方法 移动到上一条记录rs.Movefirst方法 移动到第一条记录rs.Lastfirst方法 移动到最后一条记录rs.Delete方法 删除当前记录rs.Edit方法 修改当前记录（步骤为三步:

1.用edit方法设置为修改状态;2.将数据分别赋到记录的各字 段;3.用Updata方法,把记录更新到数据库中）rs.AddNew方法 添加记录（添加记录分三步:

1.用AddNew方法添加一个新的空白记录;2.将数据分别赋到记录的各字段;3.用Updata方法,把记录更新到数据库中去）rs.Updata方法 更新内容到数据库中rs.Findfirst"字段名='"&"查找内容"&"'"方法 查找记录中字段与内容相配的首条记录rs.Findnext方法 查找下一个匹配记录rs.FindLast方法 查找最后一个匹配记录rs.FindPrevious方法 查找前一个匹配记录rs.Close方法 关闭 记录集关闭链接

字段集的属性方法:

rs.Fields.Count属性 字段数目rs.Fields（n）第n+1个字段,Fields（0）表示第一个字段rs.Fields.Delete（NameAsString）方法 删除字段rs.Fields.Append（ObjectAsObject）方法 添加字段rs.Fields.Refresh方法 更新

字段的属性方法:

rs.Fields（n）.Name

rs.Fields（n）.Sizers.Fields（n）.Typers.Fields（n）.Fieldsizers.Fields（n）.value

示例:

打开一个数据库,建立浏览,查询,修改,删除,添加等功能.OptionExplicit

PublictotalRecsAsLong,curRecNoAsLong'用于记住 总记录数 和 当前记录号

PublicDB1AsDatabase,RS1AsRecordset

PrivateSubUserForm_Initialize（）'窗口显示

SetDB1=OpenDatabase（ThisWorkbook.Path&"pallet.mdb"）

SetRS1=DB1.OpenRecordset（"pallet",dbOpenDynaset）

IfRS1.EOFAndRS1.BOFThen

MsgBox"DatabaseisNull."

cmdExit_Click

Else

RS1.MoveLast'指针移动到最后记录,以便统计记录总数

RS1.MoveFirst

totalRecs=RS1.RecordCount

curRecNo=1

SetData'设置窗口参数,按钮是否可用

EndIfEndSub

PrivateSubcmdFirst_Click（）'第一条记录RS1.MoveFirst

curRecNo=1

SetDataEndSub

PrivateSubcmdLast_Click（）'最后一条记录RS1.MoveLast

curRecNo=totalRecs

SetDataEndSub

PrivateSubcmdPrevious_Click（）'前一条记录RS1.MovePrevious

curRecNo=curRecNo-1

SetDataEndSub

PrivateSubcmdNext_Click（）'下一条记录RS1.MoveNext

curRecNo=curRecNo+1

SetDataEndSub

PrivateSubcmdAdd_Click（）'增加一条记录DimiAsInteger

RS1.AddNew

Fori=1To5

RS1.Fields（i）=Me.Controls（"txt"&i）

Nexti

RS1.Update

totalRecs=totalRecs+1

curRecNo=totalRecs

RS1.MoveLast

SetDataEndSub

PrivateSubcmdDelete_Click（）'删除当前记录 RS1.Delete

RS1.MoveNext

IfRS1.EO