Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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. Thanks!

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
share|improve this question
2  
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 yesterday

1 Answer

up vote 1 down vote accepted

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

into these ones:

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

and the code should work, too.

share|improve this answer
1  
This works wonder! Are there any explanations to this, or it is just how it works? – George yesterday
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 – KazJaw yesterday

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.