Excel VBA宏精简二.docx
- 文档编号:11156172
- 上传时间:2023-02-25
- 格式:DOCX
- 页数:10
- 大小:25.22KB
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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel VBA宏精简二 VBA 精简