2

I am trying to write a VBA function so that I can access the cell values of a closed Excel Workbook. I found online that one can write a macro routine like this:

Public Sub TestGetValue()

    p = "C:\Users\Jake\Desktop"
    f = "TestSample.xlsx"
    s = "Sheet1"
    a = "B10"

    Call GetValue(p, f, s, a)
    x = GetValue(p, f, s, a)
    MsgBox x 
  
End Sub

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

I can run the TestGetValue() as a macro, but when I try to use GetValue() as a custom VBA function directly in Excel cells it doesn't work. It seems that the line GetValue = ExecuteExcel4Macro(arg) cannot be executed.

Does anyone know how to get around that? I'm using Excel 2010.

ZygD
  • 22,092
  • 39
  • 79
  • 102
user780069
  • 431
  • 2
  • 5
  • 6
  • 1
    see [**this**](http://vba4all.wordpress.com/2013/10/11/various-ways-to-pull-data-out-of-a-closed-or-opened-workbook-using-excel-formulas-and-vba/) –  Oct 20 '13 at 20:09
  • also [**this**](http://stackoverflow.com/questions/7401967/copy-data-from-another-workbook-through-vba/19317717#19317717) *SO answer* –  Oct 21 '13 at 07:17
  • Thanks, but still.. I know how to write a macro that can open up workbooks and update data, but I don't know how to write a VBA Function that I can call from Excel cells. For example, if you write a function like this: `code` Function OpenWorkbookToPullData(path, cell) Dim openWb As Workbook Set openWb = Workbooks.Open(path) Dim openWs As Worksheet Set openWs = openWb.Sheets("Sheet1") OpenWorkbookToPullData = openWs.Range(cell) openWb.Close (False) End Function `code` You cannot call this Function from Excel cells, although you can call it from macro. – user780069 Oct 26 '13 at 00:18
  • 1
    This is something that is outside the scope of Functions. Functions can return values, but will be unable to act on the environment as you are hoping for. Even if you could do this, I wouldn't think it would be a good option, as resource heavy calculating to occur. Probably better to write code to dump values into cells as needed. – guitarthrower Oct 30 '13 at 21:51

1 Answers1

0

As far as I see, the function ExecuteExcel4Macro isn't defined. But there might be an even easier way. Try to open the Workbook, which contains the desired values. E.g.

Dim tempWb As Workbook
Dim testValue

Set tempWb = Application.Workbooks.Open(path)
testValue = tempWb.Sheets(1).Cells(1,1)

The path can be passed as in your example.

ZygD
  • 22,092
  • 39
  • 79
  • 102
MoonMoon
  • 23
  • 4