When you want to use functionality from other applications you have to decide if you want to use
early or late binding of object variables.
Early binding
The binding between the object variable and the object takes place when the application is compiled.
This results in better performance compared to when the binding takes place when the application is run (late binding).
If you want to create an early binding you have to set a reference to the "foreign" object library you want to use.
This is done from the VBE by using the menu Tools, References.... When a VBProject has a reference to an
object library you can declare specific object variables (e.g. Dim oDoc As Word.Document). This will also make it
easier to program the "foreign-objects" since the VBE will display the same programming help regarding properties,
methods and events that it displays for the objects belonging to the application you are working
from (the VBE has automatically added the reference to this application in advance).
This is a general code example showing vba automation error:
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
Late binding
The binding between the object variable and the object takes place when the application is run.
This results in slower performance compared to when the binding takes place when the application is compiled (early binding).
If you don't add a reference to the objectlibrary belonging to the "foreign" application you have to
declare general object variables (e.g. Dim oDoc As Object). This will make it more difficult to program
the "foreign-objects" since the VBE will not display the same programming help regarding properties,
methods and events that it displays for the objects belonging to the application you are working from.
This is a general code example:
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
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
I have a Mathcad sheet I would like to pass variable data too and then retrieve variable data back so that I can have excel format a tabularize results. I have been trying to set up an OLE link but I seem to get and error object not recognized.