Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Open Excel file in VBA from Powerpoint

Tags:

powerpoint

vba

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. :)

like image 291
Electrifyings Avatar asked May 26 '12 02:05

Electrifyings


People also ask

Can you open an Excel spreadsheet from PowerPoint?

In PowerPoint, on the Insert tab, click or tap Object. In the Insert Object dialog box, select Create from file. Click or tap Browse, and in the Browse box, find the Excel workbook with the data you want to insert and link to. Before you close the Insert Object box, select Link, and click OK.

How do I open a VBA file in PowerPoint?

Press ALT+F11 to start the VBA editor. Or click the Office button, choose PowerPoint Options, click Popular and put a check next to Show Developer tab in Ribbon. Close the options dialog box, click the Developer tab then click Visual Basic to start the editor.

Can you use VBA in PowerPoint?

PowerPoint VBA provides you with a way to do one of two things using macros and add-ins: Automate PowerPoint: If you ever find yourself repeating the same task over and over again, VBA could be your new best friend. Let's say you have 100 slides and you need to unhide all hidden objects across all those slides.


1 Answers

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.

like image 122
Bobsickle Avatar answered Oct 02 '22 03:10

Bobsickle