excel mysql日报_EXCEL日报自动化_2

建立“通报正文”表

整理好“数据源”表与“数据转化”表后，接下来就该把每条通报正文与图表一一对应起来，按事先拟定的分析框架进行整理。

① 新建一张工作表，并命名为“日报正文”。

② 在A1单元格处输入通报标题，合并A1：H1单元格，并设置字体大小及排版居中。

③ 添加日期下拉输入控件，可直接复制“数据转化”表中已设置好的下拉控件。

④ 输入、设置通报正文，例如在B4单元格输入“一、用户规模”，在B5单元格输入“=数据转化!I18”，将新增用户数的柱形图复制到B5单元格所在行的下方，图表数据会随原图表数据的更新而更新。其他通报正文设置亦同此理，我就不再重复介绍，直接完成设置。

⑤ 可隐藏通报正文范围外无须用到的单元格区域，例如要隐藏I列及其以后的列，那么用鼠标选中I列，同时按下【Ctrl+Shift+→】三个键，并单击鼠标右键，选中【隐藏】即可，同理，还可隐藏无须用到的行。

⑥ 如果希望隐藏行、列标题，可通过【文件】→【选项】→【高级】，找到【显示行和列标题】的复选框，去除勾选即可。

最终通报正文效果如图所示。

数据提取自动化

实现从数据库取数，把数据结果追加至Excel相应表中，需要用SQL与VBA语句，主要实现以下几个功能。

★ 打开Access数据库。

★ 输入提取数据的日期。

★ 运行指定的提数SQL语句。

★ 将SQL语句运行的数据结果自动追加到Excel“数据源”表的新记录中。

相关的VBA语句编写如下：

Sub每日数据提取()

'声明定义VBA语句中需要使用到的各个变量类型

Dim AdoConn As New ADODB.Connection '定义变量AdoConn为连接数据库对象(ADODB是数据库访问组件，Connection是其中的一个对象)，用于实现连接数据库和关闭数据库连接等操作

Dim MyData As String '定义变量MyData为字符串型变量，用于数据库路径赋值

Dim N As Integer 定义变量N为数值型变量，用于Excel表行数赋值

Dim D1 As Date '定义变量D1为日期型变量，用于数据起始日期赋值

Dim D2 As Date '定义变量D2为日期型变量，用于数据结束日期赋值

' 定义SQL语句所需要的4个字符串型变量，可根据实际需要选择需要声明的变量个数

Dim strSQL1 As String

Dim strSQL2 As String

Dim strSQL3 As String

Dim strSQL4 As String

' InputBox函数的作用是打开输入对话框，提示输入提数日期，并赋值给D1，D2赋值D1+1

D1 = InputBox('请输入需要提数的日期，例如：2011-9-4', '提数日期')

D2 = D1 + 1

' 取第3列第1个空格单元格的行数，并赋值给N

N=ActiveSheet.Range('C1').End(xlDown).Row + 1

指定数据库，可根据实际情况替换双引号内数据库文件名，注意Access数据库文件与Excel文件必须在同一个路径下，如果不是，则须更改为MyData = 'D：\数据\业务数据库.accdb'，根据实际情况替换Access数据库文件

MyData =ThisWorkbook.Path & “\业务数据库.accdb'

建立数据库连接，打开刚才指定的数据库MyData

With AdoConn

.Provider=“Microsoft.ACE.OLEDB.12.0”

.Open MyData

End With

设置SQL语句，根据实际情况编写和替换

strSQL1= 'SELECT count(用户ID)FROM用户明细WHERE注册日期

册日期>=# '&D1& #'

strSQL2= 'SELECT count(用户ID)FROM(SELECT DISTINCT用户ID FROM订购明细WHERE

订购日期

strSQL3= 'SELECT count(订单编号),sum(订购金额)FROM订购明细WHERE订购日期

strSQL4= 'SELECT count(用户ID)FROM(SELECT DISTINCT用户ID FROM订购明细WHERE

订购日期

执行SQL查询，并将查询结果输出到当前表格相应位置，可根据实际情况在相应位置进行调整

ActiveSheet.Cells(N,3).CopyFromRecordset AdoConn.Execute(strSQL1)

ActiveSheet.Cells(N,4).CopyFromRecordset AdoConn.Execute(strSQL2)

ActiveSheet.Cells(N,5).CopyFromRecordset AdoConn.Execute(strSQL3)

ActiveSheet.Cells(N,7).CopyFromRecordset AdoConn.Execute(strSQL4)

关闭数据库连接

AdoConn.Close

释放变量

Set AdoConn = Nothing

MsgBox函数作用是打开输出对话框，以对话框形式提示“数据提取完毕!”

MsgBox “数据提取完毕!”

End Sub

VBA语句执行的步骤，如图所示。

★ 双引号之间的语句是VBA语句中的文本字符串。

★ 井号(#)主要在SQL查询语句中表示数据类型为日期型，通常在数据值两端加上井号(#)，这在讲Access数据库SQL语句查询时就介绍过。

★ 连接符(&)在VBA语句中，用于连接各个文本字符串，以组成一串所需的字符串，例如VBA语句中的strSQL1变量所赋值的SQL语句。因为需要根据输入的日期进行数据查询提取，VBA语句中涉及文本字符串与日期参数两种类型的文本。

如果将各个文本字符串与日期参数D1、D2直接连接，而没有进行类别分隔再连接，那么系统会将日期参数D1、D2识别为文本字符串D1、D2，而不是日期参数，这样就不能引用对话框输入的日期进行数据筛选查询，并且还会运行出错。

所以需要将一条完整的SQL语句拆分为各个文本字符串部分，再将各个文本字符串部分与日期参数D1、D2用连接符(&)进行连接组合，使其组合成一串所需的SQL语句，这就好比刚才介绍的“数据转化”表中的通报正文的组合一样，将各个文本字符串按需组合起来。

因为SQL语句要求各个关键字用空格符号分隔，所以在文本字符串与日期参数连接时注意空格符号的使用。