Getting Data From Closed Workbook Using GetValue 1

[mattygriff]

I'm trying to retrieve some data from a closed worksheet and was hoping to use Walkenbach's GetValue function but it keeps returning #REF.

Private 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



Sub PopulatePBR()

    Dim arrPath() As String
    
    txtWhichMER = "Please select the Monthly Engineering Report" & vbCrLf _
    & "to be included in this Project Board Report"
    
    txtWhichPSC = "Please select the PSC Report to be" & vbCrLf _
    & "included in this Project Board Report"
    
    MERFilename = Application.GetOpenFilename _
        ("Excel Files (*.xls),*.xls", , _
        "Please select Monthly Engineering Report...", MultiSelect:=False)
    
    
    arrPath = Split(MERFilename, "\")
    
    numParts = UBound(arrPath)
    
    strFilename = arrPath(numParts)
    
    strPath = ""
    
    For t = 0 To numParts - 1
        strPath = strPath & arrPath(t) & "\"
    Next t
    
    p = strPath
    f = strFilename
    s = "Sheet1"
    a = "B2"
     
    Sheet1.Range("SCHEME_NAME").Value = GetValue(p, f, s, a)
      
    SavePBR
    
End Sub

The data I want is defeinitely in Cell B2 on Sheet 1 and the path and filename are correct. The only thing I can think of is that Cell B2 is a merged cell - would that cause any problem?

I have tried using a named range "SCHEME_TITLE" rather than the direct "B2" reference but that doesn't work either - I guess that's something to do with the "xlR1C1" definition?

Thanks in advance for any assistance!

 

[SkipVought]

Why don't you conduct a test? Then you could Inform all of us.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[mattygriff]

Well, I've had a look around and the general consensus is that, because most of the Excel object model is only available in open workbooks, it's not possible to get data from a closed workbook using named ranges.

Hope this helps.

 

[Gavona]

This is only an issue with VBA. In a worksheet
=SUM('C:\......\Eraseme.xls'!Test2) gives the sum of the named range.
So even if you have to create a (hidden) worksheet to help it must be possible.
Populate cell A1 in the worksheet then read the value of this cell into your VBA.

Gavin