CODEWITHSUNDEEP
excelvba
Ivan
Ivan

Reputation: 1304

Excel VBA function to get value using variables from closed workbook

I have the following function which I use to fetch data from a closed workbook:

Public Function GetValue(path, file, sheet, ref)
'   Retrieves a value from a closed workbook
    Dim arg As String
'   Make sure the file exists
    If Right(path, 1) <> "\" Then path = path & "\"
    If Dir(path & file) = "" Then
        GetValue = "File Not Found"
        Exit Function
    End If
'   Create the argument
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
      Range(ref).Range("A1").Address(, , xlR1C1)
'   Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
End Function

Then I have the following test routine which works:

Sub TestGetValue()
    p = Range("B2").Value
    f = Range("B3").Value
    s = "TOTAL"
    a = "D" & ActiveCell.Row + 3
    MsgBox GetValue(p, f, s, a)
End Sub

However, if I use the GetValue function in an Excel cell providing all 4 parameters exactly like in the routine, it always throws a #VALUE! error.

Why does it work in a routine and not while being called as a function?

Upvotes: 1

Views: 2052

Answers (1)

Ivan
Ivan

Reputation: 1304

After several attempts I did find 3 solutions:

  1. Using a function with "ExecuteExcel4Macro"
  2. Opening the other workbook in the background by vba procedure, then copy/pasting data
  3. Creating external references by vba procedure, then leaving only values

First one while being able to use it as an excel custom formula, it is the slowest, especially when you need data from a specific range. So I don't recommend this option as it would slow your workbook down a lot.

Second option while being much faster, still takes around 15 seconds to get data from several ranges across 2 workbooks.

Third option was the fastest. Like an instant really less than 1 second execution time during which data was pulled from 2 closed workbooks and for different ranges.

Here's the 3rd option's procedure:

Sub getDS()
asname = "data"
bsname = "Anual"
csname = "Total"
filename = Sheets("data").Range("B64").Value
path = Sheets("data").Range("B63").Value
Dim ws As Worksheet
Set ws = Sheets("data")

    With ws.Range("D4:D34")
        .ClearContents
        .Formula = "='" & path & "[" & filename & "]" & bsname & "'!R11"
        .Value = .Value
    End With
    With ws.Range("J4:J34")
        .ClearContents
        .Formula = "='" & path & "[" & filename & "]" & bsname & "'!U11"
        .Value = .Value
    End With
    With ws.Range("J37:J37")
        .ClearContents
        .Formula = "='" & path & "[" & filename & "]" & csname & "'!DW96"
        .Value = .Value
    End With
End Sub

I found this by far to be the BEST and FASTEST VBA solution to fetch data in an INSTANT from closed workbooks (with variables) without opening them.

Upvotes: 1

Related Questions