[原创]VBA 学习笔记--不断更新(2008.8.3)

forrest

          第二章 EXCEL VBA 常用对象之使用

            第一节 文件的操作

1） Excel 文件
2） 文本文件
3） ACCESS文件

1）Excel 文件

1.1 新建与打开
一、新建
Workbooks.Add

二、打开
Workbooks.Open "路径\" & "文件名.xls"

三、文件打开对话框的使用
Application.GetOpenFilename(fileFilter:="Excel files(*.xls),*.xls,All files(*.*),*.*")

示例：
flag=False
Do While Not flag                               '对话框打开已有EXCEL文件
   fm=Application.GetOpenFilename(fileFilter:="Excel files(*.xls),*.xls,All files(*.*),*.*")
   If fm<>False Then
      Workbooks.Open fm
      Set bb=Active Workbook
      '把bb变量设为当前活动工作簿对象，打开一工作簿时，该工作簿自动为当前活动工作簿
      flag=True
    End If
Loop

1.2 保存与关闭

一、保存

Workbooks("文件.xls").Save

二、另存对话框的使用

Application.GetSaveAsFilename(fileFilter:="Excel files(*.xls),*.xls,All files(*.*),*.*")

示例：

flag=False
Do while Not flag                '循环要求必须输入文件名或选择文件名
  fm=Application.GetSaveAsFilename(fileFilter:="Excel files(*.xls),*.xls,All files(*.*),*.*")
  If fm<>False Then
     activeworkbook.SaveAs fm    '当前活动工作簿另存
     flag=True
  End If
Loop

forrest

三、关闭

1.单个文件关闭
  Workbooks("filename.xls").Close

2. 所有该EXCEL程序打开的文件都关闭
   Workbooks.Close

如果文件使用中改动过内容,那么该命令使用时会提示对话框,询问是否保存。
如果要想不弹出提示对话框，可以使用第三或第四种文件关闭方法，或者如下示例。

示例：

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

四、关闭同时保存
  Workbooks("filename.xls").Close savechanges:=True

五、关闭同时不保存
  Workbooks("filename.xls").Close savechanges:=False

六、EXCEL程序的关闭
  Application.Quit

说明：

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

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

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

Password Variant 类型，可选。为一个字符串，该字符串为工作表或工作簿指定区分大小写的密码。如果省略本参数，不用密码就可以取消对该工作表或工作簿的保护。否则，必须指定密码，通过密码来取消对该工作表或工作簿的保护。如果忘记了密码，就无法取消对该工作表或工作簿的保护。最好在安全的地方保存一份密码及其对应文档名的列表。

Structure Variant 类型，可选。如果为True,则保护工作簿结构（工作表的相对位置）。默认值为Fale.
Windows Variant 类型，可选。如果为True,则保护工作簿窗口。如果省略本参数，则窗口不受保护。
 如： Workbooks("学生档案"）protect "1234"

          Workbooks("学生档案").Unprotect(Password)

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

[此贴子已经被作者于2008-8-5 18:53:20编辑过]

forrest

1.3 示例

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

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

Sub test()

Dim i As Integer, flag As Boolean, fm
Dim aa, bb, cc, temp

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Workbooks.Open "d:\" & "test1.xls"    '´ò¿ªÎļþtest1.xls
Set aa = ActiveWorkbook.Sheets("sheet1")

flag = False          '¶Ô»°¿ò´ò¿ªÒÑÓÐEXCELÎļþ
Do While Not flag
 fm = Application.GetOpenFilename(fileFilter:="Excel files(*.xls),*.xls,All files(*.*),*.*")
   If fm <> False Then
      Workbooks.Open fm
      Set bb = ActiveWorkbook
      '°Ñbb±äÁ¿ÉèΪµ±Ç°»î¶¯¹¤×÷²¾¶ÔÏ󣬴ò¿ªÒ»¹¤×÷²¾Ê±£¬¸Ã¹¤×÷²¾×Ô¶¯Îªµ±Ç°»î¶¯¹¤×÷²¾
      flag = True
    End If
Loop

Workbooks.Add
Set cc = ActiveWorkbook
With cc.Sheets("sheet1")
    For i = 1 To 10
        temp = aa.Cells(i, 1) * Int(10 * Rnd) + 1 'Éú³É1µ½10Ö®¼äµÄËæ»úÊý
        .Cells(i, 1) = temp
        bb.Sheets(1).Cells(i, 1) = temp
    Next
End With

flag = False
Do While Not flag                'Ñ­»·ÒªÇó±ØÐëÊäÈëÎļþÃû»òÑ¡ÔñÎļþÃû
  fm = Application.GetSaveAsFilename(fileFilter:="Excel files(*.xls),*.xls,All files(*.*),*.*")
  If fm <> False Then
     cc.SaveAs fm    'µ±Ç°»î¶¯¹¤×÷²¾Áí´æ
     flag = True
  End If
Loop

bb.Save      '±£´æ
Set aa = Nothing: Set bb = Nothing: Set cc = Nothing 'ÉèÖöÔÏó±äÁ¿Îª¿Õ
Application.Quit   '¹Ø±ÕEXCEL
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

[此贴子已经被作者于2008-8-5 19:20:55编辑过]

forrest

2) 文本文件

2.1 打开与新建

Open 语句

  能够对文件输入/输出(I/O)

语法
Open pathname For mode[Access access] [lock] As [#]filename [Len=reclength]

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

部分            描述
pathname        必要。字符串表达式，指定文件名，该文件名可能还包括目录、文件夹及驱动器。
mode            必要。关键字，指定文件方式，有Append,Binary,Input,Output或Random方式。如果未指定方式，则以Random访问方式打开文件。
access          可选。关键字，说明打开的文件可以进行的操作，有Read,Write或Read Write 操作。
lock            可选。关键字，说明限定于其它进程打开的文件的操作，有Shared,Lock Read,Lock Write和Lock Read Write操作。
filename       必要。一个有效的文件号，范围在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 方式下，如果要用不同的文件号打开同一文件，则必须在打开文件之前行关闭该文件。

[此贴子已经被作者于2008-8-6 7:10:00编辑过]

forrest

2.2 读入与写出

2.2.1 读入

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

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

2.2.2 写入

Write #filenumber,[outputlist]
将数据写入顺序文件,以双引号"数据"逗号,分隔数据

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

2.3 关闭

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

注意:

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

forrest

2.4 其它文件函数

Lof(filenumber) 返回一个Long,表示用Open 语句打开的文件的大小，该大小以字节为单位。

EOF(filenumber) 返回一个Integer, 它包含Boolean 值True, 表明已经到达为 Random或顺序 Input 打开的文件的结尾。

Loc(filenumber) 返回一个Long, 在已打开的文件中指定当前读/写位置。

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

2.5 示例

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

Sub test()

    Dim Fm,i As Long, J As Long, K As Long
    Dim TT,T1
    On Error Resume Next

    Fm=Applicaiton.GetOpenFilename("Text Files(*.txt),*.txt")
    If Fm=False Then Exit Sub      '取消选择文件则退出
    k=FreeFile
    Open Fm For Input As #k        '以随机只读的方式打开文件

    f2=FreeFile
    Open "c:\test2.ini" For Output As #f2  '以随机方式新建一个不存在的文件
   

    j=1
     With Worksheets("sheet1")
     Do While Not EOF(k)          '循环读至文件最后一行
        Line Input #k,TT          '读入一行数据并将其赋予某变量
        T1= Split(TT)             '以文本中空格来分开这个字符串并赋值给变量，请参考split 函数帮助
        .Cells(J,1)= T1(0)
        Print #f2,T1(0)
        j=j+1
      Loop
      End With

      Clock #k                    '关闭文件
      Close #f2                   '关闭文件

forrest

3） Access 文件

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

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

forrest

3.1 DAO 使用的步骤及方法

(1) 引用DAO类型库

     从VBE 的"工具/引用"菜单中选择可引用"Microsoft DAO 3.6 Object Library"项.
设置DAO数据类型变量

(2) 定义DAO对象变量
1) Dim db As Database, Database(数据库)变量对应于Access 数据库.
2) Dim rs As RecordSet, RecordSet(记录集)变量对应于Access 数据库的一个表或子表.
多定义全局变量,以供程序多处使用.

(3) 打开数据库
Set db=OpenDatabase( Thisworkbooks.path & "\数据库名称.mdb")
'用DAO 怎么打开有密码的ACCESS 数据库
Set dbTemp=OpenDatabase("C:\db.mdb",False,False, ";PWD=12345")

(4)打开表或建立表的子表
A.建立表的子表
 Set rs=db.OpenRecordset("Select * from 表名")

B.打开表
 Set RS=DB.OpenRecordset("表名",dbOpenDynaset)

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

Set database的对象变量=opendatabase([路径及数据库名称],[除外性],[只读])
除外性: 由true 和 false 值所构成,当值为 true 时代表仅允许唯一的使用者使用数据库.
只读: 同true 和 false 值所构成,为 true 代表数据库令提供读取的服务

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

  set recordset 对象变量=数据库对象.openrecordset(来源，种类）
recordset 种类有5种，分别为：表（table)、动态集（dynaset)、快照集（snapshot)、动态（dynamic)、正向（forward-only),其中常用的时动态集（dynast)实际上是引用一个或多个表中数据记录的集合，是功能最强的数据记录集合类型，也是默认值。

（5）操作数据库记录
对记录的操作就是使用记录集的对象方法和属性来实现，特附录常用属性和方法如下。

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

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

rs.Move n  方法  移动到第n 条记录
rs.Movenext 方法  移动到下一条记录
rs.MovePrevious方法 移动到上一条记录
rs.MoveFirst方法   移动到第一条记录
rs.Lastfirst 方法  移动到最后一条记录
rs.Delete 方法     删除当前记录
rs.Edit 方法       修改当前记录
              （步骤为三步：1. 用EDIT 方法设置为修改状态;
                            2. 将数据分别赋到记录的各字段
                            3. 用update 方法,把记录更新至数据库中)
rs.AddNew 方法 添加记录
               (添加记录分三步: 1. 用AddNew 方法添加一个新的空白记录;
                                2. 将数据分别赋到记录的各字段:
                                3. 用Update 方法,把记录更新到数据库中去)
rs.Update  方法  更新内容到数据库中
rs.Findfirst "字段名='" & "查找内容" & "'" 方法 查找记录中字段与内容相配的首条记录
rs. Findnext 方法 查找下一个匹配记录
rs. FindLast 方法 查找最后一个匹配记录
rs. FindPrevious 方法 查找前一个匹配记录
rs. Close 方法 记录集关闭链接

forrest

字段集的属性方法:

rs.Fields.Count 属性 字段数目
rs.Fields(n) 第 n+1 个字段,Fiels(0) 表示第一个字段
rs.Fields.Delete(Name As String) 方法 删除字段
rs.Fields.Append(Object As Object) 方法 添加字段
rs.Fields.Refresh 方法 更新

字段的属性方法:

rs.Fields(n).Name
rs.Fields(n).Size
rs.Fields(n).Type
rs.Fields(n).Fieldsize
rs.Fields(n).value

示例:略

forrest

3.2ADO 使用的步骤及方法

(1) 引用ADO 类型库

    从VBE 的"工具/引用"菜单中选择可引用"Microsoft ActiveX Data Object 2.5 Library"项.

(2) 定义和生成ADO对象

    1) Dim Cnn As New ADODB.Connection          '数据库连接Connection对象变量
    2) Dim Rst As New ADODB.Recordset           '数据库记录集对象变量
    3) Dim Fld As ADODB.Field                   '记录字段对象变量

(3) 打开数据库

     Cnn.Open "Provide=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "\CONTACT1.MDB;"
    用来定义生成的数据库对接对象的open方法,打开一个数据库.如果要用ADO打开一个加密码的数据库,方法如下:

     Cnn.Open "Provide=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "\CONTACT1.MDB;" & ";Jet OLEDB:Dataase Password=123"

(4) 打开表或建立表的子表

    Rst.Open "SELECT 字段名[,字段名] FROM 表名",Cnn,adOpenKeyset,adLockOptimistic
    用记录集对象的open 方法,打开或生成一个数据库的表或子表记录。
   如：Rs1.Open "Person", Cnn,adOpenKeyset,adLockOptimistic     '取得联系人表所有记录集内容

（5）操作数据库记录

    对记录集的操作就是使用记录集的对象方法和属性来实现，属性和方法其实与DAO的相似。

（6）SQL

    请参考别的专业教程。

[此贴子已经被作者于2008-8-7 12:45:42编辑过]