Open Excel file in VBA from Powerpoint

Question

I'm trying to open the Excel file using VBA in Powerpoint 2010 with the help of following code.

Private Sub CommandButton1_Click()
Dim xlApp As Excel.Application
Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = True

xlApp.Workbooks.Open "C:\lol\Book1.xlsx", True, False
Set xlApp = Nothing

Range("A8").Value = "Hello"
End

But I'm getting the following error.

Compile Error User Defined type not defined.

Am I missing something. Can anyone share the sample piece of code to open an excel file, change a cell value and close Excel file in Powerpoint 2007 and 2010 using VBA.

I have searched a lot and tried different pieces of code, but getting the same error everytime. :(

Thanks in advance. :)

Bobsickle · Accepted Answer · 2012-05-26 04:20:22Z

10

Have you added a reference to the Excel Object Model? That would save you having to use the late bound objects (and you get the benefit of having the Intellisense help when you are coding).

You need to go to Tools -> References and check the "Microsoft Excel v.x Object Library" (I think that number changes depending on the version of office you are using.

Your code should work if you do that, you should also remove the

CreateObject("Excel.Application")

line and replace it with

Set xlApp = new Excel.Application

And move the

Set xlApp = nothing

line to the end of your subroutine.

The rest of your code looks fine to me.

answered May 26, 2012 at 4:20

Bobsickle

1,7491 gold badge12 silver badges15 bronze badges

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

1 Comment

Electrifyings Over a year ago

Thanks a lot, worked fine after adding the reference to Excel Object Model. But can you please give me the sample code which will work without referencing the excel object model?

Bobsickle · Accepted Answer · 2012-05-26 07:02:03Z

4

Late binding code would be this

Private Sub test()
Dim xlApp As Object
Dim xlWorkBook As Object

Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = True
Set xlWorkbook = xlApp.Workbooks.Open("C:\lol\Book1.xlsx", True, False)
xlWorkbook.sheets(1).Range("A8").Value = "Hello"

Set xlApp = Nothing
Set xlWorkbook = Nothing


End Sub

It's better to use early binding though.

answered May 26, 2012 at 7:02

Bobsickle

1,7491 gold badge12 silver badges15 bronze badges

Collectives™ on Stack Overflow

Open Excel file in VBA from Powerpoint

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related