在多个 Excel 实例之一中查找工作簿 [英] Finding a workbook in one of multiple Excel instances

问题描述

我在 Outlook VBA 中有一个宏可以从打开的 Excel 工作簿(Workbook1")中获取数据.

I have a macro in Outlook VBA to grab data from an open Excel workbook ("Workbook1").

我参考工作簿如下:

Dim objApp As Excel.Application
Set objApp = GetObject(, "Excel.Application")
Set wb = objApp.Workbooks("Workbook1.xlsx")

我经常收到运行时错误 9，即 VBA 找不到工作簿.

I often get runtime error 9, that VBA cannot find the workbook.

我认为由于我打开了多个 Excel 实例，VBA 正在错误的实例中寻找我的工作簿.

I think since I have more than one Excel instance open, VBA is looking for my workbook in the wrong instance.

如何在运行多个 Excel 实例时引用我的工作簿?

推荐答案

试试这个

Option Explicit
Public Sub Example()
    Dim xlApp As Excel.Application
    Dim Book As Workbook

    Set xlApp = New Excel.Application
    Set Book = xlApp.Workbooks.Open(Environ( _
                        "USERPROFILE") & "DocumentsTempTemp.xlsm")

    ' Do something

    Set xlApp = Nothing
    Set Book = Nothing
End Sub

或者这对我有用.

Option Explicit
Public Sub Example()
    Dim xlApp As Excel.Application
    Dim Book As Excel.Workbook
    Dim Sht As Excel.Worksheet
    Dim xlStarted As Boolean
    Dim FilePath As String
    Dim Cell As Range
    Dim Rng As Range

'   // File Path
    FilePath = "C:TempTemp.xlsx"
    Debug.Print FilePath

'   // If Error get Excel Application
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")

    If Err <> 0 Then
        Application.StatusBar = "Please wait while Excel source is opened ... "
        Set xlApp = CreateObject("Excel.Application")
        xlStarted = True
    End If
    On Error GoTo 0

'   // Open Workbook, Sheet1 to get data
    Set Book = xlApp.Workbooks.Open(FilePath)
    Set Sht = Book.Sheets("Sheet1")

'   // Set range variable
    Set Rng = Sht.Range("A1")

    For Each Cell In Rng
        Debug.Print Cell.Value
    Next


    '// Close & SaveChanges
    Book.Close SaveChanges:=True
    If xlStarted Then
        xlApp.Quit
    End If

    Set xlApp = Nothing
    Set Book = Nothing
    Set Sht = Nothing
End Sub

这篇关于在多个 Excel 实例之一中查找工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！