有关在Excel中使用VBA OLE自动化基本信息
当您想使用其他应用程序的功能时,必须决定是要使用对象变量的早期绑定还是后期绑定。
早期绑定
对象变量和对象之间的绑定发生在编译应用程序时。
与运行应用程序时进行绑定(后期绑定)时相比,这导致更好的性能。
如果要创建早期绑定,则必须设置对要使用的“外部”对象库的引用。
这是通过VBE使用菜单工具,引用…来完成的。当VBProject引用了对象库时,您可以声明特定的对象变量(例如Dim oDoc As Word.Document)。这也将使对“外部对象”进行编程变得更加容易,因为VBE将显示与属性,方法和事件相同的编程帮助,以帮助显示属于您正在使用的应用程序的对象(VBE已自动添加了提前参考此应用程序)。
这是一个显示vba自动化错误的常规代码示例:
Sub OLEAutomationEarlyBinding() ' replace xxx with one of the following: ' Access, Excel, Outlook, PowerPoint or Word Dim oApp As xxx.Application ' early binding Dim oDoc As xxx.Document ' Excel.Workbook, Outlook.MailItem, PowerPoint.Presentation, Word.Document On Error Resume Next ' ignore errors Set oApp = GetObject(, "xxx.Application") ' reference an existing application instance If oApp Is Nothing Then ' no existing application is running Set oApp = New xxx.Application ' create a new application instance End If On Error GoTo 0 ' resume normal error handling If oApp Is Nothing Then ' not able to create the application MsgBox "The application is not available!", vbExclamation End If With oApp .Visible = True ' make the application object visible ' at this point the application is visible ' do something depending on the application... Set oDoc = .Documents.Open("c:\foldername\filename.doc") ' open a document ' ... oDoc.Close True ' close and save the document .Quit ' close the application End With Set oDoc = Nothing ' free memory Set oApp = Nothing ' free memory End Sub
后期绑定
对象变量和对象之间的绑定在运行应用程序时发生。
与编译应用程序时进行绑定(早期绑定)相比,这会导致性能降低。
如果您不添加对属于“外部”应用程序的对象库的引用,则必须声明常规对象变量(例如Dim oDoc As Object)。这将使编写“外来对象”变得更加困难,因为VBE将不会针对其为属于您正在使用的应用程序的对象显示的属性,方法和事件显示相同的编程帮助。
这是一个通用的代码示例:
Sub OLEAutomationLateBinding() ' replace xxx with one of the following: ' Access, Excel, Outlook, PowerPoint or Word Dim oApp As Object ' late binding Dim oDoc As Object ' late binding On Error Resume Next ' ignore errors Set oApp = GetObject(, "xxx.Application") ' reference an existing application instance If oApp Is Nothing Then ' no existing application is running Set oApp = CreateObject("xxx.Application") ' create a new application instance End If On Error GoTo 0 ' resume normal error handling If oApp Is Nothing Then ' not able to create the application MsgBox "The application is not available!", vbExclamation End If With oApp .Visible = True ' make the application object visible ' at this point the application is visible ' do something depending on the application... Set oDoc = .Documents.Open("c:\foldername\filename.doc") ' open a document ' ... oDoc.Close True ' close and save the document .Quit ' close the application End With Set oDoc = Nothing ' free memory Set oApp = Nothing ' free memory End Sub