Excel VBA - Value from closed workbook - Error 2015

[TheFitz]

Hi all,

I'm trying to get a value from a closed workbook which is in merged cell B30:G32 and I get error 2015.

If I change the reference to "E10" (a non merged cell) it works.

Private Function GetValue(path, file, sheet, ref)
'   Retrieves a value from a closed workbook
    Dim arg As String
    path = "d:\"
    file = "zzz.xls"
    ref = "B30:G32"
'   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(1, 1, xlA1)
      
      '   Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
End Function

Any ideas would be apprecaited.

Thanks

Fitz

Fitz
Did you know, there are 10 types of people in this world:
* Those who understand binary
and
* Those who Don't!!

 

[zelgar]

Tyr only referring to the first cell (i.e., B30) instead of the merged range.

 

[TheFitz]

I tried that first of all. It's definately due to the merged cell. I don't have the ability to change it from a merged cell but that's what's causing the issuue.

Thanks for your thoughts tho.

F.

Fitz
Did you know, there are 10 types of people in this world:
* Those who understand binary
and
* Those who Don't!!

 

[SkipVought]

Check this out...

http://msdn.microsoft.com/en-us/library/office/aa175731(v=office.11).aspx

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]