VBA GetObject

Excel VBA GETOBJECT Function

We can use 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 CreateObject function to create the object and GETOBJECT function returns the reference to the object.

Syntax for GETOBJECT Function

The GETOBJECT 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, 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. Objecttype: We specify the type of class of object to create.

Example of Excel VBA GETOBJECT Function

Suppose, we have a word document containing 3 tables.

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

Steps would be:

• Create an excel file and save the file with the .xlsm extension (Excel Macro-Enabled Workbook) as we will need to run the VBA code (a macro).
• Open the visual basic editor with a shortcut key (Alt+F11) or using ‘Visual Basic’ command in the ‘Code’ group in the ‘Developer’ tab.
• 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.

• Choose ‘Open’ from the list.

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

• 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.

• 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.

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

• 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.

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

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

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

• 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.”

• 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’.

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

 

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

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 no table in the document then we will display a message box to the user that “No Tables found in the Word document”.

• 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.

• 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.

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

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 CreateObject function to create the object and then use GetObject function to access the object.

Recommended Articles

This has been a guide to VBA GetObject. Here we discuss how 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 Exit Sub
• Excel VBA COUNTA 
• Auto Refresh Pivot Table using VBA
• VBA INSTRREV
• VBA Global Variables
• VBA FileSystemObject (FSO)
• VBA Activate Sheet
• VBA Option Explicit