Excel VBA常用技巧 第03章 Wordbook对象Word下载.docx

Excel VBA常用技巧 第03章 Wordbook对象Word下载.docx

《Excel VBA常用技巧 第03章 Wordbook对象Word下载.docx》由会员分享，可在线阅读，更多相关《Excel VBA常用技巧 第03章 Wordbook对象Word下载.docx（29页珍藏版）》请在冰豆网上搜索。

VBA中，在不同的工作簿之间转换需要指定引用的工作簿，通常有下面几种方法。

1-1使用工作簿的名称

工作簿名称是指Excel文件的文件名，可以使用Workbooks集合引用方式来引用工作簿，如下面的代码所示。

#001SubWbPath（）

#002MsgBox"

名称为：

"

&

Workbooks（"

工作簿的引用方法.xls"

）.Path

#003EndSub

代码解析：

WbPath过程显示工作簿“工作簿的引用方法”的路径。

应用于Workbook对象的Path属性将完整路径返回给应用程序，语法如下：

expression.Path

参数expression是必需的，一个有效的对象。

运行WbPath过程结果如图401所示。

图401返回工作簿完整路径

1-2使用工作簿的索引号

工作簿索引号是指工作簿打开的顺序，Excel根据工作簿打开的顺序以1开始进行编号。

下面的代码显示应用程序打开的第一个工作簿的名称。

#001SubWbName（）

第一个打开的工作簿名字为：

Workbooks

（1）.Name

WbName过程显示应用程序打开的第一个工作簿的名称。

应用于Workbook对象的Name属性返回对象的名称，语法如下：

expression.Name

运行WbName过程结果如图402所示。

图402返回工作簿名称

如果需要返回包含完整路径的工作簿名称则使用Workbook对象的FullName属性，如下面的代码所示。

#001SubWbFullName（）

包括完整路径的工作簿名称为：

Workbooks

（1）.FullName

WbFullName过程显示应用程序打开的第一个工作簿的完整路径和名称。

FullName属性返回对象的名称，包括其磁盘路径的字符串，此属性等价于在Path属性后加上当前文件系统的分隔符，然后加上Name属性。

运行WbFullName过程结果如图403所示。

图403返回包含完整路径的工作簿名称

1-3使用ThisWorkbook

使用ThisWorkbook代表当前宏代码运行的工作簿，如下面的代码所示。

#001SubWbClose（）

#002ThisWorkbook.CloseSaveChanges:

=False

WbThis过程使用Close方法关闭当前宏代码运行的工作簿，不保存对工作簿的任何更改。

关于应用于Workbook对象的Close方法请参阅技巧45-1。

注意本属性仅可在MicrosoftExcel内使用。

不能使用此属性访问任何其他应用程序的工作簿。

1-4使用ActiveWorkbook

使用ActiveWorkbook代表活动窗口（最上面的窗口）的工作簿，如下面的代码所示。

#001SubWbActive（）

当前活动工作簿名字为：

ActiveWorkbook.Name

WbActive过程显示活动工作簿的名称，ActiveWorkbook属性返回一个Workbook对象，该对象代表活动窗口（最上面的窗口）的工作簿。

如果没有打开任何窗口或者活动窗口为信息窗口或剪贴板窗口，则返回Nothing。

运行WbActive过程结果如图404所示。

图404返回活动工作簿名称

技巧2新建工作簿文件

在VBA中使用Add方法新建工作簿，如下面的代码所示。

#001SubAddNowbook（）

#002DimNowbookAsWorkbook

#003DimShNameAsVariant

#004DimArrAsVariant

#005DimiAsInteger

#006DimmyNewWorkbookAsInteger

#007myNewWorkbook=Application.SheetsInNewWorkbook

#008ShName=Array（"

余额"

"

单价"

数量"

金额"

）

#009Arr=Array（"

01月"

02月"

03月"

04月"

05月"

06月"

07月"

08月"

09月"

10月"

11月"

12月"

#010Application.SheetsInNewWorkbook=4

#011SetNowbook=Workbooks.Add

#012WithNowbook

#013Fori=1To4

#014With.Sheets（i）

#015.Name=ShName（i-1）

#016.Range（"

B1"

）.Resize（1,UBound（Arr）+1）=Arr

#017.Range（"

A2"

）="

品名"

#018EndWith

#019Next

#020.SaveAsFilename:

=ThisWorkbook.Path&

"

\"

存货明细.xls"

#021.CloseSavechanges:

=True

#022EndWith

#023SetNowbook=Nothing

#024Application.SheetsInNewWorkbook=myNewWorkbook

#025EndSub

AddNowbook过程使用Add方法建立新的工作簿并对新建工作簿进行操作。

第2行到第6行代码声明变量类型。

第7行代码保存Excel自动插入到新工作簿中的工作表数目。

第8、9行代码将数组元素赋值给变量。

第10行代码将Application对象的SheetsInNewWorkbook属性设置为4，在新建工作簿时插入4张工作表。

第11行代码使用Add方法建立新的工作簿，应用于Workbooks对象的Add方法新建工作簿，新建的工作簿将成为活动工作簿。

第12行到第22行代码操作新建工作簿。

其中第15行到第17行代码将新建工作簿的工作表进行重命名并给单元格赋值。

第20行代码使用SaveAs方法将新建工作簿重命名为“存货明细.xls”保存在同一目录中。

关于SaveAs方法请参阅技巧47-2。

第21行代码使用Close方法关闭工作簿。

关于Close方法请参阅技巧45-1。

第24行代码恢复工作簿的默认设置。

运行AddNowbook过程将在工作簿同一目录中新建“存货明细.xls”工作簿，新建工作簿格式如图411所示。

图411新建“存货明细.xls”工作簿格式

注意本例中没有考虑工作簿同名因素，如果目录中已有“存货明细.xls”工作簿，运行时会显示如图412所示的对话框，选择“是”即可，否则将会出错。

图412同名提示

技巧3打开指定的工作簿

VBA中使用Open方法打开一个工作簿，如下面的代码所示。

#001SubOpenfile（）

#002DimxAsInteger

#003Forx=1ToWorkbooks.Count

#004IfWorkbooks（x）.Name="

123.xls"

Then

#005MsgBox"

123"

工作簿已经打开!

#006ExitSub

#007EndIf

#008Next

#009Workbooks.OpenThisWorkbook.Path&

\123.xls"

#010EndSub

Openfile过程打开同一目录中的“123”工作簿。

第3行代码利用Workbook对象的Count属性取得打开工作簿的数目，使用For...Next语句遍历所有打开的工作簿。

遍历工作簿除了使用For...Next语句外还可以使用For...Each...Next语句来遍历Workbook对象集合中的所有元素。

第4行到第8行代码遍历所有打开的工作簿，如果Workbook对象集合中存在“123”工作簿，说明“123”工作簿已打开，则显示一条如图421所示的提示信息。

图421工作簿已打开提示

第9行代码如果“123”工作簿没有被打开则使用Open方法打开“123”工作簿。

Open方法应用于Workbooks对象时打开一个工作簿，语法如下：

expression.Open（FileName,UpdateLinks,ReadOnly,Format,Password,WriteResPassword,IgnoreReadOnlyRecommended,Origin,Delimiter,Editable,Notify,Converter,AddToMru,Local,CorruptLoad）

参数expression是必需的，返回一个Workbooks对象

参数FileName是必需的，要打开的工作簿的文件名。

参数UpdateLinks是可选的，指定文件中链接的更新方式。

如果省略本参数，则提示用户选择链接的更新方式。

否则，该参数的取值应为表格421中的某个值。

值

描述

不更新任何引用

1

更新外部引用，但不更新远程引用

2

更新远程引用，但不更新外部引用

3

同时更新远程引用和外部引用

表格421UpdateLinks参数值

参数ReadOnly是可选的，如果该值为True，则以只读模式打开工作簿。

参数Format是可选的，如果MicrosoftExcel正在打开一个文本文件，则该参数用于指定分隔字符，如表格422所示。

如果省略本参数，则使用当前的分隔符。

分隔符

制表符

逗号

空格

4

分号

5

没有分隔符

6

自定义字符（请参阅Delimiter参数）

表格422Format参数值

参数Password是可选的，该字符串指定打开一个受保护工作簿的密码。

如果省略该参数并且指定工作簿已设置密码，则提示用户输入密码。

参数WriteResPassword是可选的，该字符串为一个写保护工作簿的写入权密码。

参数IgnoreReadOnlyRecommended是可选的，如果该值为True，则设置MicrosoftExcel不显示建议只读消息（如果该工作簿以“建议只读”选项保存）。

参数Origin是可选的，如果文件为文本文件，则该参数用于指示该文件来源于何种操作系统。

参数Delimiter是可选的，如果该文件为文本文件并且Format参数为6，则此参数用于指定用作分隔符的字符。

参数Editable是可选的，如果该文件为MicrosoftExcel4.0加载宏，则该参数的值为True时可打开该加载宏以便在窗口中看到。

如果该参数的值为False或者省略该参数，则该加载宏以隐藏方式打开，并且无法设为可见。

参数Notify是可选的，当该文件不能以可读写模式打开时，如果该参数的值为True，则可将该文件添加到文件通知列表。

参数Converter是可选的，打开文件时试用的第一个文件转换器的索引号。

参数AddToMru是可选的，如果该值为True，则将该工作簿添加到最近使用的文件列表中。

默认值为False。

参数Local是可选的，如果该值为True，则以MicrosoftExcel（包括控制面版设置）的语言保存文件。

如果该值为False（默认值），则以VisualBasicforApplications（VBA）的语言保存文件，其中VisualBasicforApplications（VBA）为典型安装的美国英语版本，除非VBA项目的Workbooks.Open来自旧的国际化的XL5/95VBA项目。

参数CorruptLoad是可选的，可为以下常量之一：

xlNormalLoad、xlRepairFile和xlExtractData。

如果未指定任何值，则默认值通常为普通状态。

技巧4判断指定工作簿是否打开

4-1遍历Workbooks集合方法

通过遍历当前应用程序所有已打开的工作簿文件（Workbooks集合），判断指定名称的工作簿是否打开，如下面的代码所示。

#001SubWorkbookIsOpen_1（）

#002DimWbAsWorkbook

#003DimmyWbAsString

#004myWb="

ExcelHome.xls"

#005ForEachWbInWorkbooks

#006IfWb.Name=myWbThen

#007MsgBox"

工作簿"

myWb&

已经被打开!

#008ExitSub

#009EndIf

#010Next

#011MsgBox"

没有被打开!

#012EndSub

WorkbookIsOpen_1过程通过遍历当前应用程序中所有已打开的工作簿文件（Workbooks集合），判断“ExcelHome”工作簿是否打开。

第5行代码使用For...Each...Next语句来遍历Workbook对象集合中的所有元素。

第6行到第8行代码如果Workbook对象集合包含“ExcelHome.xls”工作簿名称，说明文件已打开，使用ExitSub语句结束代码的运行。

第11行代码如果运行到此行代码说明“ExcelHome.xls”工作簿没有被打开。

4-2错误处理方法

使用错误处理程序判断指定名称的工作簿是否打开，如下面的代码所示。

#001SubWorkbookIsOpen_2（）

#005Err.Clear

#006OnErrorGoToline

#007SetWb=Application.Workbooks（myWb）

#008MsgBox"

#009SetWb=Nothing

#010ExitSub

#011line:

#012MsgBox"

#013SetWb=Nothing

#014EndSub

WorkbookIsOpen_2过程使用错误处理程序判断“ExcelHome”工作簿是否打开。

第5行代码使用Clear方法清除Err对象的所有属性设置。

第6行代启动错误处理程序，如果第7行代码发生错误则执行line行后面的代码。

第7行代码使用Set语句将Workbook对象引用赋给变量Wb，如果“ExcelHome.xls”工作簿没有被打开将发生下标越界错误，此时执行第12、13行代码，否则执行第8、9行代码。

技巧5禁用宏则关闭工作簿

通常情况下，当应用程序的宏安全性的安全级别设置为“中”时，打开包含MicrosoftExcel4.0版的宏的工作簿，将显示如图441所示的“安全警告”对话框。

图441安全警告对话框

如果用户选择“禁用宏”按钮，则会显示如图442所示的警告消息框，当用户选择“否”时，不能打开该工作簿；

用户选择“是”时，打开该工作簿，但VBA宏被禁止，而MicrosoftExcel4.0版的宏未被禁止。

图442MicrosoftExcel4.0宏警告对话框

我们可以利用禁用VBA宏不能禁止MicrosoftExcel4.0版的宏这个特点，使用MicrosoftExcel4.0版的宏来实现禁用宏则关闭工作簿的功能。

步骤1新建或打开需要添加此项功能的工作簿文件。

步骤2按<

Ctrl+F11>

组合键为工作簿添加一个宏表，添加的宏表名称默认为“Macro1”。

步骤3在宏表“Macro1”的A1至A7单元格中输入下面的内容。

#001禁用宏则关闭工作簿

#002=ERROR（FALSE）

#003=IF（ERROR.TYPE（RUN（"

TestMacro"

））=4）

#004=ALERT（"

因禁用了宏功能,文件将被关闭!

3）

#005=FILE.CLOSE（FALSE）

#006=END.IF（）

#007=RETURN（）

完成后的宏表如图443所示。

图443完成输入后的宏表

MicrosoftExcel4.0宏函数以等号（=）开始，其他不是由等号开始的内容将被视作注释。

通常用作定义的宏名称或者作为宏函数实现功能的注释内容设置为斜体字样以示区别，如图443中单元格A1所示。

第2行代码关闭错误检查功能。

如果关闭错误检查，那么当宏执行遇到错误时，MicrosoftExcel将不予理会而继续执行。

第3行到第6行代码使用If函数与End.If函数构成条件判断语句。

其中，第3行中的语句通过检查宏函数RUN（"

）的返回错误类型是否为4（禁用宏时的返回结果），判断工作簿是否禁用了宏功能。

如果第3行的结果为True，则执行下面的语句。

在第4、5行代码，插入几个空格来表示相关代码之间的层次结构。

第4行中的代码显示一个消息框。

第5行中的代码关闭当前活动工作簿，设置参数值为Fasle表示关闭时工作簿时不保存对其所作的更改。

第7行代码终止当前代码的执行。

MicrosoftExcel4.0宏要求每个宏必须使用RETURN或HALT函数结束。

步骤4为每个表添加工作表级别的名称“Auto_Activate”，并将引用都指向宏表“Macro1”的A2单元格。

“Auto_Activate”是一个自动宏，表被激活时自动执行。

添加工作表级别的名称的方法如下：

选择一张工作表，假设为表“Sheet1”，单击菜单“插入”→“名称”→“定义名称”。

在“定义名称”对话框中添加名称，如图444所示。

图444定义工作表级别的名称

输入完成后单击“确定”按钮，完成一张工作表的“Auto_Activate”的定义。

完成定义后的名称将在“定义名称”对话框中显示，如图445所示。

依次为每个表添加“Auto_Activate”名称。

图445名称对话框中的工作表级名称

此外，使用VBA也可以实现同样的操作，并且使用VBA的好处是能够隐藏名称，以避免名称被删除或修改。

代码如下：

#001SubAddPrivateNames（）

#002DimshtAsObject

#003ForEachshtInSheets

#004ThisWorkbook.Names.Addsht.Name&

!

Auto_Activate"

_

=Macro1!

$A$2"

False

#005Next

#006EndSub

步骤5运行下面的代码，隐藏宏表工作表：

#001SubHideMacroSheet（）

#002ThisWorkbook.Excel4MacroSheets

（1）.Visible=xlSheetHidden

步骤6保存工作簿。

当应用程序的宏安全性的安全级设置为“中”时，如果用户打开该工作簿文件并选择“禁用宏”，将显示如图442所示的警告消息框。

当用户选择“是”时，活动工作表上的自动宏“Auto_Activate”将被执行，执行结果显示如图446所示的消息框，当用户选择“确定”按钮后，将强制关闭该工作簿文件。

图446警告消息框

技巧6关闭工作簿不显示保存对话框

当用户更改工作簿后，没有进行保存操作而直接关闭工作簿时，将显示如图451所示的消息框，提示用户是否保存对工作簿的更改，如果希望不显示该消息框而直接关闭关闭工作簿，可以在关闭时进行相应的设置。

图451提示保存对话框

6-1使用Close方法关闭工作簿

使用Close方法关闭工作簿的，可以在Close方法中指定相应的参数，如下面的代码所示。

#001SubwbClose_1（）

wbClose_1过程使用Close方法关闭工作簿，并放弃所有对工作簿的更改。

应用于Workbook对象的Close方法关闭对象，语法如下：

expression.Close（SaveChanges,Filename,RouteWorkbook）

其中SaveChanges参数是可选的，如果工作簿没有改变则忽略此参数；

如果工作簿发生了改变并且在另外的窗口中也打开了该工作簿，则仍然忽略此参数；

如果工作簿发生了改变并且没有在另外的窗口中打开，则此参数将指定是否在工作簿中保存所发生的更改。

取值与操作如表格451所示：

作用

True

将改变保存到工作簿。

如果该工作簿尚未命名，则使用FileName指定的名称。

如果省略FileName参数，则要求用户输入文件名。

False

不将改变保存到此文件。

省略

显示一个对话框，要求用户决定是否保存所做的更改。

表格451SaveChanges参数值的作用

如果希望在关闭工作簿时自动保存更改