EXCEL服务器VBA编程接口

Excel服务器客户端组件是在安装目录下的一个文件----ESClient10.dll，它不是VBA程序，而是一个Com加载项，相当于对Excel附加了功能。Com载加项也是一种对象，我们可以在VBA程序中调用它提供的方法（编程接口）。

若要在VBA程序中调用Excel服务器的功能，首先需要声明对象，通过调用对象的方法来完成想要的功能，最后，还需要释放对象。

如下面的代码片断，第1行声明了一个变量对象oAdd，第2行设置该变量为Excel服务器客户端组件所对应的Com加载项。中间部分具体的操作，最后1行释放对象。

若要通过VBA程序操纵Excel服务器，开头的两行和最后的一行代码都是必需的，所不同的只是中间省略的部分。

Dim oAdd As Object

Set oAdd = Application.COMAddIns("ESClient10.Connect").Object

…….

             这里可以调用客户端组件的不同方法

…….

Set oAdd = Nothing

32.2.1 自动保存、连续输入

方法说明

saveCase方法，作用是保存当前正在填报的表单。该方法有五个参数：

参数1：要省略

参数2：布尔型，是否省略保存对话框，默认值为False，也即显示保存对话框。

参数3：布尔型，是否询问继续填写下一张，默认值为True，也即询问是否填下一张。

参数4：tempSave为布尔型 ，表示是否暂存True为暂存，False为保存。

参数5：errMsg 为string型， 表示若暂存出错后的报错。

返回值：布尔型，True表示保存成功，False表示保存失败

示例

我们通常用Excel服务器填报完成一张表单后，需要手工点击【保存】工具栏按钮，或选择“文件-保存”菜单，才能保存。对于有大量表单需连续输入的情况，这种做法需要键盘和鼠标交替操作，效率不高。

我们可以通过在Worksheet_selectionChange事件处理程序中调用saveCase方法，使得录入员只需要操作键盘，当数据录入完成，光标跳转到最后一个数据项出，自动保存，出现新的表单等待输入，过程中不需要使用鼠标。

假设有如下的模版，定义三个数据项：x、y和录入时间，其中录入时间有默认值，x和y需要输入。

我们希望录入员的辅助动作次数最少：

输入x，按回车，输入y，按回车，保存成功，提示是否填下一张，按回车（相当于回答【是】），出现新的一张空白表，光标自动回到C2处。

输入一张单据的过程中辅助动作只有三次回车，没有鼠标动作。下面看一下实现方式。

在模版的设计状态下，打开Visual Basic编辑器，在Worksheet_selectionChange事件处理程序中输入如下代码：（为讲解方便，加上了行号）

第1行，Worksheet_SelectionChange为事件处理程序名，它对应于单元区域被选中这一事件。这个事件处理程序有一个参数Target，它代表被选中的单元区域。

第2行，声明对象变量（必须这样写）

第5行，设置变量oAdd对应于Excel服务器客户端组件（必须这样写）

第7行，判断光标跳到的单元格是不是C4，如果不是，直接转到第16行（什么也没做）

第8行，（光标跳到了C4单元格），调用Excel服务器客户端组件提供的saveCase方法，实际保存当前已填好的表单，不弹出保存对话框，但保存后会询问是否继续填写。

第12行，（保存成功）光标跳转到C2处，等待继续输入。

第16行，释放对象（必须这样写）

本例在示例数据库中。

32.2.2 执行提数公式

方法

execQuery方法，作用是应用指定的提数公式，它只有一个参数，为字符串类型，其内容是一条或若干条表间公式的名称，若要执行的表间公式不止一条，彼此间需要用逗号分割（英文逗号）。

例如：

execQuery(“查询”)，表示要执行当前模版上定义的表间公式“查询”

execQuery(“查询,统计”)，表示要连续执行当前模版上定义的表间公式“查询”和“统计”。

示例

复习之前建立的“销售台帐”模板，我们定义了一个手动执行的表间公式“组合条件查询”。在模板上增加一个按钮，如图：

在按钮的Click事件处理程序中写如下代码：

第2、4行，声明对象变量、赋值（必须这样写）

第6行，光标放置到明细表的第1行

第7行，执行表间公式

第9行，释放对象（必须这样写）

32.2.3动态决定显示哪一种树型

方法

popTree(<树型数据规范的名称>)

在当前单元格弹出指定的树型数据规范

应用场合

如果某个数据项上需要通过树型选择输入，但是需要显示何种树型并不是固定的，而是根据不同的情况，显示不同的树型。

示例

有如下模板：

用户可以选择两种分类标准：按地区、按销售员。当分类标准＝“按地区”时，在“客户名称”处弹出“先显示地区，地区之下显示客户”的树型如下图

当分类标准＝“按销售员”时，在“客户名称”处弹出“先显示销售员，销售员之下显示客户”的树型，如下图：

为了达到上述目的，首先我们需要定义两种树型数据规范――－“地区_客户”和“销售员_客户”，其次，在模板的Selection_Change事件中写如下的VBA代码：

第2行，判断当前单元格是不是C3单元格，如果不是，什么也不做。如果是，继续执行下面的代码。

第4、5行，声明接口对象，并赋值。（必须这样写）

第7行，声明一个字符型变量nTreeName，用于存放应当显示的树型名称

第8~14行，根据C2单元格（分类标准）的内容，决定要显示的树型名称

第15行，决定了树型名称后，调用popTree方法，显示树型

第17行，释放对象。

32.2.4新建表单

新建Excel表单

方法

用VBA程序来新建一份表单，需要调用如下的接口方法：

NewReport(<模板名称>)或

NewReportById(<模板编号>)

利用NewReport方法，会针对指定模板建立一份空表单。如果需要在建立表单的同时为表单内容赋值，还需要在newReport之前调用addInitData方法：

AddInitData(<目的字段名>，<赋值表达式>，<赋值表达式类型>)

其中“赋值表达式”可以是本模板的字段名

“赋值表达式类型”：默认为True，此时赋值表达式为数据；为False时赋值表达式为字段名。

若模板名称改名但需要通过VBA新建表单则可用NewReportById方法。

示例

假设有如下模板，列举所有客户的信息

当点击【录入订单】按钮之后，会打开一份新订单，并且把当前选中（桔黄底色的那一行）的客户信息，自动传递到新订单上。

为了实现上述目的，我们需要做三件事。

首先，我们要识别当前客户是在哪一个。“当前客户”指的是光标所在行的客户。但是，当用户点击按钮的时候，输入焦点在按钮上，也就是说，在按钮的Click事件中无法读取当前行号，所以，需要在点击按钮之前，就把当前行号记录下来。我们的解决方案是，用J2单元格存放当前行号信息，在workSheet_SelectionChange事件中，把当前行号记录到J2单元格中去。也就是说，每当光标在工作表上移动，J2单元格总是随时反映当前的光标所在行号。

类似地，把当前行的客户编号记录到H2单元格，记录它的目的是定义条件格式，使得当前行的底色总是显示成桔黄色。

第三，在【录入订单】按钮的Click事件中，用VBA代码新建订单，并且把当前行的客户信息传递过去，代码如下：

第3行，确定当前行号r。

第6行，如果当前行的客户编号为空，说明当前行不是有效的客户信息，什么也不做，直接退出。

第9～10行，声明接口对象，并赋值。

第12～15行，指定要传递到新表单上的数据。

第17行，新建订单并打开，同时把12～15行指定的数据传递过去。

第19行，释放接口对象。

新建Word表单

方法

用VBA程序来新建一份word表单，需要调用如下的接口方法：
       NewWordReport(<模板名称>)或
       NewWordReportByNo(<模板编号>)

利用NewWordReport方法，会针对指定模板建立一份表单。如果在建立Word表单的同时需要对输入参数进行赋值，则需要在newWordReport之前调用AddWordParam方法：

AddWordParam([参数名称]，[参数值])

若模板名称改名但需要通过VBA新建Word表单，则可用NewWordReportByNo方法。

示例

假设有如下模板：

当点击【新建word】按钮之后，打开保存word文档窗口，选择保存文件名称和路径后显示新Word表单生成成功可打开编辑。打开的表单中参数赋值正确位置。
         为了实现上述效果，我们首先需要新建Word模板且设置了需要的输入参数（如“年份“、“提交日期”）和对参数赋值的提数公式。
         然后，在【录入订单】按钮的Click事件中，用VBA代码新建Word模板表单，并且把输入参数赋值到表单对应的数据项中，代码如下：

第4、5行，给输入参数赋值。
第7行，按Word模板名称新建Word表单。
第8行，按Word模板编号新建Word表单。
第10行，释放接口对象。
       

         32.2.5 执行回写公式

方法

execUpdate(<回写公式名称列表>)

执行指定的一条或多条回写公式

示例

假设有如下模板，列举所有客户的信息。

在这个模板上定义了一条回写公式，把表格中的客户信息更新到客户登记表中去。通常情况下，回写公式只是在保存的时候才执行，但是我们可以写一段VBA代码，让它在点击【修改客户信息】按钮后执行。

1 Private Sub cmdUpd_Click()

2    Dim oAdd As Object

3    Set oAdd = Application.COMAddIns("ESClient10.Connect").Object

4    If oAdd.execUpdate("更新客户信息") = True Then

5        MsgBox "客户信息已更新"

6    End If

7    Set oAdd = Nothing

8 End Sub

         32.2.6 其它程序员用VBA编程接口

以下一些编程接口，仅专业的软件开发人员利用Excel服务器做二次开发时才需要用到。有的需要使用者拥有编程相关的一些背景知识才能正确使用，非专业用户，一般不建议使用。

AddInitData

AddInitDAta(DestFieldName As String, SrcField)
           功能：调用NewReport前，使用此函数做数据初始化，将数据填充到新建的表单上。
         参数：

示例详见33.2.4。

AddLink

AddLink(<fileName>,[sh],[r],[c])

功能：向当前表单的当前单元格（或指定单元格）上加入附件

返回值：无

参数：

AddPicture

AddPicture(<fileName>,[sh],[r],[c])

功能：向当前表单的当前单元格（或指定单元格）上插入图片

返回值：无

参数：

调用举例：

AddPicture "c:\users\may\desktop\photo\koala.jpg", 1,3,6

表示在当前工作簿的第1个sheet 的第3行第6列中插入路径“c:\users\may\desktop\photo”下的koala.jpg图片。

deleteRow

deleteRow(<sheetId>,<base>,<num>)

功能：在明细表中删除行

参数：

返回值：无

调用举例：

deleteRow(2,3,4)，表示在当前工作簿的第2个sheet上，从第3行开始，删除以下的4个行（即所删除的行是3,4,5,6，四行）

deleteColumn

deleteColumn(<sheetId>,<base>,<num>)

功能：在明细表中删除列

参数说明：同deleteRow

ExecProc

ExecProc(<ProcName>,<errMsg>,<Args1>,<Args2>,…<Args15>) As Boolean

功能：执行不返回纪录集的存储过程

返回值：

True 成功

False 出错

参数：

调用举例：

ExecProc("p_NoReturn", sErr, ActiveSheet.Range("D2"), ActiveSheet.Range("D3"))

完整示例在示例数据库中。执行当前应用数据库中一个名为“p_NoReturn”的存储过程，该存储过程需要两个参数，分别是 D2 和 D3 单元格的值，如果该存储过程执行出错，错误信息会赋值到变量 sErr。

ExecQryProc

ExecQryProc(<ProcName>,<rset>,<errMsg>,<Args1>,<Args2>,…<Args15>) As Boolean

功能：执行返回纪录集的存储过程

返回值：

True 成功

False 出错

参数：

调用举例：

ExecQryProc("p_withReturn", oRs, sErr, ActiveSheet.Range("D2"), ActiveSheet.Range("D3"))

完整示例在示例数据库中。执行当前应用数据库中一个名为 p_withReturn 的存储过程，该存储过程需要两个参数，分别用D2 和 D3 单元格的内容给这两个参数赋值；该存储过程是一个查询，查询的结果会写到记录集 oRs 中；如果执行出错，错误信息会赋值到变量 sErr 中。

外部数据源中的存储过程（目前可操作存储过程的外部数据源都是SQL SERVER，其他数据库待测试）

●执行本地外部数据源中的存储过程：

调用举例：

ExecQryProc("ESApp1.dbo.p_withReturn",oRs,sErr,ActiveSheet.Range("D2"), ActiveSheet.Range("D3"))

注意：存储过程名要完整，要写明数据库名如：“ESApp1.dbo.p_withReturn”。

●执行的存储过程为非本地外部数据源：

调用举例：

ExecQryProc("ABC.ESApp1.dbo.p_withReturn", oRs, sErr, ActiveSheet.Range("D2"), ActiveSheet.Range("D3"))

注意：存储过程名要完整，要写明数据库名、链接服务器名（链接服务器名若为：“ABC”）写完整则为：“ABC.ESApp1.dbo.p_withReturn”。

前提准备：需要开启外部数据源的“RPC”和“RPC OUT”，设置为”True”。

开启方法1（图形）：

1. 登录SQL数据库管理台后，选择对应非本地外部数据源的链接服务器（如：“ABC”）

2. 点击右键，点击属性。

3. 打开如下图所示窗口，按图所示进行设置。

开启方法2（语句）：

EXEC master.dbo.sp_serveroption @server=N'链接服务器名', @optname=N'rpc', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'链接服务器名', @optname=N'rpc.out', @optvalue=N'true'

GO

GetByDBFunction

GetByDBFunction(<funcName>,<rset>,<errMsg><p1>,<p2>,…<pn>)

功能：执行指定的数据库函数

返回值：

True 成功

False 出错

参数：

GetLockState

功能：获取当前表单的锁定状态

参数：无

返回值：

True 已锁定

False 未锁定

GetFieldAddress

GetFieldAddress(<fieldName>,<address>,<startRow>,<startCol>,<endRow>,<endCol>)

功能：返回当前模板上某字段的地址及行列范围

返回值：

True 成功

False 出错

参数：

GetInstallPath

功能：返回Excel服务器软件在本计算机的安装路径

参数：无

返回值：String类型，为安装路径

GetState

Getstate(byref bookType as integer,byref readOnly as boolean) as Boolean

功能：返回当前的Excel工作簿的性质

返回值：

True 调用成功

False 调用失败

参数：

insertRow

insertRow(<sheetId>,<base>,<num>)

功能：向明细表中增加行

参数：

返回值：无

调用举例：

             insertRow(2,3,4)，表示在当前工作簿的第2个sheet上，在第3行之上，增加4个新行

insertColumn

insertColumn(<sheetId>,<base>,<num>)

功能：向明细表中增加列

参数说明：同insertRow

IsNotCaseBook

功能：

判断当前工作簿是不是表单。Excel中的任何工作簿，都是三种情况之一：被打开的模板、正填报的表单、普通的Excel文件。可能用户在模板上，会写一些VBA代码，但是希望这些VBA代码只是在填报状态下执行，模板设计下不要执行，为了做到这一点，可以在VBA代码的开头，用此函数判断当前工作簿是不是表单，不是表单，则直接返回。

返回值：布尔型。若当前工作簿不是表单，返回真，否则返回假。

参数：无

PrintReport

功能：打印当前表单

参数：

调用举例：

PrintReport：用本模板上定义的自定义打印格式，打印当前表单。
PrintReport(true)：用Excel自身的打印功能，打印当前表单。
PrintReport(false)：用本模板上定义的自定义打印格式，打印当前表单。

ProtectBook

功能：

对当前表单加保护。若当前工作簿不是表单，本函数不起作用。通常和AddRow等函数一起使用。

返回值：布尔型，成功为真，出错为假。

参数：无

ReturnWi

ReturnWi(errMsg)

功能：退回任务

参数：

返回值：布尔型，成功为真，出错为假

SaveLocal

SaveLocal 保存文件的全路径

功能：表单保存至

示例：oAdd.SaveLocal "d:\文件名.xls"

说明：不输入参数时则自动弹出“另存为”窗口。

ShowWorkBench

ShowWorkBenc（optional byval tmpName as string ="",optional byval nodeType as integer=0)

功能：打开【我的工作台】并按参数内容定位到指定的位置。

参数：

示例：oAdd.ShowWorkBench "订单", 0

说明：不输入参数时则定位到最后一次操作我的工作台位置。

UnProtectBook

功能：对当前表单解加保护。若当前工作簿不是表单，本函数不起作用。通常和AddRow等函数一起使用。

返回值：布尔型，成功为真，出错为假。

参数：无

SaveLocal
           

ShowWorkBench
           

示例：oAdd.ShowWorkBench "订单", 0
       说明：不输入参数时则定位到最后一次操作我的工作台位置。

UnProtectBook