Reference an open Excel file from PowerPoint using VBA

I found the following VBA code for PowerPoint which works and allows PowerPoint to reference an Excel file

Private Sub test()
Dim xlApp As Object
Dim xlWorkBook As Object

Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = True
Set xlWorkbook = xlApp.Workbooks.Open("C:\lol\Book1.xlsx", True, False)
xlWorkbook.sheets(1).Range("A8").Value = "Hello"

Set xlApp = Nothing
Set xlWorkbook = Nothing

End Sub

However, this code opens the Excel and then reference to it. But what if the Excel file I want to reference to is already opened? I tried to change the code to below, but that doesn't seem to work. Any ideas how to reference an already opened Excel file from VBA in PowerPoint?

Private Sub test()
Dim xlApp As Object
Dim xlWorkBook As Object

Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = True
Set xlWorkbook = xlApp.Workbooks("Book1.xlsx")
xlWorkbook.sheets(1).Range("A8").Value = "Hello"

Set xlApp = Nothing
Set xlWorkbook = Nothing

End Sub

Solution

Reference an Open Excel Workbook by its Name from PowerPoint

... or from Word, Outlook, Publisher...etc.

Your first code creates a new instance of Excel and opens a specific workbook. If the workbook is already open in another instance of Excel, the code will fail.
Your second code also creates a new instance of Excel and fails when attempting to reference the specific workbook because it is certainly not open in this new instance.
To reference an existing instance of Excel, you need to use the GetObject function:
```
Set xlApp = GetObject(, "Excel.Application")
```
Then, if there is no open instance of Excel, an error will occur, and no reference will be created.

The Function

You could utilize the following function.

Function RefOpenWorkbook( _
    ByVal WorkbookName As String, _
    Optional ByVal DisplayMessages As Boolean = True) _
As Object
    Const PROC_TITLE As String = "Reference an Open Workbook"
    
    Dim xlApp As Object:
    On Error Resume Next
        Set xlApp = GetObject(, "Excel.Application")
    On Error GoTo 0
    
    If xlApp Is Nothing Then
        If DisplayMessages Then
            MsgBox "No instance of Excel open!", _
                vbExclamation, PROC_TITLE
        End If
        Exit Function
    End If
    
    Dim wb As Object:
    On Error Resume Next
        Set wb = xlApp.Workbooks(WorkbookName)
    On Error GoTo 0
    
    If wb Is Nothing Then
        If DisplayMessages Then
            MsgBox "No workbook named """ & WorkbookName & """ open!", _
                vbExclamation, PROC_TITLE
        End If
        Exit Function
    End If
    
    Set RefOpenWorkbook = wb

End Function

A Test

Test the function with the following code.


Sub Test()

    ' Reference the workbook by its name.
    Dim wb As Object: Set wb = RefOpenWorkbook("Book1.xlsx")
    If wb Is Nothing Then Exit Sub
    
    ' If it was referenced, do your thing.
    wb.Sheets(1).Range("A8").Value = "Hello"

    ' Bonus: About Saving, Closing and Quitting
    
    ' Before saving the workbook, make sure it has a path, i.e.,
    ' it was saved before!
    ' Otherwise you need to use 'SaveAs' specifying the path and format.
    If Len(wb.Path) = 0 Then
        MsgBox "The workbook was never saved!", vbExclamation
        Exit Sub
    End If

    ' Use only one of the following three. Preferably the first, or none.
    
    ' Save.
    'wb.Save

    ' Since the workbook was initially open, you usually don't want to close it.
    ' You also don't want to quit Excel, especially if there could be
    ' other workbooks open. But if you insist, the following is how to do it.

    ' Save and close.
    'wb.Close True ' where 'True' means 'save changes'

    ' Save and close and quit Excel.
'    With wb.Application ' references Excel
'        wb.Close True ' where 'True' means 'save changes'
'        .Quit
'    End With

End Sub