From Outlook, read value of a cell in a closed (or open) Excel file

[pebcak]

While writing an email in Outlook, I want to run a script that will read the value of a cell in an Excel file that may or may not be open. (There's more to it than that, but I'll be able to figure the rest out once I figure out how to get Outlook to read the Excel sheet).

But I can't even get Outlook to create an instance of Excel...



Dim excelApp as Object
set ExcelApp = createobject("Excel.Application")


gives me errors.

Can someone get me started on reading the value of cell D4 in c:\myfile.xls from Outlook without having to open the Excel file?

I would be very grateful!! THANK YOU!

[pebcak]

Perhaps ensuring Microsoft Excel 16.0 Object Library is enabled in my Outlook VBA references?!

[gmayor]

You don't need the Excel object library reference. This is fast if Excel is already open. Extracting the cell without opening Excel is a challenge without seeing the worksheet.


Sub Macro1()
Dim xlApp As Object
Dim xlWB As Object
Dim bXL As Boolean, bWB As Boolean
Dim sText As String
Const sPath As String = "C:\"
Const sWB As String = "myfile.xls"
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Set xlApp = CreateObject("Excel.Application")
bXL = True
End If
On Error GoTo 0
xlApp.Visible = True
For Each xlWB In xlApp.workbooks
If xlWB.Name = sWB Then
bWB = True
Exit For
End If
Next xlWB
If bWB = False Then
Set xlWB = xlApp.workbooks.Open(sPath & sWB)
End If
sText = xlWB.sheets(1).Range("D4")
Debug.Print sText 'do something with sText
xlWB.Close 0
If bXL = True Then xlApp.Quit
Set xlWB = Nothing
Set xlApp = Nothing
End Sub