EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home VBA VBA Resources VBA Object functions VBA GetObject

VBA GetObject

By Ashwani JaiswalAshwani Jaiswal

VBA GetObject

Excel VBA GetObject

VBA GetObject, as the name, seems like we need to create a code for getting or creating an object. But in fact, it is the process of getting the Tables from any word to excel file. In VBA GetObject, we fetch the data from a word file by putting the location where it is kept and append any number of tables from that Word into the Excel sheet.

Syntax of VBA GetObject

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax of GetObject

Syntax is explained as shown below:

  • PathName = Here we will be giving the path to the Word document where it is kept. This field is optional.
  • Class = Here, we need to define the Class of Object function. This one is too optional. But if we are not defining the PathName in the syntax then Class will be must to define.

Both the arguments of the syntax are optional. But anyone of them should be defined.

Here, we will be using appname.objecttype to define the Class. AppName will be the application or type of file from which we will be fetching the data and Objecttype will be kind of file application we will be using.

Example of GetObject Function in Excel VBA

We will be seeing, how to fetch the data which is in the form of Table in a word document and append that data in Excel worksheet.

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

For this, we require such data in word file. Here, we are having a word file below, which has 2 tables of Employee name and Employee ID.

VBA GetObject 1

We have saved this file somewhere in our local drive which is easy to access. Follow the below steps to use GetObject function in Excel VBA.

Step 1: Go to VBA window and open a Module from the Insert menu tab as shown below.

VBA GetObject 1-2

Step 2: In the newly opened Module, write the subcategory of VBA GetObject or you can choose any other name as per need.

VBA GetObject 1-3

Step 3: First, define the 2 Object variable to access the Object prepared by VBA GetObject.

VBA GetObject 1-4

Step 4: We would need another variable for which we will store the location of File as String.

VBA GetObject 1-5

Step 5: To avoid any break if an error occurs we will keep resuming the next step.

VBA GetObject 1-6

Step 6: Now we would use GetObject function and set it as WordFile object. Keeping Path blank, we will define the Class as Word.Application where Word is the application of Microsoft.

VBA GetObject 1-7

Step 7: To avoid error number 429 which usually occurs in this case, we will clear it as it happens.

VBA GetObject 1-8

Step 8: Now make the created Object variable WordFile visible.

Object variable WordFile

Step 9: As we haven’t defined the path, so we will assign file location to StrDoc along with the extension.

Assign file location

Step 10: If we did not find anything in the word file, then we should be getting a message prompting that “No Data Available” or “Document Not Found”. And this would be done in If-End If loop.

VBA GetObject 1-11

Step 11: Now activate the Word file.

Activate Word file

Step 12: Do the same procedure for setting WordDoc as well. If WordDoc is nothing then we will open the file from the location where it is kept.

Setting WordDoc

Step 13: Now we will need to define the variables which will help in accessing the table from Word document. By this we will be creating a Table with Rows and Columns.

VBA GetObject 1-14

Step 14: As we all know the vertex of cells is 2 dimensional. So starting from 1st cell, we would need 2 variable where we will define the location from where we need to start our table in Excel. Here, we have considered that position at (1, 1) which is at 1st cell of the sheet.

VBA GetObject 1-15

Step 15: At this stage, we need to check the number of tables in Word file. And if there are no tables found then we should be getting the message for that.

Check the Number of tables

Step 16: In this step, we need to access the table from Word document and map that in Excel file. For this, we will use For loop for each row and column.

For loop

Step 17: At last we will quit the document once it appends the data from word to excel file without saving the file.

VBA GetObject 1-18

Step 18: Now Run the above code by pressing the F5 key or by clicking on the Play button.

We will see, the data which we have seen at the starting of the article which was separate in 2 different tables, is now appended in a single table in the excel sheet.

VBA GetObject 1-19

Below is the complete code in one sequence:

Code:

Sub VBA_GetObject()

Dim WordFile As Object
Dim WordDoc As Object
Dim StrDoc As String

On Error Resume Next
Set WordFile = GetObject(, "Word.Application")

If Err.Number = 429 Then
Err.Clear
Set WordFile = CreateObject("Word.Application")
End If

WordFile.Visible = True
StrDoc = "D:\Input\Test.docx"

If Dir(StrDoc) = "" Then
MsgBox StrDoc & vbCrLf & "Not Found in mentioned Path" & vbCrLf & "C:\Input Location", vbExclamation, "Document name not found"
Exit Sub
End If

WordFile.Activate

Set WordDoc = WordFile.Documents(StrDoc)

If WordDoc Is Nothing Then Set WordDoc = WordFile.Documents.Open("D:\Input\Test.docx")
WordDoc.Activate

  Dim Tble As Integer
  Dim RowWord As Long
  Dim ColWord As Integer
  Dim A As Long
  Dim B As Long
A = 1
B = 1

With WordDoc
Tble = WordDoc.Tables.Count
If Tble = 0 Then
MsgBox "No Tables Avaiable", vbExclamation, "Nothing To Import"
Exit Sub
End If

For i = 1 To Tble
With .Tables(i)
For RowWord = 1 To .Rows.Count
For ColWord = 1 To .Columns.Count
Cells(A, B) = WorksheetFunction.Clean(.cell(RowWord, ColWord).Range.Text)

B = B + 1
Next ColWord

B = 1
A = A + 1
Next RowWord
End With
Next
End With

WordDoc.Close Savechanges:=False

WordFile.Quit

Set WordDoc = Nothing
Set WordFile = Nothing

End Sub

Pros of Excel VBA GetObject

  • It is quite useful in importing the big set of data from word file to excel file.
  • We can import any kind of data from any kind of file just by changing the extension of it.

Things to Remember

  • Close all the word files before running the code.
  • Give the proper extension to the file which is being used.
  • GetObject cannot be used to access the reference to class.

Recommended Articles

This is a guide to VBA GetObject. Here we discuss how to use GetObject function in VBA to fetch the data from a word file into the excel sheet along with a practical example and downloadable excel template. You can also go through our other suggested articles –

  1. VBA GetOpenFileName
  2. VBA Workbook Open
  3. VBA RGB
  4. VBA On Error Resume Next
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
1000+ Hours of HD Videos
43 Learning Paths
250+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
1500+ Hour of HD Videos
80 Learning Paths
360+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
3000+ Hours of HD Videos
149 Learning Paths
600+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Financial Analyst Masters Training Program1000+ Hours of HD Videos | 43 Learning Paths | 250+ Courses | Verifiable Certificate of Completion | Lifetime Access
All in One Data Science Bundle1500+ Hour of HD Videos | 80 Learning Paths | 360+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

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

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

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

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more