Results 1 to 3 of 3

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

  1. #1
    VBAX Regular
    Feb 2021

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

    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!

  2. #2
    VBAX Regular
    Feb 2021
    Perhaps ensuring Microsoft Excel 16.0 Object Library is enabled in my Outlook VBA references?!

  3. #3
    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
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts