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

Question

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.

You can't do that with a UDF - see for example: ablebits.com/office-addins-blog/…. — Tim Williams
– Tim Williams, Commented Jun 16, 2022 at 22:43
Interesting read, thanks. So my function cannot open a workbook. So how do I use a macro to open one file for reading and writing my data (open GetValues.xlsm, read the file to open in A1, get the data and write to B2, then get the next file indicated in A2, open it, get the data and write to B2, etc.? — calmymail
– calmymail, Commented Jun 17, 2022 at 14:07

karma · Accepted Answer · 2022-06-24 16:50:23Z

2

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.

edited Jun 24, 2022 at 16:50

answered Jun 17, 2022 at 17:37

karma

2,0091 gold badge12 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Williams · Accepted Answer · 2022-06-19 17:29:03Z

2

You'd need to call your function from a sub, not as a UDF from a worksheet cell:

Sub ProcessPaths()
    Dim c As Range
    'loop over cells with file paths
    For Each c In ActiveSheet.Range("A1:A10") 'for example
        c.Offset(0, 1).Value = GetValue(c.Value) 'populate ColB
    Next c
End Sub

Function GetValue(myFile)
    If Len(Dir(myFile)) > 0 Then
        With Workbooks.Open(myFile, ReadOnly:=True)
            'GetValue = .Worksheets(1).Range("A1").Value  'read from cell
            GetValue = .Worksheets(1).Evaluate( _
                         "=SUMIF(B1:B50,""Current"",A1:A50)") 'execute a function
            .Close False
        End With
    Else
        GetValue = "File?"
    End If
End Function

edited Jun 19, 2022 at 17:29

answered Jun 17, 2022 at 15:38

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

2 Comments

calmymail Over a year ago

This works as expected. Thanks! Follow-up question. Let's say I now want to modify this code to sum all values in myFile based on a condition. So, if wish to implement the following statement instead of simply getting the value of a cell: sumif(B1:B50,"Current",A1:A50)

Tim Williams Over a year ago

See edit above. The Worksheet.Evaluate() method allows you to evaluate a function in the context of the worksheet. Note there's also an Application.Evaluate() but that uses whichever sheet happens to be active as the context for the evaluation, so it's less reliable.

Ricardo A · Accepted Answer · 2022-06-20 16:51:05Z

0

Do not use ActiveWorkbook, instead assign the opened workbook to a Variable

Function Getvalue(myFile)
    'Dim myPath As String
    'Dim myExtension As String
    Dim valueWorkbook As Workbook
    'The less you use ActiveWorkbook the better
    'use ThisWorkbook to refer to the workbook that contains the Code
    myPath = Application.ThisWorkbook.Path & "\"
    myFile = myPath & myFile
    Set valueWorkbook = Workbooks.Open(myFile)
    Debug.Print "myFile: " & myFile
    Debug.Print "ActiveWorkbook/ValueWorkbook: " & valueWorkbook.Name
    cellValue = valueWorkbook.Worksheets(1).Range("A1").Value
    MsgBox "Value in Range A1 is: " & cellValue & " of file: " & valueWorkbook.Name
    GetValue = 1
End Function

edited Jun 20, 2022 at 16:51

answered Jun 16, 2022 at 23:20

Ricardo A

1,8151 gold badge14 silver badges17 bronze badges

5 Comments

calmymail Over a year ago

I was not aware of that requirement. However, when I implement it the way you state, I get the error "Function call on left-hand side of assignment must return Variant or Object." I get this at the following statement: Val = valueWorkbook.Worksheets(1).Range("A1").Value

Ricardo A Over a year ago

probably because Val is a built in VBA function, use anything else but Val to save the value.

Ricardo A Over a year ago

edited to change Val to something else that works

calmymail Over a year ago

I implemented this exactly as you indicated. However, the second debug.print does not print anything and nothing works after this statement. The value returned by the function is #VALUE!.

Ricardo A Over a year ago

I copied your debug.print code and didnt realize that you are using an invalid format. The semicolon after the text is breaking the code. I just removed them.

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

2 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related