Opening an excel sheet from PPT

[Nasser]

Hi Hans,

I have created a menu in my excel program. I want to open this menu from my power point presentation using an activeX control "button". I have used this code but an error is coming.

Private Sub CommandButton1_Click()
Workbooks.Open "C:\Users\admc\Desktop\Tony\Menu.xlsm"
ActiveWorkbook.Sheets(1).Select
End Sub

From excel to excel the code is working nicely but from ppt to excel, error is coming.

Thanks,
Nasser.

[HansV]

"Workbooks" and "ActiveWorkbook" belong to Excel VBA, not to PowerPoint VBA. If you want to use Excel VBA in PowerPoint, you must use Automation and create an Excel application object:

• Activate the Visual Basic Editor.
• Select Tools | References...
• Locate the Microsoft Excel n.0 Object Library in the list; n is a number that depends on the version you have (n=10 for Excel 2002, n=11 for Excel 2003, n=12 for Excel 2007 and n=14 for Excel 2010)
• Tick the check box for this library and click OK.

You can now use Excel VBA.

Change the code as follows:

Code: Select all

Private Sub CommandButton1_Click()
  Dim xlApp As Excel.Application
  Dim xlWbk As Excel.Workbook
  Set xlApp = CreateObject("Excel.Application")
  Set xlWbk = xlApp.Workbooks.Open("C:\Users\admc\Desktop\Tony\Menu.xlsm")
  xlWbk.Sheets(1).Select
  xlApp.Visible = True
End Sub

[Nasser]

Hi Hans,

From PPT, after clicking on the button, an error is coming:

Compile error:
User-defined type not defined
and "Dim xlApp As Excel.Application" declaration is highlighted.

Thanks,
Nasser.

[HansV]

Have you set a reference to the Microsoft Excel n.0 Object Library, as I described in my previous reply?

[Nasser]

Sorry i forgot that. Great it's working :)

Thank you Hans
Cheers,
Nasser.