Read the cell value without opening the workbook

Question

Set ObjWB = Workbooks.Open("c:\Test.xlsx")

If I used workbooks.open command, that Excel workbook opens.

I need without open that Excel workbook to read the cell value.

You cannot read a file without opening it. But you can open that file, read the values you need and close it. — Pᴇʜ, Oct 04 '17 at 10:00
If it is really something you need then search for "indirect.ext". Personally i'd open the workbook, retrieve the values, close the workbook. — Zerk, Oct 04 '17 at 10:03

score 0 · Answer 1 · answered Oct 04 '17 at 10:42

Run the Sub GetValue after setting the parameters in ReadFromClosedWorkbook (Workbook & Worksheet). You could pass either or both of them as arguments from the calling procedure.

Sub GetValue()
    Debug.Print ReadFromClosedWorkbook("A1")
End Sub

Private Function ReadFromClosedWorkbook(Target As String) As Variant

    Const WbFullName = "D:\My Documents\Your file name.xlsx"

    Dim PathName As String
    Dim WbName As String
    Dim WsName As String
    Dim Target As String
    Dim Sp() As String

    WsName = "My Worksheet's Name"

    Sp = Split(WbFullName, "\")
    WbName = Sp(UBound(Sp))
    ReDim Preserve Sp(UBound(Sp) - 1)
    PathName = Join(Sp, "\") & "\"
    If Len(Dir(WbFullName)) Then
        Target = "'" & PathName & _
                 "[" & WbName & "]" & WsName & _
                 "'!" & Range(Target).Address(True, True, xlR1C1)
        ReadFromClosedWorkbook = ExecuteExcel4Macro(Target)
    End If
End Function

I got a answer ,i have added GetObject function to read the cell value without open the workbook eg : Set ObjWB = GetObject("c:\Test.xlsx") — srivignesh, Oct 04 '17 at 11:47

Read the cell value without opening the workbook

1 Answers1