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

问题描述

我在Outlook VBA中有一个宏，需要从打开的Excel工作簿（ Workbook1 ）中获取数据。

我参考我需要选择的工作簿如下：

  Dim objApp As Excel.Application 
 Set objApp = GetObject（，Excel.Application）
设置wb = objApp.Workbooks（Workbook1.xlsx）
  

但是，通常我会得到运行时错误9 ，该VBA找不到该工作簿。

我认为问题可能是因为我打开了多个Excel实例， VBA正在错误的实例中查找我的工作簿。

由于我运行了许多Excel，所以对我来说是非常重要的。

解决方案

尝试这个

---

  Option Explicit 
 Public Sub Example（）
 Dim xlApp As Excel。应用程序
 Dim Book As Workbook 
 
设置xlApp =新建Excel.Application 
 Set Book = xlApp.Workbooks.Open（Environ（_ 
USERPROFILE）& \Documents\Temp\Temp.xlsm）
 
'做某事
 
设置xlApp =没有
设置书=没有
结束Sub 
  

或这适用于我。

< hr>

  Option Explicit 
 Public Sub Example（）
 Dim xlApp As Excel.Application 
 Dim Book As Excel.Workbook 
 Dim Sht As Excel.Worksheet 
 D im xlStarted As Boolean 
 Dim FilePath As String 
 Dim Cell As Range 
 Dim Rng As Range 
 
'//文件路径
 FilePath =C： \Temp\Temp.xlsx
 Debug.Print FilePath 
 
'//如果错误获取Excel应用程序
 On Error Resume Next 
设置xlApp = GetObject（ ，Excel.Application）
 
如果Err<> 0然后
 Application.StatusBar =请等待Excel源打开...
设置xlApp = CreateObject（Excel.Application）
 xlStarted = True 
结束If 
 
'//打开工作簿，Sheet1获取数据
 Set Book = xlApp.Workbooks.Open（FilePath）
 Set Sht = Book.Sheets（Sheet1）
 
'//设置范围变量
设置Rng = Sht.Range（A1）
 
每个单元格在R $ 
 Debug.Print Cell.Value 
下一个
 
 
'//关闭& SaveChanges 
 Book.Close SaveChanges：= True 
如果xlStarted然后
 xlApp.Quit 
如果
 
设置xlApp = Nothing 
设置书=没有
 Set Sht = Nothing 
 End Sub 
  

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

I reference the workbook I need to select as follows:

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

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

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

It is important for me to have separate instances since I am running many Excels..

How do I reference my workbook when running more than one excel instance?

解决方案

Try This

---

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") & "\Documents\Temp\Temp.xlsm")

    ' Do something

    Set xlApp = Nothing
    Set Book = Nothing
End Sub

Or This which works for me.

---

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:\Temp\Temp.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

'   // 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屋！