WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA GetObject

VBA GetObject

By Babita SehdevBabita Sehdev | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Excel VBA GETOBJECT Function

We can use the GetObject function in VBA in MS Excel to access an ActiveX object from the excel file and then assign the object to an object variable. To use OLE (Object Linking and Embedding) or COM (Compound Object Module) technology to control any Microsoft application like MS Word, MS Outlook, MS PowerPoint, and Internet Explorer, etc., we can use VBA GETOBJECT function.

We use the CreateObject function to create the object, and the GETOBJECT function returns the reference to the object.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

VBA GETOBJECT

Syntax for GETOBJECT Function

VBA GetObject syntax

The GET OBJECT Function has these named arguments:

  1. Pathname: We need to specify the full path and the name of the file containing the object to retrieve. This is an optional argument, in fact, both the arguments in the GetObject function are optional, but if ‘pathname’ is omitted, the second argument ‘class’ is required.
  2. Class: This is also an optional argument as specified earlier also. This accepts a string representing the class of the object.

We uses the syntax ‘appname.objecttype’ to specify ‘class’ argument.

  1. Appname: We need to specify the application name, which will provide the object.
  2. Object type: We specify the type of class of object to create.

Example of Excel VBA GETOBJECT Function

You can download this VBA GetObject Template here – VBA GetObject Template

Suppose we have a word document containing 3 tables.

VBA GetObject Example 1

VBA GetObject Example 1-1

We want to write a VBA code that will import all the tables in the document to the excel sheet. To do the same, we will need to use the CreateObject and GetObject function in VBA.

Steps would be:

  • Create an excel file and save the file with the .xlsm excel extension (Excel Macro-Enabled Workbook) as we will need to run the VBA code (a macro).
  • Open the basic visual editor with a shortcut key (Alt+F11) or using the ‘Visual Basic’ command in the ‘Code’ group in the ‘Developer’ tab in excel.
  • Double click on ‘ThisWorkbook’ on the left side of the VBA editor and choose ‘Workbook’ from the list shown thereafter on the top of the screen.

VBA GetObject Example 1-2

  • Choose ‘Open’ from the list.

VBA GetObject Example 1-3

  • Now we need to write the code in between these two lines.

VBA GetObject Example 1-5

  • First, we will declare variables to hold the objects (MS Word Document and MS Word Application object) and a ‘String Variable’ to hold the name of the document from where we need to extract the tables.

VBA GetObject Example 1-4

  • For error handling, we will add one statement. This statement tells the VBA program to ignore the error and resume the execution with the next line of code. “On Error Resume Next” statement does not fix the runtime errors, but it simply means that program execution will continue from the line following the line that caused the error.

VBA GetObject Example 1-6

  • Now we will use the GetObject function to get access to the current instance of the Word Application Object.

VBA GetObject Example 1-7

  • If in case there is no current instance of the MS Word Application, or ActiveX component cannot create an object or return reference to this object, then error 429. For this, we will add below two lines in the code. After handling the error, we need to create an instance of the MS Word Application object using the CreateObject function.

VBA GetObject Example 1-8

  • To make MS Word Application visible, we will change the visible property of the ‘WdApp’ object to TRUE.

VBA GetObject Example 1-9

  • We need to find the location and file name of the word document from which we want to import the tables into an excel sheet and assign the same to the “strDocName” To find the name and location, and we can check out the properties of the file.

To open the ‘Properties’ dialog box, just select the file and press ‘Alt+Enter.’

VBA GetObject Example 1-10

Get Object Example 1-11

  • If the file does not exist in the specified location, then the code returns the message stating, “The file Marks Details was not found in the folder path.” The title would be “Sorry, that document name does not exist.”

Get Object Example 1-12

  • Now we need to activate the MS Word Application and assign the variable ‘wddoc’ with the word document having the file name stored in the ‘strDocName.’

Get Object Example 1-13

  • If the file is not opened already, then we need to open the document and activate the app.

Get Object Example 1-14

  • After activating the word document, we need to access the tables in the document. To do the same, we will create some variables.

Get Object Example 1-15

Tble is the integer variable, which will store the count of tables in the document.

rowWd is the long variable, which will store the number of rows in a particular table.

colWd is the long variable, which will store the number of columns in a particular table.

  • We need to count the number of tables in the document, and if there is notable in the document, then we will display a message box to the user that “No Tables found in the Word document.”

Get Object Example 1-16

  • To access tables in the document and to write the content in the excel sheet, we will run a ‘For’ VBA loop for a number of tables times, and within this VBA loop, we will run nested ‘for’ loops for accessing every row and every column in the row.

Get Object Example 1-17

  • As we do not want to save the document and quit the application. We should also release the memory of the system. To do the same, we will write the following code.

Get Object Example 1-18

Now, whenever we open the excel file, the fill is updated with table content from the word document.

VBA Get-Object Example 1-19

Code:

Private Sub Workbook_Open()

Rem Declaring Object variables to access object created by GETOBJECT
Dim WdApp As Object, wddoc As Object
Rem Declaring a string variable to access the Word document
Dim strDocName As String

Rem Error handling
On Error Resume Next

Rem Activating MS Word if it is already opened
Set WdApp = GetObject(, "Word.Application")

If Err.Number = 429 Then
Err.Clear
Rem Creating a Word application object if MS Word is not already opened
Set WdApp = CreateObject("Word.Application")
End If

WdApp.Visible = True

strDocName = "C:\Users\CBA7_01\Desktop\Marks Details.docx"

Rem Checking relevant directory for the relevant document
Rem If not found then informing the user and closing the program
If Dir(strDocName) = "" Then
MsgBox "The file " & strDocName & vbCrLf & "was not found in the folder path" & vbCrLf & "C:\Users\CBA7_01\.", _vbExclamation, "Sorry, that document name does not exist."
Exit Sub
End If

WdApp.Activate

Set wddoc = WdApp.Documents(strDocName)

If wddoc Is Nothing Then Set wddoc = WdApp.Documents.Open("C:\Users\CBA7_01\Desktop\Marks Details.docx")
wddoc.Activate

Rem Defining variables to access the tables in the word document
Dim Tble As Integer
Dim rowWd As Long
Dim colWd As Integer
Dim x As Long, y As Long
x = 1
y = 1

With wddoc
Tble = wddoc.Tables.Count
If Tble = 0 Then
MsgBox "No Tables found in the Word document", vbExclamation, "No Tables to Import"
Exit Sub
End If

Rem Starting the looping process to access tables and their rows, columns
For i = 1 To Tble
With.Tables(i)
For rowWd = 1 To .Rows.Count
For colWd = 1 To .Columns.Count
Cells(x, y) = WorksheetFunction.Clean(.cell(rowWd, colWd).Range.Text)
Rem Accessing next column
y = y + 1
Next colWd
Rem Going to next row and start from column 1
y = 1
x = x + 1
Next rowWd
End With
Next
End With

Rem we do not need to save the word document
wddoc.Close Savechanges:=False
Rem we quit MS Word application
WdApp.Quit
Rem We finally release system memory allocated for the 2 object variables
Set wddoc = Nothing
Set WdApp = Nothing

End Sub

Things to Remember

  1. There is some single-instance object, for which only one instance of the object is generated, regardless of the number for which CreateObject is run. GetObject function at all times returns the same instance when it is called with a string of zero length, and an error comes if the ‘pathname’ argument is not mentioned.
  2. We cannot use GetObject to access a reference to a class created with VBA.
  3. If in case, there is no active instance of the MS Word Application, or we do not want the object initiated with a file already loaded, then we first use the CreateObject function to create the object and then use the GetObject function to access the object.

Recommended Articles

This has been a guide to VBA GetObject. Here we discuss how the GetObject function returns the reference of an object in Excel VBA along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –

  • VBA IF Not
  • VBA INSTRREV
  • VBA FileSystemObject
  • VBA Option Explicit
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA GetObject Template

Special Offer - VBA Training Course (6 courses, 35+ hours video) View More