excel - 代码中引用的 Excel 工作簿在任务栏中无处可见

Question

我正在尝试使用 VBA 从 powerpoint 打开工作簿。虽然工作簿对象正在设置，但工作簿无处可见。 [为清楚起见，请参阅最后的快照]
代码:

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

折断:

Answer 1

因此，可以通过不同的方式创建一个 Excel session 。

由于您从 Dim QCWbk As Excel.Workbook 修改了初始声明至Dim QCWbk As New Excel.Workbook您的代码问题在于您尝试设置 Excel 应用程序的方式。您的代码尝试设置工作簿，而不是 Excel 应用程序。从你开始的一段工作代码应该是下一个:

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 

无需引用 Excel 库即可轻松创建 Excel 应用程序。这是后期绑定(bind)方式，但您不会从提供对象属性/方法的智能感知中受益:

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!

您可以使用早期绑定(bind)而无需预先实例化 COM 对象。您可以从 Intellisense 优惠中受益，但您必须在第二步中创建实例:

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!

您可以使用以下代码获取现有的 Excel 打开 session :

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

如果您知道在特定 session 中打开的工作簿的名称，则可以找到打开的 Excel session ......例如，如果另一个应用程序(例如 SAP)在新 session 中导出这样的工作簿(名为“Book1”)，您可以通过以下方式找到该 session :

Dim Ex As Excel.Application
Set Ex = GetObject("Book1").Application

Debug.Print Application.ActiveWindow.hwnd, Ex.ActiveWindow.hwnd

最后，您可以使用API​​(FindWindowEx，IIDFromString和AccessibleObjectFromWindow)识别所有Excel打开 session ，但它有点复杂，需要在特定情况下使用......