Excel简单编程学习笔记

多少年来，我都一直认为Excel就是一个表格工具，用来记一记数据内容即可，直到后来开发同事用Excel做出了很花哨的动态报告，可以从数据库提取数据，自动填充排版数据，可以自动计算绘图，自动套用模版格式，自动生成pdf…  太可怕了。

我目前无法达到这样的水平，只能从最基本的数据提取开始。

一. 开启编程模式

1. Excel版本建议在2010以上，目前我使用的是2013；

2. 进入Excel后，依次点击打开左上角文件 → 选项，在选项中选择“自定义功能区”，将“开发工具”勾选，然后确定；

 →  → 

 

3. 回到excel主界面，可以看到开发工具选项卡已经显示，点击后找到最左侧的Visual Basic按钮，进入后，我们的所有编写代码将在里面完成；

 

二. 页面布局

1. 目前进行数据提取的学习实践，所以在excel主界面，我们需要设计一个基本布局界面，和我们平时使用excel表格，做表格样式一样；

 

2. 现在我参照sql中测试表的样式，在excel做相同页面布局；

  

 

三. 代码编写，功能实现

1. 增加触发按钮，如同SSMS中的执行按钮一样，excel界面上也需要增加一个执行按钮，“开发工具”→“插入”→“表单控件按钮”；

 

2. 按钮绑定代码宏，单纯的按钮是没有功能的，所以需要跟按钮绑定相关的代码宏，在此我提前新建了一个宏“数据提取”，选择后点击确定即可，同时给按钮重新命名一下；

 

3. 正式编辑宏代码，接上图，选择宏后，点击编辑按钮，进入到了VB代码编辑页面，我们的功能属于“模块1”，之后就在右侧sub和end sub中编写代码；

 

4. 首先定义变量参数；

i：循环变量；

box1，box2：按钮条件变量，比如开始时间、结束时间；

cn：数据库连接，ADODB.Connection数据库连接控件；

rs：记录集，ADODB.Recordset记录集控件；

strCn：数据库连接串存储变量，下方填写了数据库连接；

strSQL：sql语句存储变量；

注意：在此步一定要注意勾选引用，否则部分功能控件无法使用。

工具 → 引用，将Microsoft ActiveX Data Objects 2.6 Library勾选，确定

 

 

5. 定义box的类型

这里将用户输入的“日期”内容记录到box变量。

 

6. 连接数据库，编写sql语句，打开记录集记录查询到的数据；

cn.Open strCn：连接数据库；

rs.Open strSQL, cn：将sql语句查询出的数据记录到记录集；

i=2：起始行从第2行开始

 

7. 数据载入表格页面；

 

 Worksheets("Sheet1").Unprotect：解除选项页“Sheet1”的保护，可以写入数据；

Do While Not rs.EOF：循环读取数据集中的数据，到达尾行则停止循环；

Worksheets("Sheet1").Cells(i, 1).Value = rs("id")：将sql查询出的数据中的“id”字段，填入选项页“Sheet1”的第2行开始的第1列，其他内容类似，不做重复描述；

rs.MoveNext：记录开始下一条，与sql中的游标功能类似，逐行循环；

i = i + 1：循环递增1；

Loop：循环语法中的执行，类似于while … do中的do；

rs.Close：结束记录集；

cn.Close：结束数据库连接；

 

8. 按照编程的操作规范，应该要进行调试，这里就不做描述；

 

四. 使用演示