calling ExecuteExcel4Macro from Access 2010 VBA module - can't seem to get reference right

[Rx_]

These are my code fragements after many attempetd failures to call the ExecuteExcelMacro from an Access 2010 VBA module.
Access referencing Excel (in code window, References, Excel checked) is a preferred way to create reports from MS Access 2010.

Just can't seem to get the object reference right for passing the variable from Access back to the Excel object.
Searched the web for an hour, nobody else has figured it out either.
If anyone can help with this, I would be happy to verify it.

It will gain popularity since Access 2013 is rumored not to have Reports. Users are expected to use SQL Server Reporting Services or Excel Automation. Yes, I realize there are other ways besides the ExecuteExcel4Macro to accomplish the same result.

Public Function GetValue(MyPath As String, MyFile As String, MySheet As String, MyCellReference As String) As String
' Retrieves a value from a closed workbook
    Dim arg As String
    Dim myA1 As String
    myA1 = vbQuote & "A1" & vbQuote
    'myrange = vbQuote & "A3" & vbQuote
    Dim objXL As Excel.Application
    Set objXL = New Excel.Application
' Make sure the MyFile exist
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
    If Dir(MyPath & MyFile) = "" Then
        GetValue = "MyFile Not Found."
        Exit Function
    End If
' Create the argument
    arg = "'" & MyPath & "[" & MyFile & "]" & MySheet & "'!" & Range(MyCellReference).Range("A1").Address(, , xlR1C1) & "'"
' Execute an XLM macro
    GetValue = objXL.ExecuteExcel4Macro(arg)
        Set objXL = Nothing
'In order for this function to work properly, a worksheet must be active in Excel.
'This will generate an error if all windows are hidden, or if the active sheet is a Chart sheet or set with XLveryhidden.
End Function

Function GetExcelValue(path, file, sheet, ref)
         path = "C:\"
         file = "Book1.xlsx"
         sheet = "Sheet1"
         ref = "A1:C3"
         Dim objXL As Excel.Application
        Set objXL = New Excel.Application
        Dim objxlRange As Excel.Range
        Set objxlRange = objXL.Range("A3:A5").Range("a1").Address(, , xlR1C1)
    Dim arg As String
   
    If Right(path, 1) <> "\" Then path = path & "\"
     
    If Dir(path & file) = "" Then
        GetExcelValue = "file not found"
        Exit Function
    End If
     
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & objxlRange
    GetExcelValue = objXL.ExecuteExcel4Macro(arg)
End Function

 

[Norie]

What variable are you trying to pass to Excel and why are you using ExecuteExcel4Macro?

 

[Rx_]

What variable are you trying to pass to Excel and why are you using ExecuteExcel4Macro?

Just trying to pass in the drive, path, workbook name, sheet, range
Extract a value from there with out opening the workbook.
Take value back to MS Access.

Was trying to help answer a question on an Access vba site. This one stumpped me.
It is the range object that appears to be stopping up the function.

 

[Kyle123]

Why not just query the Excel workbook with ADO/DAO? Seems much more lightweight than the approach you're taking.

 

[RoryA]

The address you are passing to the executeexcel4macro command has a superfluous apostrophe on the end of it as far as I can see.

 

[Kyle123]

Shouldn't objxlRange be a string?

 

[Rx_]

Shouldn't objxlRange be a string?

Tried that too. Didn't show it here. Just could not get it to make a difference.

Was interested in the comment above about the efficiency of this function.
In Access, I used the more traditional method to search the shared network drive for thousands of templates which I opened each worksheet and pulled data into Access (linked to SQL Server). Given the size of the data harvest, it seemed to be efficient. There was no visible Excel being opened since object code was used.
The person's asking for assistance on the Access site seem to be under the impression that the ExecuteExcel4macro is more efficient than opening the Excel workbook via code.
If that is not the case, it might be better to advise them to drop this method. My guess is that Excel4 stands for some older version of Excel. Older is not to say it is more or less efficient.
Can you share your thoughts on the efficiency of ExecuteExcel4Macro?

 

[Kyle123]

Against what?

Why not run some tests? I'd run it against accessing the workbook via ADO - With ADO you don't need a reference to Excel and you don't need to create an instance

 

[Rx_]

I am such a DAO guy, never thought of the ADO not requiring an instance.
You have sparked my interest. When there is some breathing room in my schedule, will look forward to trying that.
Do you have any sites with code to recommend?