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. :)
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.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With