How do I get the Cell value in Excel using an envi

Question

I have 2 spreadsheets, EG. test1.xlxs and test2.xlsm

test1 has the following data on sheet 1 cell B6: testdata

test2 has some vba code I want to disable if test1 is not present or has the wrong information in it, as such, I need to use an environmental variable in the VBA IF statement that that I don't have to edit the code or re link the sheets every time I move them to a new pc

The problem I have is, when I use the environmental variable The If statement tests against the string and not the cell value EG "=C:\users\username\documents[test.xlxs]Sheet1'!$B$6" instead of testdata

This is the code I currently have in test2:

Sub Check_Key()
Dim Key As String
Key = "='" & Environ("USERPROFILE") & "\Documents\[test.xlxs]Sheet1'!$B$6"
If Key = Sheet1.Range("D8") = True Then
        Sheet1.Range("D9") = "Valid"
    Else
        Sheet1.Range("D9") = "Invalid"
    End If
End Sub

is there any way to make it work? I would prefer to have the VBA script do the verification rather than an if statement in a cell on the workbook

Requirements: User should not be able to see data in test1 (spreadsheet should stay closed) Data from test1 needs to be verified via VBA IF statement test2 should be able to be anywhere on pc while test1 should be in my documents

Here is a link to the Spreadsheets, it includes the Licence file the test sheet and a key generator Documents

Answer 1

If the test1 is already open in the same excel instance :

key = workbooks("test1.xlsm").worksheets("sheet1").range("B6")

If he is not

set wbk = Workbooks.open (Environ("USERPROFILE") & "\Documents\test.xlsx")
key = wbk.worksheets("sheet1").range("B6")
' other code
wbk.close false

Answer 2

The following code copies the value in cell B6 from the closed workbook.

Sub test()

'variables
Dim key As Variant, FolderName As String, wbName As String

FolderName = Environ("USERPROFILE") & "\Documents"

wbName = Dir(FolderName & "\" & "test.xlsx") 'Workbook name

key = GetInfoFromClosedFile(FolderName, wbName, "Sheet1", "B6")

End Sub

'Returns value in cell CREDIT: http://erlandsendata.no/?p=2106
Private Function GetInfoFromClosedFile(ByVal wbPath As String, wbName As String, wsName As String, cellRef As String) As Variant
Dim arg As String
    GetInfoFromClosedFile = vbNullString
    If Right(wbPath, 1) <> "\" Then wbPath = wbPath & "\"
    If Dir(wbPath & wbName) = vbNullString Then Exit Function
    arg = "'" & wbPath & "[" & wbName & "]" & wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
    'On Error Resume Next
    GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function