excelvba

Function to open excel file and get value in specific cell vba

I'm trying to use VBA to read in a filename of an excel file, open it, get a value in it, and write the value into a cell in my main excel file.

Specifically, in one excel sheet, named GetValues.xlsm, I have in cell "A1" the name of a file, "Test1.xlsx". I am trying to open this file, then assign the value in cell "A1" to cell "B1" in GetValues.xlsm. I am trying to use a function, below, to do this;

Function Getvalue(myFile)
'Dim myPath As String
'Dim myExtension As String
myPath = Application.ActiveWorkbook.Path & "\"
myFile = myPath & myFile
Workbooks.Open myFile
Debug.Print "myFile: "; myFile
Debug.Print "ActiveWorkbook: "; ActiveWorkbook.Name
'Val = ActiveWorkbook.Worksheets(1).Range("A1").Value
GetValue = 1
End Function

So that in cell "B1" of GetValues.xlsm I type

=GetValue(A2)

which gives me the result:

myFile: G:\Teaching-CAL\MAE343-CompressibleFlow\04_Codes\LVL\Test1.xlsx
ActiveWorkbook: GetValues.xlsm

My issue is that I'm expecting ActiveWorkbook to be the Test1.xlsx file, but as you can see in the output it is giving me GetValues.xlsm.

I assign the value of 1 to "GetValue" so I can attempt to debug this function.

Thanks in advance for your help.

Solution

  • If it's OK with you to fill cell B1 of "GetValues.xlsm" active sheet with the value of cell A1 in "Test1.xlsx" without UDF and without opening "Test1.xlsx" workbook :

    Sub test()
Dim p As String: Dim f As String
Dim s As String: Dim c As String
    p = ThisWorkbook.Path & "\" 'the path directory
    f = Range("A1").Value 'the name of the file
    s = "Sheet1" 'the name of the sheet
    c = "a1" 'the cell
    Ret = "'" & p & "[" & f & "]" & _
          s & "'!" & Range(c).Address(True, True, -4150)
    Range("B1").Value = ExecuteExcel4Macro(Ret)
End Sub

    The sub above will fill cell B1 of "GetValues.xlsm" active sheet with the value of cell A1 sheet1 of whatever_the_name_of_the_ExcelFile you wrote in cell A1 of "GetValues.xlsm" active sheet without opening that whatever_the_name_of_the_ExcelFile.

    "GetValues.xlsm" must be in the same folder with whatever_the_name_of_the_ExcelFile.