Check a value in another closed workbook

Question

I have an excel file and name (Test 1) in the same folder path. And it has a value on ("Sheet1") in the range "C3". And I want to check this value without ever opening the workbook.

Sub Check()

Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False

Dim mydata As String
Dim wb As Workbook

Set wb = Workbooks.Open(ThisWorkbook.Path & "\test1.xlsx")
If wb.Worksheets("Sheet1").Range("C3").Value = "a9a" <> Empty Then
        
       MsgBox "The value is correct", 64
       
       Else
       
myError:

       MsgBox "The value is incorrect", 64

     End If

Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True

End Sub

You just close the wb before reactivating the screen. You cannot get a value from a closed wb. — ceci

Алексей Р Алексей Р · Accepted Answer · 2021-05-15T14:45:29

You can quickly read data from another workbook without opening it, using ADO (see https://docs.microsoft.com/en-us/previous-versions/office/troubleshoot/office-developer/transfer-excel-data-from-ado-recordset). Working sample:

Option Explicit

Sub Check()
    Dim con As Object, rst As Object, tFilePath As String
    
    Set rst = CreateObject("ADODB.Recordset")
    Set con = CreateObject("ADODB.Connection")
    
    tFilePath = ThisWorkbook.Path & "\test1.xlsx"
    
    con.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
             "Data Source=" & tFilePath & ";" & _
             "Extended Properties='Excel 12.0;HDR=NO';"
    rst.Open "SELECT * FROM [Sheet1$C3:C3]", con, 3, 1

    If rst.Fields(0).Value = "a9a" Then
           MsgBox "The value is correct", 64
    Else
           MsgBox "The value is incorrect", 64
    End If
         
    rst.Close: Set rst = Nothing
    con.Close: Set con = Nothing
End Sub

Check a value in another closed workbook

1 Answers