Finding a workbook in one of multiple Excel instances

Question

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

I reference the workbook as follows:

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

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

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

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

Try with writing full path of excel file in line: Set wb = objApp.Workbooks("Workbook1.xlsx"). e.g. Set wb = objApp.Workbooks("D:\my file\Workbook1.xlsx") — J.B., Jan 18 '17 at 10:22
AFAIK, Using an Excel application object to access a work book needs your workbook to be closed, But I think you can access it using an OleDB connection string - HTH ;). — shA.t, Jan 18 '17 at 10:46
Thanks, I have it working for an open workbook but it sometimes fails. I need the workbook to be open because i have formulas which to not otherwise load fast enough if i am opening-closing. I have tried a direct file reference too but i think if the excel instance does not contain the workbook, then even an explicit filepath reference will not work.. — Freelancer, Jan 18 '17 at 16:42

0m3r · Accepted Answer · 2018-08-14T18:25:22.120

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
    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

score 1 · Answer 2 · answered Jan 18 '17 at 10:29

1

I suspect that changing the file name to add in the full file path would work, so try changing the line:

Set wb = objApp.Workbooks("Workbook1.xlsx")

To something like:

Set wb = objApp.Workbooks("C:\Users\Documents\Workbook1.xlsx")

answered Jan 18 '17 at 10:29

Ben Smith

809
5
21
46

Hihi, Thanks for your input. I just tried this but i couldnt get it to work. I looked at the objApp object in the debugger, the workbook count is set to zero. Som though we specified a specific workbook, it did not find any workbook at all. – Freelancer Jan 18 '17 at 16:22
Is the extension correct? E.g. is ".xlsx" correct or is it ".xlsm" or another variation? – Ben Smith Jan 18 '17 at 16:32
yes, the name is fine. Sometimes the macro works, and sometimes not... the error occurs, if at all, on the line where i reference the wb. the out of bounds error – Freelancer Jan 18 '17 at 16:35

score 1 · Answer 3 · edited Dec 01 '22 at 09:56

1

If you want to set the objApp to a particular instance of Excel,

you can do so using the name of an open workbook in that instance:

Dim objApp As Excel.Application
Dim objWbk As Excel.Workbook
Dim wB As Excel.Workbook
Set objWbk = GetObject("Workbook1.xlsx")
Set objApp = objWbk.Application
Set wB = objApp.Workbooks("Workbook1.xlsx")

edited Dec 01 '22 at 09:56

gawi

2,843
4
29
44

answered Jan 18 '17 at 10:46

R3uK

14,417
7
43
77

Hello, I tried using the line you gave to specify which instance objApp should be assigned to but I got the error: Automation error Invalid Syntax. Set objApp = GetObject("FundRFQ.xlsx").Application – Freelancer Jan 18 '17 at 16:23
@Freelancer : I've decomposed the command line so that you know where is the error coming from. Let me know what it says! ;) – R3uK Jan 23 '17 at 09:26

Finding a workbook in one of multiple Excel instances

3 Answers3

Linked