Contents
The GetObject function in VBA is used to retrieve a reference to an object from a file or a running instance of an application. It can be particularly useful when you want to interface with an already open application (like an Excel workbook or a Word document) or when you want to manipulate files that are associated with an application.
Basic syntax
Set objectVariable = GetObject([pathname[, class]])
Parameters
- pathname – (Optional) The full path and filename of the file containing the object to retrieve. If pathname is omitted, class must be provided.
- class – (Optional) The class of the object to retrieve. This could be a ProgID (e.g., “Excel.Application”) or a CLSID.
Here’s an example on how to use GetObject to connect to an existing instance of Excel:
Sub GetExcelInstance()
Dim excelApp As Object
On Error Resume Next ' Ignore errors in case Excel is not running
' Attempt to connect to a running Excel instance
Set excelApp = GetObject(, "Excel.Application")
On Error GoTo 0 ' Reset normal error handling
If excelApp Is Nothing Then
MsgBox "Excel is not running."
Else
MsgBox "Connected to Excel version " & excelApp.Version
' You can now work with the Excel application object
End If
' For instance, you might activate Excel
If Not excelApp Is Nothing Then
excelApp.Visible = True
excelApp.WindowState = xlMaximized
End If
End Sub
Here’s another example that demonstrates how to use GetObject to open a specific Word document and perform an action:
Sub OpenWordDocument()
Dim wordApp As Object
Dim wordDoc As Object
Dim docPath As String
docPath = "C:\Path\To\Your\Document.docx" ' Specify the correct path
' Create a new Word application object
On Error Resume Next
Set wordApp = GetObject(, "Word.Application")
If wordApp Is Nothing Then
' Word isn't open; create a new instance of Word.
Set wordApp = CreateObject("Word.Application")
End If
On Error GoTo 0
If Not wordApp Is Nothing Then
' Attempt to open the document with Word
Set wordDoc = wordApp.Documents.Open(docPath)
' Make Word visible
wordApp.Visible = True
' You can now manipulate the document
wordDoc.Activate
' Add code here to work with the document...
Else
MsgBox "Could not start Word."
End If
End Sub
Using GetObject lets you hook into the existing instance of an application, avoiding the overhead of starting a new instance. However, you should include proper error handling to gracefully deal with situations where the expected object cannot be retrieved.
Unlock Your Potential
Excel
Access
Power BI
