Exchanging variables between powerpoint and excel VBA

1
votes

I have a powerpoint pres that grabs some data from an excel sheet when a button is pressed.

Set EXL = New Excel.Application
EXL.Visible = False

Dim XLApp As Excel.Application

Set XLApp = GetObject(, "Excel.Application")

This is how I set the new excel application.

What I'm wondering is how I can send over a variable from my powerpoint slide into the excel workbook? I have textfield in my powerpoint slide that I want the text to be used in a variable inside excel. Is this possible? If so, how?

And how do I, from a powerpoint module, call a Sub within the excel workbook to run?

2
excelvbavariablespowerpoint

2 Answers

2
votes

(This is some simplified production code from an Access db, powerpoint might have a few minor differences)

What I'm wondering is how I can send over a variable from my powerpoint slide into the excel workbook? 

Sub SetXLCellValue( _
    FileStr As String, _
    TabStr As String, _
    Cell As String)

    Dim XLApp As New Excel.Application
    Dim ObjXL As Excel.Workbook
    Set ObjXL = XLApp.Workbooks.Open(FileStr)

    ObjXL.Worksheets(TabStr).Range(Cell).value = value
    ObjXL.Save
    ObjXL.Close True
End Sub
0
votes

As for calling a Sub in your Excel application, you can use

XLApp.Run("MySub")

This also has the ability to pass parameters to the method (intellisense should show you the way)