Search code examples
excelvbapowerpoint

Referenced Excel workbook in the code is nowhere to be seen in taskbar

I am trying to open a workbook from powerpoint using VBA. Although the workbook object is getting set but the workbook is no where to be seen. [ See snap at the end for clarity ]

Code:

Public Path As String

Sub CheckThisSub()

  Path = ActivePresentation.Path
  Debug.Print (Path & "\" & "QC_Log.xlsm") 'prints D:\QC\Test\QC_Log.xlsm

  Dim QCWbk As New Excel.Workbook
  Set QCWbk = Excel.Workbooks.Open(Path & "\" & "QC_Log.xlsm")
  'QCWbk is getting set but the excel QC_Log.xlsm is nowhere to be seen.

  Debug.Print QCWbk.Name                   'prints QC_Log.xlsm
  QCWbk.Windows.Item(1).Visible = True

' Rest of code

End sub

Snap:

Snap

Solution

  • So, creating one Excel session can be done in different ways.

    1. Since you modified your initial declaration from Dim QCWbk As Excel.Workbook to Dim QCWbk As New Excel.Workbook your code problem resides in the way you try setting Excel application. Your code tries to set a workbook, not the Excel application. A piece of working code starting from yours should be the next one:
    Dim QCWbk As New Excel.Workbook, wb as Excel.Workbook, Path As String 'Using New the Excel COM object is already instantiated:

Path = ActivePresentation.Path
Set wb = QCWbk.Workbooks.Open(Path & "\" & "QC_Log.xlsm") 'the workbook is set in this way!
QCWbk.Visible = True
    1. Excel application can be simple created without referencing Excel libraries. This is late binding way, but you do not benefit of intellisense offering the objects properties/methods:
    Dim QCWbk As Object, wb as Object, Path As String '

Path = ActivePresentation.Path
Set QCWbk = CreateObject("Excel.Application")
QCWbk.Visible = True
Set wb = QCWbk.Workbooks.Open(Path & "\" & "QC_Log.xlsm") 'the workbook is set in this way!
    1. You can use the early binding without pre-instantiate the COM object. You benefit of Intellisense offers, but you have to create the instance in a second step:
    Dim QCWbk As Excel.Workbook, wb as Excel.Workbook, Path As String 'Using New the Excel COM object is already instantiated:

Path = ActivePresentation.Path
Set QCWbk = CreateObject("Excel.Application")
QCWbk.Visible = True 
Set wb = QCWbk.Workbooks.Open(Path & "\" & "QC_Log.xlsm") 'the workbook is set in this way!
    1. You can get an existing Excel open session with the next code:
    Dim QCWbk Excel.Workbook, wb as Excel.Workbook, Path As String

Path = ActivePresentation.Path

On Error Resume Next
 Set QCWbk = GetObject(, "Excel.Application")
 If Err.Number <> 0 Then
     Err.Clear: On Error GoTo 0
     MsgBox "No Excel Open session..."
     Exit Sub 'the code exists if only an open session is required...
 End If
 'Usually in such a case a new session is created (as above). Here is only the part showing how to use an existing session
 Set wb = QCWbk.Workbooks.Open(Path & "\" & "QC_Log.xlsm") 'the workbook is set in this way!
 QCWbk.Visible = True
    1. An open Excel session can be found if you know the name of a workbook open in a specific session... For instance, if another application (SAP, for instance) exports such a workbook (named "Book1") in a new session, you can find that session in this way:
    Dim Ex As Excel.Application
Set Ex = GetObject("Book1").Application

Debug.Print Application.ActiveWindow.hwnd, Ex.ActiveWindow.hwnd
    1. Finally, you can identify all Excel open sessions using API (FindWindowEx, IIDFromString and AccessibleObjectFromWindow), but it is a little more complicated and need to be uses in specific circumstances...