VBA: Reading excel data into word

Question

I am making a simple form that extract the data from my excel sheet, such as name, date of birth, and address. And inserting them into my word form, I am doing 20-30 sheets everytime, so I think it might be able to save the copying & pasting time.

I tried to follow this tutorial: http://www.makeuseof.com/tag/integrate-excel-data-word-document/ And created a button with a simple label named m_name, for member's name. But it tells me Compile error: User-defined type not defined. And flaged on line 1.

I am using Word 2003, (I am not able to find the Tools > Reference as the guide was asking for). I am not sure if it is related to this error.

Private Sub CommandButton1_Click()
Dim objExcel As Excel.Application
Dim exWb As Excel.Workbook

Set exWb = objExcel.Workbooks.Open("U:\test.xls")
ThisDocument.m_name.Caption = exWb.Sheets("Member's Data").Cells(3, 3)

exWb.Close

Set exWb = Nothing

End Sub

You need a reference to the "Microsoft Excel [version] Object library". There should be a Tools>>References menu option in the Word VB Editor. — Tim Williams
– Tim Williams, Commented Jun 7, 2013 at 20:45

Kazimierz Jawor · Accepted Answer · 2019-06-27 06:00:35Z

4

Yes, it's very important to set references according to the tutorial.

However, change these two lines:

Dim objExcel As Excel.Application
Dim exWb As Excel.Workbook

to:

Dim objExcel As Object
Set objExcel = CreateObject("Excel.Application")

and the code should work, too.

edited Jun 27, 2019 at 6:00

answered Jun 7, 2013 at 20:44

Kazimierz Jawor

19.1k7 gold badges38 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

George Over a year ago

This works wonder! Are there any explanations to this, or it is just how it works?

Kazimierz Jawor Over a year ago

If you can't set references according to tutorial (which is called 'early-binding') you could do it with technique proposed by me (which is called 'late-binding'). For further information please read this

Collectives™ on Stack Overflow

VBA: Reading excel data into word

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related