CODEWITHSUNDEEP
vbaexcel
cohara
cohara

Reputation: 111

Accessing Excel file from word macro

I am trying to open an excel file from a Word Macro, and access data within the Excel file. However, I can only get as far a defining the exWb and I run into the error above. My code is:

Sub mySub()

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

    Dim exWb As Excel.Workbook
    'Dim exWb As Object
    'Set exWb = CreateObject("Excel.Workbook")

    ...

End Sub

The issue is with 

Dim exWb As Excel.Workbook

at which I get the error:

Compile error: User-defined type not defined

Or

Dim exWb As Object
Set exWb = CreateObject("Excel.Workbook")

At which I get the error:

Run-time error '429':
ActiveX component can't create object

Excel 2007 is installed on the PC, and I have added references to Microsoft Office 12.0 Object Library, and Microsoft ActiveX Data Objects 6.1 Library, but this does not help.

Does anyone have any suggestions?

Upvotes: 1

Views: 1992

Answers (2)

vacip
vacip

Reputation: 5406

You can do either of two things.
1:
Add reference to the excel object library (see @DisplayName's answer), and rewrite your code a bit:

Dim exApp As New Excel.Application
Dim exWb As Excel.Workbook
Set exWb = exApp.Workbooks.Add

This is early binding. Advantage is that it is easier to write the code, as intellisense is helping you. Disadvantage is that if someone tries to run it in a different Office version (e.g Excel 2013), it won't work.

2:
Don't add reference, and use this code:

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

You are trying to create an excel application. In this application you want to create a workbook (Set exWb = exApp.Workbooks.Add), etc.

This is late binding, which gives you hard time when coding, but will work regardless of Excel version.

I usually code in early binding, then rewrite the code to late binding when finished.

Upvotes: 2

DisplayName
DisplayName

Reputation: 13386

you have to Add Excel library reference

in VBA IDE, click

  • Tools
  • References
  • select Microsoft Excel XX.0 Object Library
  • "OK"

Upvotes: 1

Related Questions