What is Excel VBA GETOBJECT Function?
VBA GetObject is a function that allows us to access VBA ActiveX objects from an Excel file or external object references, which can be assigned to the object variable for smooth operations.
When we try to establish a connection between other Microsoft Office applications like MS Word, MS Outlook, MS PowerPoint, and Internet Explorer, we must use the VBA GetObject function, which will use the Object Linking and Embedding (OLE) and Compound Object Model (COM) technology.
For example, if we have an open workbook named “Sales Report.xlsx.” in our system and want to access it, we can use the VBA GetObject function to establish a connection to the open workbook.
Dim My_Wb as Object
Set My_Wb = GetObject(“Sales Report.xlsx”)
Once the workbook reference is assigned to the variable My_Wb, we can use it to access the workbook.
Table of contents
Key Takeaways
- VBA GetObject function helps us reference objects created by other applications. Thus, the VBA code can be used to access properties and methods of an external object.
- The VBA GetObject function allows us to get the reference of the Excel workbook, which is saved in our workbook. It allows us to open an MS Word document from the saved location and activate and work with that document.
- Using the VBA GetObject function, we can get object references, but it cannot be used to access the reference to a class.
Syntax – Excel VBA GETOBJECT Function
The syntax of the VBA GetObject function is as follows.
GetObject( [PathName], [Class])
Both the arguments are optional here.
- [PathName]: The complete path to the file or the object to be accessed. If there is no path given, the object which is already opened will be returned. If the path name is not given, the second argument becomes mandatory.
For example, if you want to access the Excel file which is stored in one of the folders on your computer, then we can provide the full path as follows.
C:\Users\jeevanay\OneDrive – xlmojo\Desktop\Personal\Sales Report.xlsx
- [Class]: The application that we must access will be given in this argument in double quotes. For example, to create an Excel application, we can provide the class as follows.
“Excel.Application”
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
How to Use VBA GETOBJECT Function in Excel?
Let us look at a practical example of using the VBA GETOBJECT function to work with the opened Excel workbook. For example, we have the following Excel workbook in a computer folder.
We will use the VBA GetObject function to work with this workbook. First, let’s open this workbook.
- Create a sub-procedure by naming the macro.
- Define a variable of the Object data type.
- For this variable, let us assign the workbook reference using the VBA GETOBJECT function.
- For the path name argument of the VBA GetObject function, copy the complete file path address where it is stored.
- After the folder path is entered, put a backslash followed by the workbook name which is opened.
- Now, the variable “Wb” holds the reference for the workbook “Text.xlsx.” We can use this variable name and access the properties and methods of the Workbook object.
However, as soon as we enter a dot, the IntelliSense list should show all the properties and methods associated with the workbook object. But we do not see anything when we use “Object” as the data type.
Hence, for IntelliSense to show all the properties and methods of the workbook object, we must specifically use the “Workbook” object. Change the data type name from “Object” to “Workbook.”
Now, use the variable “Wb” and enter a dot. It should show all the properties and methods of the Workbook object.
- Choose the Activate method.
Now, let’s execute the VBA code. It should activate the workbook “Test.xlsx.”
It has activated the workbook “Test.xlsx.”
Examples of Excel VBA GETOBJECT Function
Let us show you VBA GetObject function examples to understand it better.
Example #1 – Get the Opened Workbook Reference
We often work with multiple workbooks as a part of automation, In such scenarios, we need to get the reference of the workbook to one of the variables.
Sub VBA_GetObject_Ex1()
‘Define a variable to assign the workbook reference
Dim MyWorkbook As Workbook
‘Define a variable to store the location folder
Dim Location As String
‘Assign the location folder path to the variable
‘At the end we have assigned back slash () to assign the workbook name later
Location = “C:\Users\jeevanay\Desktop\Charticulator\New folder\”
‘Assign the workbook reference to variable MyWorkbook
Set MyWorkbook = GetObject(Location & “Sales Report.xlsx”)
‘Use the variable of the VBA GetObject function
‘Activate the workbook
MyWorkbook.Activate
‘Insert some value to referenced workbook
MyWorkbook.Worksheets(“Sheet1”).Range(“A1”).Value = “Hi There!!!”
End Sub
The VBA GetObject function helps us to get the reference of the opened workbook. The following code will get the workbook reference to variable “MyWorkbook.”
Note: To run this code, we should first open the workbook named “Sales Report.xlsx.” Once we run the code, it will insert the value “Hi There !!!” in the referenced workbook into the worksheet in cell A1.
Example #2 – Create Microsoft Word Document with the VBA GetObject Function
We can initiate the work document object from the Excel file using the VBA GetObject function. For example, assume we have a Word document, “Test Document.docx”, saved in our computer folder. Let us try to open and activate this Word document using the VBA GetObject function.
The following is the code for your reference.
Sub VBA_GetObject_Ex2()
‘Define a variable to assign the workbook reference
Dim MyWordDoc As Object
‘Initiate the work applicaiton from Excel
Set MyWordDoc = GetObject(, “Word.Application”)
‘Open the word document from the given location folder
MyWordDoc.Documents.Open (“C:\Users\jeevanay\Desktop\Charticulator\New folder\Test Document.docx”)
‘Once the word document is opened, lets activate the word documnet
MyWordDoc.Activate
End Sub
Let’s execute the code. It will activate the word document from your given folder.
As we can see in the above image, the word document which is saved as “Test Document.docx” is opened and activated using the given code which used the VBA GetObject function.
Important Things to Note
- VBA GetObject function returns the following error if the given folder path is not correct: Run-time error ‘5174’ Sorry, we couldn’t find your file. Was it moved, renamed, or deleted?
- VBA GetObject function returns the following error if we do not open the Excel workbook and try to access it via variable. Run Time error ‘432’ File name or class name not found during Automation operation.
- Since the VBA GetObject function refers to objects, we use the “Set” keyword while assigning object references to variables.
- The VBA GetObject function’s second argument becomes mandatory if we ignore the first argument.
Frequently Asked Questions (FAQs)
The VBA GetObject function won’t work in some scenarios because of the following reasons:
• The given folder location path is incorrect.
• If we do not open the Excel workbook and try to access it, then it won’t work.
We can use the References tab to get the references of external applications. Here, we need to set the references from the Object reference library.
If the file or object given is not found, then we will get the following error.
Run-time error ‘5174’ Sorry, we couldn’t find your file. Was it moved, renamed, or deleted?
We can use the VBA GetObject to automate only the following applications.
• Excel Workbook
• MS Word
• MS PowerPoint
• Internet Explorer
• Google Chrome
• Microsoft Edge
To automate other applications, we may need to use different sets of codes with the proper syntax for those applications.
Recommended Articles
This has been a guide to VBA GetObject. Here we learn to use the GetObject function in Excel VBA, with its syntax and step-by-step examples. You can learn more from the following articles –
Leave a Reply