Link and access to Excel from Outlook

Question

I am trying to update an Excel file from Outlook (Office 2010). How do I contact and access Excel?

As a simple test, I am trying to count the number of open books. When I run this, I get 0, although 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

Answer 1

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

after installing xlApp, you can use xlApp.Workbooks.Countto count worksheets

Note. This code will open first if there is more than one instance of Excel to search

, , Set xlApp = GetObject("Book2").Application , . , book2 - - , : http://msdn.microsoft.com/en-us/library/aa164798%28v=office.10%29.aspx