Reference and access Excel from Outlook

Question

I'm trying to update an Excel file from Outlook (Office 2010). How do I reference and access Excel?

As a simple test I'm trying to count the number of open workbooks. When I run this I get 0, even though there are 2 open.

Sub Test()
    Dim xlApp As Excel.Application
    Dim xlWBook As Excel.Workbook

    Set xlApp = New Excel.Application
    Debug.Print "xlApp.Workbooks.Count = " & xlApp.Workbooks.Count

    On Error Resume Next
    Set xlWBook = xlApp.Workbooks("Data.xlsx")
    Err.Clear 'Clear error and open File Index

    If  xlWBook Is Nothing Then
        Set xlWBook = xlApp.Workbooks.Open("C:\Users\Chris\Desktop\Data.xlsx")
    End If
End Sub

You are creating a new Excel instance - which is separate to the one or more instances (containing your open two workbooks) — brettdj, Jun 26 '12 at 13:59
Sounds like a messy way to operate. Better to open the workbooks you need in a (new) instance of Excel that your code owns. — Jean-François Corbett, Jun 26 '12 at 14:55

SeanC · Accepted Answer · 2012-06-27T16:37:44.033

4

This is what I use to detect Excel:

Dim xlApp As excel.Application
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err.Number = 429 Then 'Excel not running
    Set xlApp = CreateObject("Excel.Application")
End If
On Error GoTo 0

once xlApp is set, you can use xlApp.Workbooks.Count to count the worksheets

Note: This code will get the first one opened, if there are more than one instance of Excel to find

If you have to find a specific workbook, from this page, Set xlApp = GetObject("Book2").Application will find the workbook, even if it's not in the first instance. If the workbook is from a file, or already saved, replace book2 with the full path and filename - Side effect - this will also open the file if it is not already open More usage info: http://msdn.microsoft.com/en-us/library/aa164798%28v=office.10%29.aspx

edited Jun 27 '12 at 16:37

answered Jun 26 '12 at 13:58

SeanC

15,695
5
45
66

1

This won't work if there is more than 1 existing instance of Excel open – brettdj Jun 26 '12 at 14:00
True, but I use this code trying not to open any more instances than I need to – SeanC Jun 26 '12 at 14:02
Awesome, thanks so much. I've never used the GetObject and SetObject - but they are now added to my repertoire. Thanks again. – Chris Day Jun 26 '12 at 14:25
1

Ah, I see. That gets the first instance of Excel. So if Data.xlsx is open in any other instance, I will not see it. Is there a way around this? I need to account for any eventuality... – Chris Day Jun 26 '12 at 14:30
http://support.microsoft.com/kb/288902 - from this page, `Set xlApp = GetObject("Book2").Application` will find the workbook, even if it's not in the first instance – SeanC Jun 26 '12 at 14:43
moved my previous comment to the answer – SeanC Jun 26 '12 at 19:06
1

@Sean: Am I missing something? From the same link that you posted: `If multiple instances of Microsoft Excel are running, GetObject attaches to the instance that is launched first. If you then close the first instance, another call to GetObject attaches to the second instance that was launched, and so forth.` – Siddharth Rout Jun 26 '12 at 19:18
if you use the `Set xlApp = GetObject("Book2").Application` instead of the `Set xlApp = GetObject(, "Excel.Application")` then it will attach to the correct instance – SeanC Jun 26 '12 at 19:42
Have you tested the above code with two different instances of Excel? I am not able to get it work... – Siddharth Rout Jun 26 '12 at 19:48
1

you have to use either the "book" name if not saved, or the full path if it is, otherwise it can cause issues. I've switched back and forth between 3 different instances using that method – SeanC Jun 26 '12 at 19:57
`or the full path if it is...` + 1 Ah! I was just using the name. Nice catch! You might want to add this additonal info in your above post? – Siddharth Rout Jun 26 '12 at 19:59
Perhaps we are causing a problem with our solution on top of a solution. It looks like `Set xlApp = GetObject("C:\Users\Chris\Desktop\File Index.xlsx").Application` creates an instance of the file passed. I'm getting 0 error when I run that line, regardless of if the file is open or not. @SeanCheshire @SiddharthRout – Chris Day Jun 27 '12 at 16:34

Reference and access Excel from Outlook

1 Answers1

Linked