Resource for Working With Other Applications? RRS feed

  • Question

  • Hello All,

    I have finished the first release of a program I've written in PowerPoint VBA but want to use an Excel workbook to act as the "Configuration" source for the PowerPoint product.  I believe this will be easier to use for the end user than keeping everything in PowerPoint slides or asking them to delve into the actual modules to make changes.  I have never worked between applications before.  Could someone point me towards a resource either online or in print that I should look into?



    • Edited by Grasor Tuesday, February 16, 2016 1:14 AM
    Tuesday, February 16, 2016 1:13 AM

All replies

  • You have 2 options:

    • Early Binding

    This is achieved by going to Tools -> References and selecting the "Microsoft Office 14.0 Object Library" option. (version number may be higher) This is will allow you to make use of IntelliSense which is much better if you're not completely comfortable with coding VBA for Excel.

    • Late Binding

    This is done by creating the object at execution time, using the CreateObject() method, this will not enable IntelliSense in the VBE because no reference has been set to that library.

    I would advise starting with Early Binding, and then using code such as this:

    Sub Foo()
        Dim xlApp As Excel.Application
        Dim xlWB  As Excel.Workbook
        Dim xlWS  As Excel.Worksheet
        Dim wbName As String
        wbName = "C:\Users\bloggsj\documents\MyWorkbook.xlsx"
        Set xlApp = New Excel.Application
        Set xlWB = xlApp.Workbooks.Open(wbName)
        Set xlWS = xlWB.Sheets("Sheet1") '// Change sheet name as required
        '// Display value in A1
        MsgBox xlWS.Range("A1").Value, vbInformation, "Test"
        '// Close everything down
        xlWB.Close SaveChanges:=False
    End Sub

    • Edited by Macro Man Tuesday, February 16, 2016 2:01 PM
    • Proposed as answer by Macro Man Tuesday, February 16, 2016 2:01 PM
    Tuesday, February 16, 2016 2:01 PM