3

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
Deduplicator
  • 44,692
  • 7
  • 66
  • 118
George
  • 4,514
  • 17
  • 54
  • 81
  • 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 Jun 07 '13 at 20:45

1 Answers1

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.

Kazimierz Jawor
  • 18,861
  • 7
  • 35
  • 55
  • 1
    This works wonder! Are there any explanations to this, or it is just how it works? – George Jun 07 '13 at 20:52
  • 1
    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](http://support.microsoft.com/kb/245115/en-us) – Kazimierz Jawor Jun 07 '13 at 20:55