Monday, February 15, 2010

Run Excel Macro from Powerpoint VBA





How to Run an Excel Macro from PowerPoint

Before writing code for doing it you need to add Excel Library to the PowerPoint VBE Project

Excel VBA and Power Point VBAPowerpoint VBE Screen

EXcel VBA and Powerpoint VBAExcel Library in the References

This can be done from Powerpoint VBE-->Tools-->References -->Browse for the particular reference and add them.

We have the Excel macros embedded in a workbook (CanBeDeleted.xlsm)



Sub AnotherWrkBook_Macro()

MsgBox "I have Run!"


End Sub



Above code is a simple message box. The code below, however, accepts an argument and stores the same in the workbook


Function Store_Value(ByVal sPPTName As String)

Sheet1.Range("A2").Value = sPPTName


End Function



The following Powerpoint VBA code uses Application.Run method of Excel VBA to execute a particular macro.

Multiple arguments can be passed to Application.Run method


Sub Run_Excel_Macro_From_PPT()

Dim oXL As Excel.Application ' Excel Application Object
Dim oWB As Excel.Workbook ' Excel Workbook Object
Dim sPName As String ' Variable - Active Presentation Name

On Error GoTo Err_PPXL

' -----------------------------------------------------------
' coded by Shasur for http://vbadud.blogspot.com
' -----------------------------------------------------------

Set oXL = New Excel.Application
Set oWB = oXL.Workbooks.Open("C:\Users\comp\Documents\CanBeDeleted.xlsm")

' Set Excel as Visibile - Turn Off if not needed
oXL.Visible = True

' Pass and Argument
sPName = ActivePresentation.Name

' Run the Macro without Argument
oXL.Application.Run "'CanBeDeleted.xlsm'!AnotherWrkBook_Macro"

' Run the Macro without Argument
oXL.Application.Run "'CanBeDeleted.xlsm'!Store_Value", sPName

' Save and Close the Workbook
oWB.Save
oWB.Close (False)


' Quit the Excel
oXL.Quit


' Release Objects - Good Practive
If Not oWB Is Nothing Then Set oWB = Nothing
If Not oXL Is Nothing Then Set oXL = Nothing


Err_PPXL:
If Err <> 0 Then
MsgBox Err.Description
Err.Clear
End If
End Sub




The macro saves and closes the workbook and quits Excel

See also:


Execute a macro in a different workbook

Run a Automatic Macro in Word Document

7 comments:

  1. Please could you help me with this code..
    I am try to automate PPT from Excel using Macros.

    I am Nithiya.I am currently working on excel macros.
    I came across ur solution in techrepublic and was really suprised to see your codes on macros..I really apprecaite and honour your coding skills.
    - Hide quoted text -


    I am in need of your help.

    I am currently working on a requirement where the excel shoud automate powerpoint..

    For eg
    Jan Feb Mar
    East 10 30 40
    West 13 14 45
    South 34 35 33

    Each row in the excel should generate a seperate slide.
    So as per the data we r supposed to have 3 slides for each row..

    I was able to code in such a way it works for a single slide reading data from the first row in the excel sheet and get updated automatically when the months grows
    But i am not able to get for more than one slide.Please can you give me a clue to continue..
    I am really struck with this

    I really thank you for your time.

    Keep up with your Good work.
    Thanks

    My code is pastes below written in MS xl 2003
    Sub Chart2PPT()
    Dim objPPT As Object
    Dim objPrs As Object
    Dim objGraph As Object
    Dim objDataSheet As Object
    Dim rngData As Range
    Dim intRow As Integer
    Dim intCol As Integer

    ' excel chart data
    Set rngData = Range("A1:J2")
    ' open powerpoint
    Set objPPT = CreateObject("Powerpoint.application")
    objPPT.Visible = True
    ' existing powerpoint pres
    objPPT.Presentations.Open "C:\PPt\Call_volume.ppt"
    ' chart on slide 2
    Set objPrs = objPPT.Presentations(1).slides(2)
    ' pointer to graph
    Set objGraph = objPrs.Shapes(2).OLEFormat.Object.Application
    ' pointer to graphs data sheet
    Set objDataSheet = objGraph.Datasheet
    ' transfer data
    For intRow = 1 To rngData.Rows.Count
    For intCol = 1 To rngData.Columns.Count
    objDataSheet.Cells(intRow, intCol) = rngData.Cells(intRow, intCol)
    Next
    Next
    ' update to keep changes
    objGraph.Update
    objGraph.Quit
    objPPT.Presentations(1).Save


    ' tidy up objects
    Set rngData = Nothing
    Set objGraph = Nothing
    Set objDataSheet = Nothing
    Set objPrs = Nothing
    Set objPPT = Nothing
    End Sub

    ReplyDelete
  2. Hi Nithiya

    Do you want to add more slides. Is that where you face problem?

    ReplyDelete
  3. I know this is an old thread, but for prosperity sake and for others who might be interested in this article...

    It appears that the range 'rngData' was set early on to only allow two rows (A1:J2). The first would be the headers Jan, Feb, Mar, etc. The next row would be the first row of data only. This range would need to be increased to at least (A1:M5) to include all months and all directions East, West, South, & North per your example.

    ReplyDelete
  4. Anonymous8:47 PM

    hello

    ReplyDelete
  5. Anonymous8:27 AM

    PP = New PowerPoint.Application()
    versa
    PP = CType(GetObject(Class:="PowerPoint.Application"), PowerPoint.Application)
    as GetObject is not alway supported. So if you are using New PowerPoint.Application it will always create an instance of PPT and so how do you see if it running?

    ReplyDelete
  6. Thank you - that was very helpful!

    In addition to above, I then need for format the Excel file from PowerPoint (before I close the Excel file. E.g.:

    Range(“A1:L1″).Select ‘format header
    With Selection
    .Font.Bold = True
    .Interior.ColorIndex = 6
    .Interior.Pattern = xlSolid
    .Font.ColorIndex = 5
    End With

    How can I also include the Excel formatting commands in the PowerPoint macro to send/execute formatting in Excel?

    [I am aware I could put a macro in Excel to format and run via:

    Set oWB = oXL.Workbooks.Open(oXL.StartupPath & “\Personal.xls”)
    oWB.SaveAs FileName:=FileXLS, FileFormat:=xlNormal

    ... however because I am also creating the Excel file thru PowerPoint, having to maintain a separate macro in Excel to run the formatting means any changes have to be updated in 2 separate locations and it limits the portability of the PowerPoint since the formatting macro will not exist in another users Excel (without alot of extra work.]



    Thank you - that was very helpful!

    In addition to above, I then need for format the Excel file from PowerPoint (before I close the Excel file). E.g.:

    Range(“A1:L1″).Select ‘format header
    With Selection
    .Font.Bold = True
    .Interior.ColorIndex = 6
    .Interior.Pattern = xlSolid
    .Font.ColorIndex = 5
    End With

    How can I also include the Excel formatting commands in the PowerPoint to reference/execute in Excel?

    [I am aware I could put a macro in Excel to format and run via:

    Set oWB = oXL.Workbooks.Open(oXL.StartupPath & “\Personal.xls”)
    oWB.SaveAs FileName:=FileXLS, FileFormat:=xlNormal

    ... however because I am also creating the Excel file thru PowerPoint, having to maintain a separate macro in Excel to run the formatting means any modifications may have to be updated in 2 separate locations (e.g. change the range to format) and it limits the portability of the PowerPoint since the formatting macro will not exist in another users Excel (without a lot of extra work - for some formatting which will only need to run the 1 time the Excel file is created) and the Excel file doesn't exist until it's created thru PowerPoint (so there's no Excel file to save the macro in.) ]

    Any assistance is greatly appreciated.

    ReplyDelete
  7. I wanted to use this in one of my presentations. On my desktop, it's fine. But when it's running from laptop, the popup window to enter name is running behind the presentation. Is there any fix for this issue? Thanks, Ash

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO

microsoft.public.vsnet.vstools.office Google Group