ExecuteExcel4Macro to get value from closed workbook

Question

I found this bit of code and thought it might be good to use if I just need to pull one value from a closed sheet.

strInfoCell = "'" & strPath & "[" & strFile & "]Sheet1'!R3C3"
myvalue = ExecuteExcel4Macro(strInfoCell)

When I run this code I get a value for strinfocell of

'C:\Users\my.name\Desktop[QOS DGL stuff.xlsx]Sheet1'!R3C3

But when I run the code a dialogue pops up, showing desktop files with "QOS DGL suff" showing.

What's causing this, why is it not just pulling back the data as expected?

I know the path and file name are right, because if I copy them from the debug output and paste them in to start>>run then the correct sheet opens.

I know that Sheet1 (named: ACL), does have a value in cells(3,3)

Answer 1

It depends on how you use it. The open file dialog box is being showed to you because the "strPath" doesn't have a "" in the end ;)

Try this code.

Option Explicit

Sub Sample()
    Dim wbPath As String, wbName As String
    Dim wsName As String, cellRef As String
    Dim Ret As String
    
    'wbPath = "C:\Documents and Settings\Siddharth Rout\Desktop\"
    wbPath = "C:\Users\my.name\Desktop\"
    
    wbName = "QOS DGL stuff.xls"
    wsName = "ACL"
    cellRef = "C3"
    
    Ret = "'" & wbPath & "[" & wbName & "]" & _
          wsName & "'!" & Range(cellRef).Address(True, True, -4150)
    
    MsgBox ExecuteExcel4Macro(Ret)
End Sub