VBA常用技巧代码解析

humengqiang

謝謝版主的無私奉獻

nicholas_he

楼主，你真好
反正我是定期浏览你的帖子，期待中。。。

leiying

版主真有心了，非常感谢，收藏！

samy1221

thanks a lot ,  so kind of you ~

LCP92095

版主，很想在EXCEL 中读取井号来从ORACLE 中提取数据，如何用VBA实现，请给与菜鸟指点

yuanzhuping

原帖由 LCP92095 于 2009-2-21 10:50 发表
版主，很想在EXCEL 中读取井号来从ORACLE 中提取数据，如何用VBA实现，请给与菜鸟指点

不好意思，oracle数据库没有涉及过，不了解。

yvhgydn

现在没在家，留个记号，回去下载到电脑UP UP STUDY

yuanzhuping

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

1. #001  Sub AddNowbook()
   
2. #002      Dim Nowbook As Workbook
   
3. #003      Dim ShName As Variant
   
4. #004      Dim Arr As Variant
   
5. #005      Dim i As Integer
   
6. #006      Dim myNewWorkbook As Integer
   
7. #007      myNewWorkbook = Application.SheetsInNewWorkbook
   
8. #008      ShName = Array("余额", "单价", "数量", "金额")
   
9. #009      Arr = Array("01月", "02月", "03月", "04月", "05月", "06月", "07月", "08月", "09月", "10月", "11月", "12月")
   
10. #010      Application.SheetsInNewWorkbook = 4
    
11. #011      Set Nowbook = Workbooks.Add
    
12. #012      With Nowbook
    
13. #013          For i = 1 To 4
    
14. #014              With .Sheets(i)
    
15. #015                  .Name = ShName(i - 1)
    
16. #016                  .Range("B1").Resize(1, UBound(Arr) + 1) = Arr
    
17. #017                  .Range("A2") = "品名"
    
18. #018              End With
    
19. #019          Next
    
20. #020          .SaveAs Filename:=ThisWorkbook.Path & "" & "存货明细.xls"
    
21. #021          .Close Savechanges:=True
    
22. #022      End With
    
23. #023      Set Nowbook = Nothing
    
24. #024      Application.SheetsInNewWorkbook = myNewWorkbook
    
25. #025  End Sub

复制代码

代码解析：
       AddNowbook过程使用Add方法建立新的工作簿并对新建工作簿进行操作。
       第2行到第6行代码声明变量类型。
       第7行代码保存Excel自动插入到新工作簿中的工作表数目。
       第8、9行代码将数组元素赋值给变量。
       第10行代码将Application对象的SheetsInNewWorkbook属性设置为4，在新建工作簿时插入4张工作表。
       第11行代码使用Add方法建立新的工作簿，应用于Workbooks对象的Add方法新建工作簿，新建的工作簿将成为活动工作簿。
       第12行到第22行代码操作新建工作簿。其中第15行到第17行代码将新建工作簿的工作表进行重命名并给单元格赋值。第20行代码使用SaveAs方法将新建工作簿重命名为“存货明细.xls”保存在同一目录中。第21行代码使用Close方法关闭工作簿。
       第24行代码恢复工作簿的默认设置。
       运行AddNowbook过程将在工作簿同一目录中新建“存货明细.xls”工作簿，新建工作簿格式如图所示。

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

yuanzhuping

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

1. #001  Sub Openfile()
   
2. #002      Dim x As Integer
   
3. #003      For x = 1 To Workbooks.Count
   
4. #004          If Workbooks(x).Name = "123.xls" Then
   
5. #005              MsgBox """123""工作簿已经打开!"
   
6. #006              Exit Sub
   
7. #007          End If
   
8. #008      Next
   
9. #009      Workbooks.Open ThisWorkbook.Path & "\123.xls"
   
10. #010  End Sub
    

复制代码

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

       第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是可选的，指定文件中链接的更新方式。如果省略本参数，则提示用户选择链接的更新方式。否则，该参数的取值应为表格中的某个值。

       参数ReadOnly是可选的，如果该值为True，则以只读模式打开工作簿。
       参数Format是可选的，如果Microsoft Excel正在打开一个文本文件，则该参数用于指定分隔字符，如表格所示。如果省略本参数，则使用当前的分隔符。

       参数Password是可选的，该字符串指定打开一个受保护工作簿的密码。如果省略该参数并且指定工作簿已设置密码，则提示用户输入密码。
       参数WriteResPassword是可选的，该字符串为一个写保护工作簿的写入权密码。如果省略该参数并且指定工作簿已设置密码，则提示用户输入密码。
       参数IgnoreReadOnlyRecommended是可选的，如果该值为True，则设置Microsoft Excel不显示建议只读消息（如果该工作簿以“建议只读”选项保存）。
       参数Origin是可选的，如果文件为文本文件，则该参数用于指示该文件来源于何种操作系统。
       参数Delimiter是可选的，如果该文件为文本文件并且Format参数为 6，则此参数用于指定用作分隔符的字符。
       参数Editable是可选的，如果该文件为Microsoft Excel 4.0加载宏，则该参数的值为True时可打开该加载宏以便在窗口中看到。如果该参数的值为False或者省略该参数，则该加载宏以隐藏方式打开，并且无法设为可见。
       参数Notify是可选的，当该文件不能以可读写模式打开时，如果该参数的值为True，则可将该文件添加到文件通知列表。
       参数Converter是可选的，打开文件时试用的第一个文件转换器的索引号。
       参数AddToMru是可选的，如果该值为True，则将该工作簿添加到最近使用的文件列表中。默认值为False。
       参数Local是可选的，如果该值为True，则以Microsoft Excel（包括控制面版设置）的语言保存文件。如果该值为False（默认值），则以 Visual Basic for Applications (VBA)的语言保存文件，其中Visual Basic for Applications (VBA)为典型安装的美国英语版本，除非VBA项目的Workbooks.Open来自旧的国际化的XL5/95 VBA项目。
       参数CorruptLoad是可选的，可为以下常量之一：xlNormalLoad、xlRepairFile 和 xlExtractData。如果未指定任何值，则默认值通常为普通状态。

yuanzhuping

技巧43         判断指定工作簿是否打开
43-1        遍历Workbooks集合方法
       通过遍历当前应用程序所有已打开的工作簿文件(Workbooks集合)，判断指定名称的工作簿是否打开，如下面的代码所示。

1. #001  Sub WorkbookIsOpen_1()
   
2. #002      Dim Wb As Workbook
   
3. #003      Dim myWb As String
   
4. #004      myWb = "Excel Home.xls"
   
5. #005      For Each Wb In Workbooks
   
6. #006          If Wb.Name = myWb Then
   
7. #007              MsgBox "工作簿" & myWb & "已经被打开!"
   
8. #008              Exit Sub
   
9. #009          End If
   
10. #010      Next
    
11. #011      MsgBox "工作簿" & myWb & "没有被打开!"
    
12. #012  End Sub
    

复制代码

代码解析：
       WorkbookIsOpen_1过程通过遍历当前应用程序中所有已打开的工作簿文件(Workbooks集合)，判断“Excel Home”工作簿是否打开。
       第5行代码使用For...Each...Next语句来遍历Workbook对象集合中的所有元素。
       第6行到第8行代码如果Workbook对象集合包含“Excel Home.xls”工作簿名称，说明文件已打开，使用Exit Sub语句结束代码的运行。
       第11行代码如果运行到此行代码说明“Excel Home.xls”工作簿没有被打开。
43-2        错误处理方法
       使用错误处理程序判断指定名称的工作簿是否打开，如下面的代码所示。

1. #001  Sub WorkbookIsOpen_2()
   
2. #002      Dim Wb As Workbook
   
3. #003      Dim myWb As String
   
4. #004      myWb = "Excel Home.xls"
   
5. #005      Err.Clear
   
6. #006      On Error GoTo line
   
7. #007      Set Wb = Application.Workbooks(myWb)
   
8. #008      MsgBox "工作簿" & myWb & "已经被打开!"
   
9. #009      Set Wb = Nothing
   
10. #010      Exit Sub
    
11. #011  line:
    
12. #012      MsgBox "工作簿" & myWb & "没有被打开!"
    
13. #013      Set Wb = Nothing
    
14. #014  End Sub
    

复制代码

代码解析：
       WorkbookIsOpen_2过程使用错误处理程序判断“Excel Home”工作簿是否打开。
       第5行代码使用Clear方法清除Err对象的所有属性设置。
       第6行代启动错误处理程序，如果第7行代码发生错误则执行line行后面的代码。
       第7行代码使用Set语句将Workbook对象引用赋给变量Wb，如果 “Excel Home.xls”工作簿没有被打开将发生下标越界错误，此时执行第12、13行代码，否则执行第8、9行代码。