How do I get the Cell value in Excel using an environmental variable for a VBA If statement

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

just put the macro workbook in the same directory as the datasheet and use this `ThisWorkbook.Path` — sourceCode, Aug 24 '17 at 12:09
test2 is stored in a central location, and each user needs to have unique data in test1, if I keep both in the same directory it kame updating and keeping track of everything quite a task — Morkai_bde, Aug 24 '17 at 12:31

sourceCode · Accepted Answer · 2017-08-24T18:05:52.800

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

I've tried the code and function, but it returns blank data, if I add "Msgbox key" to the test sub, it also shows it is blank, also, if I add it to my script to verify, with the data cell to compare it with, it returns valid if the cell is empty — Morkai_bde, Aug 24 '17 at 17:43

score 0 · Answer 2 · answered Aug 24 '17 at 12:15

0

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

answered Aug 24 '17 at 12:15

Bla2

61
1
6

Can his be done without opening test1? or at least opening it in such a way so that the user cant see the information in the workbook? – Morkai_bde Aug 24 '17 at 12:26
you can turn the screen updating off with `Application.ScreenUpdating = False ` – sourceCode Aug 24 '17 at 12:34
As far as I remember you can read on a close workbook but with particular format : Further Information in this Answer : https://stackoverflow.com/questions/29310458/how-to-copy-data-from-closed-workbookskeeping-them-closed-into-master-workbook But it's a litlle harder than this question... But like sourceCode says, you can put on start ScreenUpdating to false and then at least turn it On. – Bla2 Aug 24 '17 at 12:39
I have tried the link, but as far as I can see and understand it, it still only copies the destination data into a cell inside test2, I don't see a way to use it in a VBA if statement – Morkai_bde Aug 24 '17 at 17:42

How do I get the Cell value in Excel using an environmental variable for a VBA If statement

2 Answers2

Linked