I made some researches about your request and it's seem that there is no way to do this.
Depending of your Excel version (year,x32 x64), I found those following solutions (I have Excel 2013 x64 and none works for me) :
- MoreFunc : MOREFUNC is an add-in for Excel offering 67 new worksheet functions for Excel 95-2007 and probably 2010 & 2013 for 32 bits edition.
It contain this function :
INDIRECT.EXT: returns the contents of a cell or a range contained in a closed workbook
MoreFunc download
VBA function : I found 2 vba functions. As the first solution, it's work depending Excel version.
- IndirectEx() : Extended INDIRECT Function v1.0 by Wilson So.
'------------------------------------ 'Extended INDIRECT Function v1.0
'------------------------------------ 'Copyright (c) 2009 Wilson So.
'E-mail: @ '------------------------------------ 'Credits: '-
Designed and written by Wilson So. '- The
'CreateObject("Excel.Application")' trick was inspired by Harlan
Grove's PULL function source code.
'------------------------------------ 'This is an open source. You can
freely redistribute and modify it, but please kindly give credit to
the contributers. 'Please also kindly report any bugs/suggestions
through e-mail or in the forums where I posted it.
'------------------------------------ 'How to use: '- Basically same
as INDIRECT() in Excel - the same concept for the ref_text parameter.
'- To update the static memory for a particular reference, ' type TRUE
in the second parameter (just one of the IndirectEx() containing that
reference) ' and calculate it once.
'------------------------------------ 'Features: '- You can refer to
the closed workbook data. '- The retrieved closed workbook data will
be stored in the static memory, ' so in the next time, the closed
workbook will not be opened again for fast retrieve. '- A range
instead of an array will be returned if the path is omitted in the
ref_text, ' so it still works fine if the user refers to an enormous
array, e.g. "Sheet1!1:65536". '- You can use it inside INDEX(),
VLOOKUP(), MATCH() etc. '- You can use it with OFFSET(), but only for
opened workbook data. '- The procedure will not blindly retrieve all
the data as requested; ' it will not retrieve data beyond the "Ctrl +
End" cell, in order to keep the memory as small as possible. '- #NUM!
will be returned in case of lack of memory. '- #REF! will be returned
in case of a wrong path. '- #VALUE! will be returned in case of other
errors. '------------------------------------ 'Known issues: '- Due to
the use of SpecialCells(), #VALUE! will be returned if the worksheet
for a closed workbook is protected.
'-----------------------------------
-
Function IndirectEx(ref_text As String, Optional refresh_memory As Boolean = False) As Variant
On Error GoTo ClearObject
Dim RefName As String
Dim SheetName As String
Dim WBName As String
Dim FolderName As String
Dim vExcel As Object
Dim vWB As Workbook
Static dbOutput() As Variant
Static dbKey() As String
Static dbTotalOutput As Integer
Dim dbIndex As Integer
Dim UserEndRow As Long, UserEndCol As Integer
Dim RealEndRow As Long, RealEndCol As Integer
Dim EndRow As Long, EndCol As Integer
Dim RangeHeight As Long, RangeWidth As Integer
GetNames ref_text, RefName, SheetName, WBName, FolderName
If dbTotalOutput = 0 Then
ReDim dbOutput(1 To 1) As Variant
ReDim dbKey(1 To 1) As String
End If
For i = 1 To dbTotalOutput
If dbKey(i) = FolderName & WBName & "!" & SheetName & "!" & RefName Then
dbIndex = i
End If
Next
If dbIndex = 0 Or refresh_memory Then
If dbIndex = 0 Then
dbTotalOutput = dbTotalOutput + 1
dbIndex = dbTotalOutput
ReDim Preserve dbOutput(1 To dbTotalOutput) As Variant
ReDim Preserve dbKey(1 To dbTotalOutput) As String
dbKey(dbIndex) = FolderName & WBName & "!" & SheetName & "!" & RefName
End If
If FolderName = "" Then
Set dbOutput(dbIndex) = Workbooks(WBName).Worksheets(SheetName).Range(RefName)
ElseIf Dir(FolderName & WBName) <> "" Then
Set vExcel = CreateObject("Excel.Application")
Set vWB = vExcel.Workbooks.Open(FolderName & WBName)
With vWB.Sheets(SheetName)
On Error GoTo ClearObject
UserEndRow = .Range(RefName).Row + .Range(RefName).Rows.Count - 1
UserEndCol = .Range(RefName).Column + .Range(RefName).Columns.Count - 1
RealEndRow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
RealEndCol = .Range("A1").SpecialCells(xlCellTypeLastCell).Column
EndRow = IIf(UserEndRow < RealEndRow, UserEndRow, RealEndRow)
EndCol = IIf(UserEndCol < RealEndCol, UserEndCol, RealEndCol)
RangeHeight = EndRow - .Range(RefName).Row + 1
RangeWidth = EndCol - .Range(RefName).Column + 1
On Error Resume Next
dbOutput(dbIndex) = .Range(RefName).Resize(RangeHeight, RangeWidth).Value
If Err.Number <> 0 Then
IndirectEx = CVErr(xlErrNum)
GoTo ClearObject
End If
End With
On Error GoTo ClearObject
vWB.Close False
vExcel.Quit
Set vExcel = Nothing
Else
IndirectEx = CVErr(xlErrRef)
Exit Function
End If
End If
If TypeOf dbOutput(dbIndex) Is Range Then
Set IndirectEx = dbOutput(dbIndex)
Else
IndirectEx = dbOutput(dbIndex)
End If
Exit Function
ClearObject:
On Error Resume Next
If Not (vExcel Is Nothing) Then
vWB.Close False
vExcel.Quit
Set vExcel = Nothing
End If
End Function
Private Sub GetNames(ByVal ref_text As String, ByRef RefName As String, ByRef SheetName As String, ByRef WBName As String, ByRef FolderName As String)
Dim P_e As Integer
Dim P_b1 As Integer
Dim P_b2 As Integer
Dim P_s As Integer
P_e = InStr(1, ref_text, "!")
P_b1 = InStr(1, ref_text, "[")
P_b2 = InStr(1, ref_text, "]")
P_s = InStr(1, ref_text, ":\")
If P_e = 0 Then
RefName = ref_text
Else
RefName = Right$(ref_text, Len(ref_text) - P_e)
End If
RefName = Replace$(RefName, "$", "")
If P_e = 0 Then
SheetName = Application.Caller.Parent.Name
ElseIf P_b1 = 0 Then
SheetName = Left$(ref_text, P_e - 1)
Else
SheetName = Mid$(ref_text, P_b2 + 1, P_e - P_b2 - 1)
End If
SheetName = Replace$(SheetName, "'", "")
If P_b1 = 0 Then
WBName = Application.Caller.Parent.Parent.Name
Else
WBName = Mid$(ref_text, P_b1 + 1, P_b2 - P_b1 - 1)
End If
If P_s = 0 Then
FolderName = ""
Else
FolderName = Left$(ref_text, P_b1 - 1)
End If
If Left$(FolderName, 1) = "'" Then FolderName = Right$(FolderName, Len(FolderName) - 1)
End Sub
- PULL() : by Harlan Grove
'inspired by Bob Phillips and Laurent Longre 'but written by Harlan
Grove
'-----------------------------------------------------------------
'Copyright (c) 2003 Harlan Grove. ' 'This code is free software; you
can redistribute it and/or modify 'it under the terms of the GNU
General Public License as published 'by the Free Software Foundation;
either version 2 of the License, 'or (at your option) any later
version.
'-----------------------------------------------------------------
Function pull(xref As String) As Variant
Dim xlapp As Object, xlwb As Workbook
Dim b As String, r As Range, c As Range, n As Long
pull = Evaluate(xref)
If CStr(pull) = CStr(CVErr(xlErrRef)) Then
On Error GoTo CleanUp 'immediate clean-up at this point
Set xlapp = CreateObject("Excel.Application")
Set xlwb = xlapp.Workbooks.Add 'needed by .ExecuteExcel4Macro
On Error Resume Next 'now clean-up can wait
n = InStr(InStr(1, xref, "]") + 1, xref, "!")
b = Mid(xref, 1, n)
Set r = xlwb.Sheets(1).Range(Mid(xref, n + 1))
If r Is Nothing Then
pull = xlapp.ExecuteExcel4Macro(xref)
Else
For Each c In r
c.Value = xlapp.ExecuteExcel4Macro(b & c.Address(1, 1, xlR1C1))
Next c
pull = r.Value
End If
CleanUp:
If Not xlwb Is Nothing Then xlwb.Close 0
If Not xlapp Is Nothing Then xlapp.Quit
Set xlapp = Nothing
End If
End Function
