ExcelVBA常用技巧第03章Wordbook对象.docx

《ExcelVBA常用技巧第03章Wordbook对象.docx》由会员分享，可在线阅读，更多相关《ExcelVBA常用技巧第03章Wordbook对象.docx（35页珍藏版）》请在三一办公上搜索。

1、ExcelVBA常用技巧第03章Wordbook对象VBA常用技巧代码解析 VBA常用技巧 目录 VBA常用技巧 . 1 第3章 Wordbook对象 . 2 技巧1 工作簿的引用方法 . 2 1-1 使用工作簿的名称 . 2 1-2 使用工作簿的索引号 . 2 1-3 使用ThisWorkbook . 3 1-4 使用ActiveWorkbook . 4 技巧2 新建工作簿文件 . 4 技巧3 打开指定的工作簿 . 6 技巧4 判断指定工作簿是否打开 . 9 4-1 遍历Workbooks集合方法 . 9 4-2 错误处理方法 . 10 技巧5 禁用宏则关闭工作簿 . 10 技巧6 关闭工作

2、簿不显示保存对话框 . 14 6-1 使用Close方法关闭工作簿 . 14 6-2 单击工作簿关闭按钮关闭工作簿 . 16 技巧7 禁用工作簿的关闭按钮 . 16 技巧8 保存工作簿的方法 . 18 8-1 使用Save方法 . 18 8-2 直接保存为另一文件名 . 18 8-3 保存工作簿副本 . 18 技巧9 保存指定工作表为工作簿文件 . 19 技巧10 打印预览时不触发事件 . 21 技巧11 设置工作簿文档属性信息 . 23 技巧12 不打开工作簿取得其他工作簿数据 . 24 12-1 使用公式 . 24 12-2 使用GetObject函数 . 25 12-3 隐藏Applic

3、ation对象 . 26 12-4 使用ExecuteExcel4Macro方法 . 27 12-5 使用SQL连接 . 28 技巧13 返回窗口的可视区域地址 . 29 1 VBA常用技巧代码解析 第3章 Wordbook对象 技巧1 工作簿的引用方法 VBA中，在不同的工作簿之间转换需要指定引用的工作簿，通常有下面几种方法。 1-1 使用工作簿的名称 工作簿名称是指Excel文件的文件名，可以使用Workbooks集合引用方式来引用工作簿，如下面的代码所示。 #001 Sub WbPath #002 MsgBox 名称为： & Workbooks(工作簿的引用方法.xls).Path #0

4、03 End Sub 代码解析： WbPath过程显示工作簿“工作簿的引用方法”的路径。应用于Workbook对象的Path属性将完整路径返回给应用程序，语法如下： expression.Path 参数expression是必需的，一个有效的对象。 运行WbPath过程结果如图 1-1所示。 图 1-1 返回工作簿完整路径 1-2 使用工作簿的索引号 工作簿索引号是指工作簿打开的顺序，Excel根据工作簿打开的顺序以1开始进行编号。下面的代码显示应用程序打开的第一个工作簿的名称。 2 VBA常用技巧代码解析 #001 Sub WbName #002 MsgBox 第一个打开的工作簿名字为： &

5、 Workbooks(1).Name #003 End Sub 代码解析： WbName过程显示应用程序打开的第一个工作簿的名称。应用于Workbook对象的Name属性返回对象的名称，语法如下： expression.Name 参数expression是必需的，一个有效的对象。 运行WbName过程结果如图 1-2所示。 图 1-2 返回工作簿名称 如果需要返回包含完整路径的工作簿名称则使用Workbook对象的FullName属性，如下面的代码所示。 #001 Sub WbFullName #002 MsgBox 包括完整路径的工作簿名称为： & Workbooks(1).FullName

6、 #003 End Sub WbFullName过程显示应用程序打开的第一个工作簿的完整路径和名称。FullName属性返回对象的名称，包括其磁盘路径的字符串，此属性等价于在Path属性后加上当前文件系统的分隔符，然后加上Name属性。 运行WbFullName过程结果如图 1-3所示。 图 1-3 返回包含完整路径的工作簿名称 1-3 使用ThisWorkbook 使用ThisWorkbook代表当前宏代码运行的工作簿，如下面的代码所示。 #001 Sub WbClose #002 ThisWorkbook.Close SaveChanges:=False 3 VBA常用技巧代码解析 #00

7、3 End Sub 代码解析： WbThis过程使用Close方法关闭当前宏代码运行的工作簿，不保存对工作簿的任何更改。关于应用于Workbook对象的Close方法请参阅技巧6-1。 注意 本属性仅可在 Microsoft Excel内使用。不能使用此属性访问任何其他应用程序的工作簿。 1-4 使用ActiveWorkbook 使用ActiveWorkbook代表活动窗口的工作簿，如下面的代码所示。 #001 Sub WbActive #002 MsgBox 当前活动工作簿名字为： & ActiveWorkbook.Name #003 End Sub 代码解析： WbActive过程显示活动

8、工作簿的名称，ActiveWorkbook属性返回一个Workbook对象，该对象代表活动窗口的工作簿。如果没有打开任何窗口或者活动窗口为信息窗口或剪贴板窗口，则返回 Nothing。 运行WbActive过程结果如图 1-4所示。 图 1-4 返回活动工作簿名称 技巧2 新建工作簿文件 在VBA中使用Add方法新建工作簿，如下面的代码所示。 #001 Sub AddNowbook #002 Dim Nowbook As Workbook #003 Dim ShName As Variant 4 VBA常用技巧代码解析 #004 Dim Arr As Variant #005 Dim i As

9、 Integer #006 Dim myNewWorkbook As Integer #007 myNewWorkbook = Application.SheetsInNewWorkbook #008 ShName = Array(余额, 单价, 数量, 金额) #009 Arr = Array(01月, 02月, 03月, 04月, 05月, 06月, 07月, 08月, 09月, 10月, 11月, 12月) #010 Application.SheetsInNewWorkbook = 4 #011 Set Nowbook = Workbooks.Add #012 With Nowbook

10、 #013 For i = 1 To 4 #014 With .Sheets(i) #015 .Name = ShName(i - 1) #016 .Range(B1).Resize(1, UBound(Arr) + 1) = Arr #017 .Range(A2) = 品名 #018 End With #019 Next #020 .SaveAs Filename:=ThisWorkbook.Path & & 存货明细.xls #021 .Close Savechanges:=True #022 End With #023 Set Nowbook = Nothing #024 Applica

11、tion.SheetsInNewWorkbook = myNewWorkbook #025 End Sub 代码解析： AddNowbook过程使用Add方法建立新的工作簿并对新建工作簿进行操作。 第2行到第6行代码声明变量类型。 第7行代码保存Excel自动插入到新工作簿中的工作表数目。 第8、9行代码将数组元素赋值给变量。 第10行代码将Application对象的SheetsInNewWorkbook属性设置为4，在新建工作簿时插入4张工作表。 第11行代码使用Add方法建立新的工作簿，应用于Workbooks对象的Add方法新建工作簿，新建的工作簿将成为活动工作簿。 5 VBA常用技巧

12、代码解析 第12行到第22行代码操作新建工作簿。其中第15行到第17行代码将新建工作簿的工作表进行重命名并给单元格赋值。第20行代码使用SaveAs方法将新建工作簿重命名为“存货明细.xls”保存在同一目录中。关于SaveAs方法请参阅技巧8-2。第21行代码使用Close方法关闭工作簿。关于Close方法请参阅技巧6-1。 第24行代码恢复工作簿的默认设置。 运行AddNowbook过程将在工作簿同一目录中新建“存货明细.xls”工作簿，新建工作簿格式如图 2-1所示。 图 2-1 新建“存货明细.xls”工作簿格式 注意 本例中没有考虑工作簿同名因素，如果目录中已有“存货明细.xls”工作

13、簿，运行时会显示如图 2-2所示的对话框，选择“是”即可，否则将会出错。 图 2-2 同名提示 技巧3 打开指定的工作簿 VBA中使用Open方法打开一个工作簿，如下面的代码所示。 #001 Sub Openfile #002 Dim x As Integer #003 For x = 1 To Workbooks.Count 6 VBA常用技巧代码解析 #004 If Workbooks(x).Name = 123.xls Then #005 MsgBox 123工作簿已经打开! #006 Exit Sub #007 End If #008 Next #009 Workbooks.Open

14、ThisWorkbook.Path & 123.xls #010 End Sub 代码解析： Openfile过程打开同一目录中的“123”工作簿。 第3行代码利用Workbook对象的Count属性取得打开工作簿的数目，使用For.Next 语句遍历所有打开的工作簿。遍历工作簿除了使用For.Next 语句外还可以使用For.Each.Next语句来遍历Workbook对象集合中的所有元素。 第4行到第8行代码遍历所有打开的工作簿，如果Workbook对象集合中存在“123”工作簿，说明“123”工作簿已打开，则显示一条如图 3-1所示的提示信息。 图 3-1 工作簿已打开提示 第9行代码如

15、果“123”工作簿没有被打开则使用Open方法打开“123”工作簿。 Open方法应用于Workbooks 对象时打开一个工作簿，语法如下： expression.Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad) 参数expression是必需的，返回一个Workbooks对象 参数File

16、Name是必需的，要打开的工作簿的文件名。 参数UpdateLinks是可选的，指定文件中链接的更新方式。如果省略本参数，则提示用户选择链接的更新方式。否则，该参数的取值应为表格 3-1中的某个值。 值 0 1 2 3 描述 不更新任何引用 更新外部引用，但不更新远程引用 更新远程引用，但不更新外部引用 同时更新远程引用和外部引用 7 VBA常用技巧代码解析 表格 3-1 UpdateLinks参数值 参数ReadOnly是可选的，如果该值为True，则以只读模式打开工作簿。 参数Format是可选的，如果Microsoft Excel正在打开一个文本文件，则该参数用于指定分隔字符，如表格 3

17、-2所示。如果省略本参数，则使用当前的分隔符。 值 1 2 3 4 5 6 分隔符 制表符 逗号 空格 分号 没有分隔符 自定义字符(请参阅 Delimiter 参数) 表格 3-2 Format参数值 参数Password是可选的，该字符串指定打开一个受保护工作簿的密码。如果省略该参数并且指定工作簿已设置密码，则提示用户输入密码。 参数WriteResPassword是可选的，该字符串为一个写保护工作簿的写入权密码。如果省略该参数并且指定工作簿已设置密码，则提示用户输入密码。 参数IgnoreReadOnlyRecommended是可选的，如果该值为True，则设置Microsoft Exc

18、el不显示建议只读消息。 参数Origin是可选的，如果文件为文本文件，则该参数用于指示该文件来源于何种操作系统。 参数Delimiter是可选的，如果该文件为文本文件并且Format参数为 6，则此参数用于指定用作分隔符的字符。 参数Editable是可选的，如果该文件为Microsoft Excel 4.0加载宏，则该参数的值为True时可打开该加载宏以便在窗口中看到。如果该参数的值为False或者省略该参数，则该加载宏以隐藏方式打开，并且无法设为可见。 参数Notify是可选的，当该文件不能以可读写模式打开时，如果该参数的值为True，则可将该文件添加到文件通知列表。 参数Convert

19、er是可选的，打开文件时试用的第一个文件转换器的索引号。 参数AddToMru是可选的，如果该值为True，则将该工作簿添加到最近使用的文件列表中。默认值为False。 参数Local是可选的，如果该值为True，则以Microsoft Excel的语言保存文件。如果该值为False，则以 Visual Basic for Applications (VBA)的语言保存文件，其中Visual Basic for Applications (VBA)为典型安装的美国英语版本，除非VBA项目的Workbooks.Open来自旧的国际化的XL5/95 VBA项目。 8 VBA常用技巧代码解析 参数C

20、orruptLoad是可选的，可为以下常量之一：xlNormalLoad、xlRepairFile 和 xlExtractData。如果未指定任何值，则默认值通常为普通状态。 技巧4 判断指定工作簿是否打开 4-1 遍历Workbooks集合方法 通过遍历当前应用程序所有已打开的工作簿文件(Workbooks集合)，判断指定名称的工作簿是否打开，如下面的代码所示。 #001 Sub WorkbookIsOpen_1 #002 Dim Wb As Workbook #003 Dim myWb As String #004 myWb = Excel Home.xls #005 For Each W

21、b In Workbooks #006 If Wb.Name = myWb Then #007 MsgBox 工作簿 & myWb & 已经被打开! #008 Exit Sub #009 End If #010 Next #011 MsgBox 工作簿 & myWb & 没有被打开! #012 End Sub 代码解析： WorkbookIsOpen_1过程通过遍历当前应用程序中所有已打开的工作簿文件(Workbooks集合)，判断“Excel Home”工作簿是否打开。 第5行代码使用For.Each.Next语句来遍历Workbook对象集合中的所有元素。 第6行到第8行代码如果Workb

22、ook对象集合包含“Excel Home.xls”工作簿名称，说明文件已打开，使用Exit Sub语句结束代码的运行。 第11行代码如果运行到此行代码说明“Excel Home.xls”工作簿没有被打开。 9 VBA常用技巧代码解析 4-2 错误处理方法 使用错误处理程序判断指定名称的工作簿是否打开，如下面的代码所示。 #001 Sub WorkbookIsOpen_2 #002 Dim Wb As Workbook #003 Dim myWb As String #004 myWb = Excel Home.xls #005 Err.Clear #006 On Error GoTo line

23、 #007 Set Wb = Application.Workbooks(myWb) #008 MsgBox 工作簿 & myWb & 已经被打开! #009 Set Wb = Nothing #010 Exit Sub #011 line: #012 MsgBox 工作簿 & myWb & 没有被打开! #013 Set Wb = Nothing #014 End Sub 代码解析： WorkbookIsOpen_2过程使用错误处理程序判断“Excel Home”工作簿是否打开。 第5行代码使用Clear方法清除Err对象的所有属性设置。 第6行代启动错误处理程序，如果第7行代码发生错误则执

24、行line行后面的代码。 第7行代码使用Set语句将Workbook对象引用赋给变量Wb，如果“ Excel Home.xls”工作簿没有被打开将发生下标越界错误，此时执行第12、13行代码，否则执行第8、9行代码。 技巧5 禁用宏则关闭工作簿 通常情况下，当应用程序的宏安全性的安全级别设置为“中”时，打开包含Microsoft Excel 4.0版的宏的工作簿，将显示如图 5-1所示的“安全警告”对话框。 10 VBA常用技巧代码解析 图 5-1 安全警告对话框 如果用户选择“禁用宏”按钮，则会显示如图 5-2所示的警告消息框，当用户选择“否”时，不能打开该工作簿；用户选择“是”时，打开该工

25、作簿，但VBA宏被禁止，而Microsoft Excel 4.0版的宏未被禁止。 图 5-2 Microsoft Excel 4.0宏警告对话框 我们可以利用禁用VBA宏不能禁止Microsoft Excel 4.0版的宏这个特点，使用Microsoft Excel 4.0版的宏来实现禁用宏则关闭工作簿的功能。 步骤1 新建或打开需要添加此项功能的工作簿文件。 步骤2 按组合键为工作簿添加一个宏表，添加的宏表名称默认为“Macro1”。 步骤3 在宏表“Macro1”的A1至A7单元格中输入下面的内容。 #001 禁用宏则关闭工作簿 #002 =ERROR(FALSE) #003 =IF(ER

26、ROR.TYPE(RUN(TestMacro)=4) #004 = ALERT(因禁用了宏功能,文件将被关闭!,3) #005 = FILE.CLOSE(FALSE) #006 =END.IF #007 =RETURN 完成后的宏表如图 5-3所示。 11 VBA常用技巧代码解析 图 5-3 完成输入后的宏表 代码解析： Microsoft Excel 4.0宏函数以等号开始，其他不是由等号开始的内容将被视作注释。通常用作定义的宏名称或者作为宏函数实现功能的注释内容设置为斜体字样以示区别，如图 5-3中单元格A1所示。 第2行代码关闭错误检查功能。如果关闭错误检查，那么当宏执行遇到错误时，Ex

27、cel 将不予理会而继续执行。 第3行到第6行代码使用If函数与End.If函数构成条件判断语句。其中，第语句通过检查宏函数RUN(TestMacro)的返回错误类型是否为4判断工作簿是否禁用了宏功能。如果第3行的结果为True，则执行下面的语句。在第4、5行代码，插入几个空格来表示相关代码之间的层次结构。第4行中的代码显示一个消息框。第5行中的代码关闭当前活动工作簿，设置参数值为Fasle表示关闭时工作簿时不保存对其所作的更改。 第7行代码终止当前代码的执行。Microsoft Excel 4.0宏要求每个宏必须使用或HALT函数结束。 步骤4 为每个表添加工作表级别的名称“Auto_Act

28、ivate”，并将引用都指向宏表“Macro1”的A2单元格。“Auto_Activate”是一个自动宏，表被激活时自动执行。添加工作表级别的名称的方法如下：选择一张工作表，假设为表“Sheet1“插入”“名称”“定义名称”。在“定义名称”对话框中添加名称，如图 12 Microsoft 3行中的， RETURN 5-4所示。 ”，单击菜单 VBA常用技巧代码解析 图 5-4 定义工作表级别的名称 输入完成后单击“确定”按钮，完成一张工作表的“Auto_Activate”的定义。完成定义后的名称将在“定义名称”对话框中显示，如图 5-5所示。依次为每个表添加“Auto_Activate”名称。

29、 图 5-5 名称对话框中的工作表级名称 此外，使用VBA也可以实现同样的操作，并且使用VBA的好处是能够隐藏名称，以避免名称被删除或修改。代码如下： #001 Sub AddPrivateNames #002 Dim sht As Object #003 For Each sht In Sheets #004 ThisWorkbook.Names.Add sht.Name & !Auto_Activate, _ =Macro1!$A$2, False #005 Next #006 End Sub 13 VBA常用技巧代码解析 步骤5 运行下面的代码，隐藏宏表工作表： #001 Sub Hid

30、eMacroSheet #002 ThisWorkbook.Excel4MacroSheets(1).Visible = xlSheetHidden #003 End Sub 步骤6 保存工作簿。 当应用程序的宏安全性的安全级设置为“中”时，如果用户打开该工作簿文件并选择“禁用宏”，将显示如图 5-2所示的警告消息框。当用户选择“是”时，活动工作表上的自动宏“Auto_Activate”将被执行，执行结果显示如图 5-6所示的消息框，当用户选择“确定”按钮后，将强制关闭该工作簿文件。 图 5-6 警告消息框 技巧6 关闭工作簿不显示保存对话框 当用户更改工作簿后，没有进行保存操作而直接关闭工作

31、簿时，将显示如图 6-1所示的消息框，提示用户是否保存对工作簿的更改，如果希望不显示该消息框而直接关闭关闭工作簿，可以在关闭时进行相应的设置。 图 6-1 提示保存对话框 6-1 使用Close方法关闭工作簿 使用Close方法关闭工作簿的，可以在Close方法中指定相应的参数，如下面的代码14 VBA常用技巧代码解析 所示。 #001 Sub wbClose_1 #002 ThisWorkbook.Close SaveChanges:=False #003 End Sub 代码解析： wbClose_1过程使用Close方法关闭工作簿，并放弃所有对工作簿的更改。 应用于Workbook对象的

32、Close方法关闭对象，语法如下： expression.Close(SaveChanges, Filename, RouteWorkbook) 其中SaveChanges参数是可选的，如果工作簿没有改变则忽略此参数；如果工作簿发生了改变并且在另外的窗口中也打开了该工作簿，则仍然忽略此参数；如果工作簿发生了改变并且没有在另外的窗口中打开，则此参数将指定是否在工作簿中保存所发生的更改。取值与操作如表格 6-1所示： 值 True 作用 将改变保存到工作簿。如果该工作簿尚未命名，则使用 FileName 指定的名称。如果省略 FileName 参数，则要求用户输入文件名。 False 省略 不将改

33、变保存到此文件。 显示一个对话框，要求用户决定是否保存所做的更改。 表格 6-1 SaveChanges参数值的作用 如果希望在关闭工作簿时自动保存更改，将SaveChanges参数值设置为True即可。 还可以在使用Close方法关闭工作簿时设置Workbook对象的Saved属性，如下面的代码所示。 #001 Sub wbClose_2 #002 ThisWorkbook.Saved = True #003 ThisWorkbook.Close #004 End Sub 代码解析： wbClose_2过程使用Close方法关闭工作簿，并放弃所有对工作簿的更改。 Workbook对象的Sav

34、ed属性指示工作簿从上次保存至今是否发生过更改，如果工作簿进行了更改，则该属性值为False，否则为True。应用程序在关闭工作簿之前判断该属性的值，如果其值为False，则显示提示是否保存的消息框，询问用户是否保存对工作簿所做的更改。 第2行代码将该属性的值设置为True，使Excel认为已经保存了对工作簿所作的更改，从而不再显示提示是否保存的消息框。 15 VBA常用技巧代码解析 如果需要保存对工作簿所作的更改，那么应该在Close方法之前使用Save方法保存工作簿，代码如下： #001 Sub wbClose_3 #002 ThisWorkbook.Save #003 ThisWorkb

35、ook.Close #004 End Sub 代码解析： wbClose_3过程使用Save方法保存工作簿所做的更改，然后使用Close方法关闭工作簿。 6-2 单击工作簿关闭按钮关闭工作簿 如果是通过单击工作簿的关闭按钮等操作关闭工作簿的，则使用BeforeClose事件过程来控制，如下面的代码所示。 #001 Private Sub Workbook_BeforeClose(Cancel As Boolean) #002 Me.Saved = True #003 End Sub 代码解析： 工作簿的Workbook_BeforeClose事件，将工作簿的Saved属性设置为True，不保存

36、更改而直接关闭工作簿，且不显示提示保存的消息框。 如果希望保存对工作簿的更改，则在Workbook_BeforeClose事件中使用Save方法保存工作簿，如下面的代码所示。 #001 Private Sub Workbook_BeforeClose(Cancel As Boolean) #002 Me.Save #003 End Sub 技巧7 禁用工作簿的关闭按钮 一般情况下，用户可以通过菜单“文件”“关闭”、工作簿窗口右上角的“关闭窗口”按钮或者任务栏中图标右键菜单中的“关闭”菜单项关闭工作簿。如果希望禁用上述关闭工16 VBA常用技巧代码解析 作簿的功能，而只能通过代码关闭工作簿，则可以在相应的工作簿事件中实现，如下面的代码所示。 #001 Dim BClose As Boolean #002 Private Sub Workbook_BeforeClose(Cancel As Boolean) #003 If BClose = False Then #004