VBA ExecuteExcel4Macro method question

[antaeusguy]

Hi,

I'm using this code from John Walkenbach Power Programming 2007 page 358 for my Excel 2010 to obtain a cell content from a closed workbook. I checked from the help section that Excel 2010 seems to still support ExecuteExcel4Macro.

This is how I used the function:
Cell A1 = C:\Documents and Settings\admin\Desktop\Testing\
Cell A2 = Book1.xlsx
Cell A3 = Sheet1
Cell A4 = A1
Cell A5 = GetValue(A1, A2, A3, A4)

My cell A5 returned a #VALUE!... it should return the cell content (which I put as "Hello")...

Does anyone have any idea how to fix this function... thanks in advance!

Private Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
    Dim arg As String
 
' Make sure the file exist
    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

 

[MickG]

I think you need to do it as below.
Place all the code in a sheet module with Command button as shown.
Result in "A10"

Private [COLOR="Navy"]Sub[/COLOR] CommandButton1_Click()
Range("A10") = GetValue([a1], [a2], [a3], [a4])
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Private Function GetValue(path [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] file [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] sheet [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] ref [COLOR="Navy"]As[/COLOR] String) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
'[COLOR="Green"][B] Retrieves a value from a closed workbook[/B][/COLOR]
    [COLOR="Navy"]Dim[/COLOR] arg [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
'[COLOR="Green"][B] Make sure the file exist[/B][/COLOR]
    [COLOR="Navy"]If[/COLOR] Right(path, 1) <> "\" [COLOR="Navy"]Then[/COLOR] path = path & "\"
    [COLOR="Navy"]If[/COLOR] Dir(path & file) = "" [COLOR="Navy"]Then[/COLOR]
        GetValue = "File Not Found."
        [COLOR="Navy"]Exit[/COLOR] Function
    [COLOR="Navy"]End[/COLOR] If
'[COLOR="Green"][B] Create the argument[/B][/COLOR]
    arg = "'[COLOR="Green"][B]" & path & "[" & file & "]" & sheet & "'!" & _[/B][/COLOR]
        Range(ref).Range("A1").Address(, , xlR1C1)
'[COLOR="Green"][B] Execute an XLM macro[/B][/COLOR]
    GetValue = ExecuteExcel4Macro(arg)
[COLOR="Navy"]End[/COLOR] Function
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[antaeusguy]

Cool Mick... it works now

Thanks a lot!

I just have a thought if it could be used in Sub as well and it also works.

So I guess this code works only in VBA and not to be used directly in worksheets..

Sub PrintValueToImWindow()
[COLOR=green]'Print the value to immediate window[/COLOR]
Dim x As String
    x = GetValue([a1], [a2], [a3], [a4])
 
    Debug.Print x
End Sub