Vba+access+Excel编程

学习目的是建立简易的管理系统

当前学习路线：excel的宏的简单了解→access数据库的了解→两者的结合

 

excel的宏的简单了解：

关于宏的使用，大部分文章都提到说Vba很难写，但是可以通过一段现成的代码，修改关键部分来为自己所用，现成代码的获得方式就是录制宏，在其自动生成的代码上添加循环等修改，达到自己想要的效果。

关于宏的录制与代码的修改运行如下：

1.首先要在excel中找到开发工具（WPS也有，但当前使用的是office的）

在excel的“文件”→“选项”→“自定义功能区”的“主选项卡”部分，把“开发工具”一项勾选上，点击右下角的确定，这样excel的操作栏就会显示开发工具选项卡了

 

 接下来举例实现功能：录制宏操作，对A1：A10单元格添加批注，批注内容是“你好”

首先，在开发工具中点击录制宏

接下来做正常的批注操作，批注好之后点击停止录制

然后点击Visual Basic 会弹窗出来如图所示的界面

 

 首先要在左侧的工程界面找到我们需要操作的对象，是example.xlsx的模块，打开后才会显示右侧的代码部分，宏2是录制下来的“本体”生成的代码，把它修改为宏1的形式就完成了从A列的1到10都批注“你好”的功能

这里有一个小知识点：

Cstr()函数可以将数字转换成字符，如 2 → “2”

Val()函数可以将字符（数字字符）转换成数字， 如 “2” → 2

代码写好之后，运行即可（注意！！！如果录制的宏是对A1批注，代码中的循环操作又包括了对A1的操作的话，会报错的，需要先把原A1的批注删除掉，即不能对已经存在的操作重复操作）

 

access数据库的建立

首先可以在目标文件夹新建一个.accdb格式的数据库文档

数据库的数据来源可以手动添加，也可以根据excel导入（推荐），即可建立一个数据库可供使用

如下图所示

 

 

 

 

两者的结合

首先明确一下目标：是为了通过对宏的编写和调用，实现excel和access连接，通过一些sql语句，将目标数据从数据库中提取到excel中

在Visual Basic新建一个新的模块，即新建了一个宏，然后要现尝试着把excel和access连接起来，在Visual Basic for Applications的窗口中找到 “工具” → “引用”

如图所示

 

 

 

在可使用的引用中找到“Microsoft ActiveX Data Objects”（简称ADO）可能有很多版本，选择最高的版本即可，勾选然后点击确定

接下来就是代码的实现了，代码部分如下

Sub ConnectDBtest()
'第一步，告诉电脑需要使用ADO工具，在工具→引用中找到ADO确认引用

'第二步，创建连接对象
'2-1，给连接对象取名字
Dim con As ADODB.Connection    '声明对象变量
'2-2,创建对象变量并赋值
Set con = New ADODB.Connection   '创建对象变量

'第三步，建立数据库的连接
con.Open "provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.Path & "\example.accdb"


MsgBox "连接成功"


End Sub

第一行是给这部分代码命名

第六行是声明对象变量的类型 Dim ...As ...

第八行是正式创建这个变量

第十一行是建立对象与数据库的连接，其中要注意两点，第一是数据驱动引擎即provider的版本，第二是所连接的数据库的位置

   1.版本： 可以根据数据库文件的后缀名进行简单的判断，03版的后缀名是.mdb，07版以上用的是accdb，对应使用的provider就是12.0的

   2.数据库的位置：本地的数据库可以照上方第十一行的写法，ThisWorkbook.Path，如果是其他位置的数据库，可以按照如下所示的方法，把数据库位置设置为变量

'第三步，建立数据库的连接
Dim mydblocation As String
mydblocation = "数据库位置"
con.Open "provider=microsoft.ace.oledb.12.0;data source=" & mydblocation & "\example.accdb"
MsgBox "连接成功"

以上是确保了数据库与excel的连接，连接成功后会出现弹窗提示 “连接成功”【判断连接成功还有一个方法是通过查看本地是否出现了example.laccdb这样的一个文件，出现则表明数据库已经被打开了】

 

接下来要实现把数据库的内容通过SQL语言来帅选出来，放入excel指定的位置，代码如下

Sub ConnectDBtest()
'第一步，告诉电脑需要使用ADO工具，在工具→引用中找到ADO确认引用

'第二部，创建连接对象
'2-1，给连接对象取名字
Dim con As ADODB.Connection    '声明对象变量
'2-2,创建对象变量并赋值
Set con = New ADODB.Connection   '创建对象变量

'第三步，建立数据库的连接
Dim mydblocation As String
mydblocation = "数据库远程的位置"
con.Open "provider=microsoft.ace.oledb.12.0;data source=" & mydblocation & "\example.accdb"
MsgBox "连接成功"

'第四步，查询表中满足条件的字段，这里是列出工作表 “Summary of frame-parallel test” decoder一列下为google的行的内容
Sql = "select * from [Summary of frame-parallel test] where [decoder] = 'google'"
Dim rs As New ADODB.Recordset    '创建记录集对象

'第五步，执行sql查询语句
Set rs = con.Execute(Sql)

'获取字段名即col的title
Dim i As Integer
For i = 0 To rs.Fields.Count - 1
    Cells(1, i + 1) = rs.Fields(i).Name
Next


Sheets("sheet1").Range("A2").CopyFromRecordset rs    '将记录集rs中的数据返回到工作表中


'清空缓存区
rs.Close: Set rs = Nothing
con.Close: Set con = Nothing


End Sub

第十七行是定义了数据库查询的内容

第十八行和21行是定义了装载查询后结果的容器为rs，即记录集，21行是执行，执行后结果都放在rs中

第24~27行是遍历获取数据库的列的title并写进excel的第一行

第30行是复制了数据集rs中的内容到excel工作薄中，名为“sheet1”的工作表，且从A2开始写

第34~35行是清空rs记录集和对象的缓存

其中第21行生成记录集的方法：

1.第21行的方法可以称之为“使用connection对象的Execute方法产生记录集”，这种方法产生的记录集特点：一.rs是只读的，不能修改记录，通常用于做一些不需要返回的操作。二.不能获取记录的条数

2.还有一种方法较叫“使用recordset对象的open方法”产生记录集，这种方法获得的记录集是可读可写的，可以修改记录，语句是

$  rs.Open sql，con，adOpenKeyset，adLockOptimistic

其中Open后面跟的四个参数，第一个sql表示所执行的命令字符串（因为此例中第17行已经定义了查询语句为sql），第二个参数写的是当前连接的哪一个数据库，这里是连接了con数据库，第三个参数选游标的运动方式，选动态的（其实这里选固定的adOpenKeyset即可包括后面第四个参数也是一样）

这种方式可以获取到符合条件的记录的条数，便于做判断，使表格使用更方便（如果无记录，使用if语句判断一下，弹窗提示即可，无需再生成一个空表格）

如下代码所示，实现的是如果没有查找到记录，会弹窗提示无记录而不是报错

Sub ConnectDBtest()
'第一步，告诉电脑需要使用ADO工具，在工具→引用中找到ADO确认引用

'第二部，创建连接对象
'2-1，给连接对象取名字
Dim con As ADODB.Connection    '声明对象变量
'2-2,创建对象变量并赋值
Set con = New ADODB.Connection   '创建对象变量

'第三步，建立数据库的连接
Dim mydblocation As String
mydblocation = "数据库位置"
con.Open "provider=microsoft.ace.oledb.12.0;data source=" & mydblocation & "\example.accdb"
MsgBox "连接成功"

'第四步，查询表中满足条件的字段
Sql = "select * from [Summary of frame-parallel test] where [decoder] = 'ci'"

Dim rs As New ADODB.Recordset    '创建记录集对象方式1

'第五步，执行sql查询语句
rs.Open Sql, con, adOpenKeyset, adLockOptimistic

If rs.RecordCount <= 0 Then      '表示没有满足条件的记录
    MsgBox "没有满足条件的记录"
Else
    '获取字段名即col的title
    Dim i As Integer
    For i = 0 To rs.Fields.Count - 1
        Cells(1, i + 1) = rs.Fields(i).Name
    Next
    
    
    Sheets("sheet1").Range("A2").CopyFromRecordset rs    '将记录集rs中的数据返回到工作表中
End If

'清空缓存区
rs.Close: Set rs = Nothing
con.Close: Set con = Nothing


End Sub

如果用connection对象的Execute方法产生记录集，也可以实现判空的功能，代码如下

Sub ConnectDBtest()
'第一步，告诉电脑需要使用ADO工具，在工具→引用中找到ADO确认引用

'第二部，创建连接对象
'2-1，给连接对象取名字
Dim con As ADODB.Connection    '声明对象变量
'2-2,创建对象变量并赋值
Set con = New ADODB.Connection   '创建对象变量

'第三步，建立数据库的连接
Dim mydblocation As String
mydblocation = "数据库位置"
con.Open "provider=microsoft.ace.oledb.12.0;data source=" & mydblocation & "\example.accdb"
MsgBox "连接成功"

'第四步，查询表中满足条件的字段
Sql = "select * from [Summary of frame-parallel test] where [decoder] = 'ci'"

Dim rs As New ADODB.Recordset    '创建记录集对象方式1
Set rs = con.Execute(Sql)

'EOF表示记录的结尾，BOF表示记录的开头
If rs.EOF And rs.BOF Then       '指针既指向开头又指向末尾，说明没有记录

'第五步，执行sql查询语句
'rs.Open Sql, con, adOpenKeyset, adLockOptimistic
'If rs.RecordCount <= 0 Then      '表示没有满足条件的记录
    MsgBox "没有满足条件的记录"
Else
    '获取字段名即col的title
    Dim i As Integer
    For i = 0 To rs.Fields.Count - 1
        Cells(1, i + 1) = rs.Fields(i).Name
    Next
    
    
    Sheets("sheet1").Range("A2").CopyFromRecordset rs    '将记录集rs中的数据返回到工作表中
End If

'清空缓存区
rs.Close: Set rs = Nothing
con.Close: Set con = Nothing


End Sub

 

 窗体的设置

excel要结合窗体才能实现更便捷的功能，设置方法如下：

首先在excel的Visual Basic中找到对应工程，点击菜单栏中的插入→用户窗体

 

 接下来会出现如图所示的界面

 

 窗体调出来之后，是需要有个模块来专门编辑窗体信息的，在此再点击插入→模块，创建一个新的模块

'定义一个函数名为OpenMenuWindow来打来窗体
Sub OpenMenuWindow()

'窗体名称.show可以调出目标窗体
UserForm1.Show

End Sub

运行这个函数就可以打开窗体了

窗体的外形和内容设计的方法是：在窗体的编辑页面下，右键点击窗体，再点击查看代码会出现如图所示的界面

 

 意思是当前的窗体UserForm是单击事件，我们需要把它改掉，进行初始化的设置

可以先点进去UserForm1的界面，在工具箱的控件中选择“框架”，画出一个框架如图所示

 

 然后点击“列表框”，在框架中画出一个列表框

 

 在UserForm1的空白处点击一下，可以预览到大致的效果如图

 

 我们要对这个框架和列表框的名称进行修改，便于后续的调用操作，于是可以先选中列表框，在左侧的属性栏中，修改“名称”为“Listphone”，再选中框架，修改名称为“selectphone”，再把“caption”修改为“选择手机型号”，这样完成的效果为

 

 右键点击这个userform1，查看代码，可以把里面的东西都清除干净，然后，选择这个userform1的类型为initialize，补充函数如下

Option Explicit
Dim con As ADODB.Connection
Dim rs As Recordset

'当窗体加载时显示Android Phone这个列表框的内容
Private Sub UserForm_initialize()

Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
'建立数据库的连接
Dim mydblocation As String
mydblocation = "数据库地址"
con.Open "provider=microsoft.ace.oledb.12.0;data source=" & mydblocation & "\example.accdb"
MsgBox "连接成功"

'提取不重复的手机类型
Dim sql1 As String           '定义命令字符串变量
sql1 = "select distinct [Android Phone] from [Summary of frame-parallel test]"
rs.Open sql1, con, adOpenKeyset, adLockOptimistic

Dim i As Integer
With Listphone
    .Clear
    For i = 1 To rs.RecordCount
        .AddItem rs("Android Phone")
        rs.MoveNext
        
    Next i

End With
End Sub

函数说明：

2,3行分别是声明连接对象变量、声明记录集对象变量

第6行往下就是这函数的正式内容了

8，9行分别是创建连接对象变量，创建记录集对象变量

11~14行是建立数据库的连接

17~19行是用open方法去提取不重复的手机类型，把结果放入记录集rs中

22是用with + 模块名称，表示操作对象（23行是清除此次之前的列表框中的记录）

24行是表示开始遍历记录集中数据

25行说明了要取的rs中的数据

26行是将记录集中的指针指向下一条记录

28行是指针+1

30行，31行是结束with部分和结束函数部分

 

 

样例分析

此样例是实现了：对任意access数据库的数据表，任选三个字段名为条件进行筛选查询（目前存在的缺陷是数据中如果某字段名的列单元格为空，那么选这个字段为条件就会报错“类型不匹配”）