INDIRECT and closed workbooks

As Stephen I also like to thank Dick for opening his blog to others. So I’ll take the chance to cover some medium to advanced formula issues in my postings.
Excel provides a very powerful function – INDIRECT. It just lacks the functionality to access closed workbooks. The following is a compilation of common alternative solutions presented in the Excel newsgroups:

  1. Laurent Longre has developed the free add-in MOREFUNC.XLL which includes the function INDIRECT.EXT
    • You can use it in the same way as INDIRECT. e.g.:
      =INDIRECT.EXT("'C: emp[book1.xls]sheet!'A1")
    • Note that in this formula you also specify the path/directory information
    • If you use INDIRECT.EXT with an open workbook it behaves the same way as INDIRECT does

    There’re some limitations to this function:

    • It does not work reliable on all computers. So you have to try it in your individual environment.
    • INDIRECT.EXT can only return a single cell reference from a closed workbook. So you can’t use it for example as second parameter in a VLOOKUP function:
      =VLOOKUP("search_text",INDIRECT.EXT("'C: emp[book1.xls]sheet!'A1:B20"),2,0)
      won’t work.
    • INDIRECT.EXT does not work with defined names within closed workbooks.
    • If you have to access several closed workbooks your spreadsheet can become slow while re-calculating.
  2. Use SQL.REQUEST:
    • The usage is described here.
    • This function is relatively slow and the data has to be organized in a database like structure (that is a single area with field names as top row).
    • Note: Microsoft does not support this addin anymore.
  3. Use Harlan Grove’s PULL function:
    • Code can be found here.
    • The function creates separate Excel application instances to “pull” data from closed workbooks.
    • This function is more robust, can deal with non-database like layouts and can also deal with cell ranges.
    • Example usage:
      =VLOOKUP("search_text",PULL("'C: emp[book1.xls]sheet!'A1:B20"),2,0)

All these solutions have one common drawback: They’re quite slow. So use them carefully and don’t use them with large cell ranges.

Posted in Uncategorized

38 thoughts on “INDIRECT and closed workbooks

  1. Frank –

    Nice article. Dick’s going to be able to sit back and enjoy the stream of content from his MVP colleagues.

    You left out another option, which is to open the source file, maybe keeping its window hidden. If it’s a central file that others need to use, open it read only. (just my 2¢, without really testing it first)

    – Jon

  2. Hi Jon
    thanks for the comment and I agree with you that this was a brilliant idea from Dick :-)
    Re: open the other file. If this is possible this is always the better (faster) alternative. This probably won’t work if you have many files to which you want to refer to (based on a cell value). e.g. the respective monthly file.

  3. I’ve been working on an addin, that needs to get data from closed books, and used a method using ADO method which i found in the newgroups. In the end it didn’t do what i wanted, but it might fit some situations, i think it was returning the names of sheets from a closed book, but that it also inclued named ranges, nice code though.
    I recoken if you knew what you where doinging you might becable to get named rages back, if not cells/ cell ranges?… I don’t know!

  4. Hi ross
    sounds interesting though AFAIK you can get names from a closed workbook. So maybe you are willing to share that bit of code :-)

  5. “AFAIK you can get names from a closed workbook.”

    You can use ADO’s OpenSchema method to return the names of worksheets and workbook- and worksheet-level ‘named ranges’. By ‘named range’ I mean a Name objected defined using a simple formula that returns a range without the need to calculate anything e.g. the classic ‘dynamic range’ would not be included. Only workbook-level ‘named ranges’ are flagged as TABLE; worksheets and worksheet-level ‘named ranges’ are flagged SYSTEM_TABLE. Due to a bug in the OLE DB providers for Jet, they fail to distinguish between TABLE and SYSTEM_TABLE. To work around this, you can use the OLE DB provider for ODBC with the ODBC driver for Excel to return e.g. only workbook-level ‘named ranges’. Otherwise, with knowledge of the naming rules for Excel worksheets, Excel defined Name objects and Jet tables, the names returned by the OLE DB providers for Jet may be parsed to differentiate between worksheets, workbook- and worksheet-level ‘named ranges’. I’ve previously posted some code for this: do a google search for the exact phrase “drink soup audibly”. Note there is a forth type of Excel ‘table’ being a cell address (e.g. SELECT F1, F2 FROM [Sheet1$A1:B9];) but these are too numerous for the providers/drivers to identify.

    Jamie.

    –

  6. this is the code, I am actually useing it. I havn’t done any work on this addin for ages, might be useful though so i’ll have to pick it up agian. I’v got some bigg problems today, so i cant look through my code, but i thing, picking up on Jamie’s point, that i did managed to tell sheets from ranges:

    ps. i think it was posted by Jake Marx(?) 1st ;)

    Private Sub bFindWorksheetorRange_Click()
    On Error Resume Next
    Dim sFileName As String
    sFileName = Application.GetOpenFilename(FileFilter:=”Excel Workbooks,*.xls”, Title:=”Excel e-mail Manager – Add a workbook”, MultiSelect:=False)

    If sFileName <> “flase” Then
    Me.tAddWorkbooks.Text = Me.tAddWorkbooks.Text & sFileName & “; “
    End If

    Dim cnn As ADODB.Connection
        Dim cat As ADOX.Catalog
        Dim tbl As ADOX.Table

        Set cnn = New ADODB.Connection
        Set cat = New ADOX.Catalog

        cnn.Open “Provider=MSDASQL.1;Data Source=” _
        & “Excel Files;Initial Catalog=” & sFileName
        cat.ActiveConnection = cnn
       
       
        For Each tbl In cat.Tables
        X = tbl.Name
           
    Me.lbNamedRanges.AddItem X

        Next tbl

        Set cat = Nothing
        cnn.Close
        Set cnn = Nothing

    End Sub

    </>

  7. What about getting an entire range from the closed workbook, in a single operation? Looping through a 10×10 range in multiple reports, while doable, is much too slow, especially when computing year to date results and comparing to the previous year

    I have been playing with Access / Excel combined applications. If you have several years of monthly reports, presumably all of them organized the same way (I wish!), You can copy the pertinent contents into separate tables in the Access database. Then, excel only needs to interact with a single access database. With the right queries, access does all of the work of selecting the desired information and presenting it as a recordset. You can transfer the entire recordset to excel in a single operation, for maximum speed and efficiency. With access doing the work – file size is not a concern, and neither is the number of files that otherwise would need to be opened.

    While I find the topic of obtaining data from closed fascinating & useful (I use such techniques often enough), I have to ask if excel is the best application for extracting data from multiple files. My experience to date indicates that databases are more efficient, especially when you need to work with more than 5 files at a time..

    Now, if someone has reliable Excel VBA code to populate the database I would be very interested!
    Currently, I import the data into the database manually, although it should be possible to link each table to an excel list, adding new reports as needed.

  8. Michael,
    I might copy each sheet in to an excel work book, and use MS query on the new book to import the data i wanted: this is essentially the same a doing it with access, but it should not be to hard to set up some automation to created a access DB and import excel file as new tables… and there must be some code around on the next that you can use, I’m quite sure Jamie Collies has posted on this n the news groups, in fact I remember him telling me off Onedaywhen I gave some duff info on a not to distant topic.

  9. HELLO, I’m traying to get the format, and links from a closed workbook.
    the indirect function only allow me to get the data from a opened file, but I want from a closed file, and the format o link froma a cell.

    could it be possible?
    how?

    thanks.

  10. Using INDIRECT.EXT allows you to get the data from a closed function (see the original post for that). And as far as I know, there’s no way to get the format of a cell from a closed book.

  11. Excel4macro GET.CELL provides 66 atributes of a closed workbook’s cell
    should work for many parts of the format

  12. Hi all

    I am using a vlookup to retrieve info from other files. I am using INDIRECT to specify the RANGE for the VLOOKUP as I want to access many files (student years), but these files should be open. I’ve searched around and found that Harlan Grove wrote a pull() function which does what I’m trying to do. I’ve pasted it into a new module in my VBA, but when I write
    Code:
    ————–
    =VLOOKUP$C$4,PULL(“‘C:temp[1styear.xls]sheet!’A1:ev200?),2,0)

    All I get are #VALUE in all the vlooked-up cells. I will be very very appreciated if you resolve this problem

    best regards

    Farag

  13. Hi all

    I am using a vlookup to retrieve info from other files. I am using INDIRECT to specify the RANGE for the VLOOKUP as I want to access many files (student years), but these files should be open. I’ve searched around and found that Harlan Grove wrote a pull() function which does what I’m trying to do. I’ve pasted it into a new module in my VBA, but when I write
    Code:
    ————–
    =VLOOKUP$C$4,PULL(“‘C:temp[1styear.xls]sheet!’A1:ev200?),2,0)

    All I get are #VALUE in all the vlooked-up cells. I will be very very appreciated if you resolve this problem

    best regards

    Farag

  14. Hello,

    I’m too scared of VBA, so the two workarounds I use are:

    (1) =sum(if([whatever=whatever],if([whatever2=whatever2],[rangeofcells],0)) entered as an array formula

    (2) alternatively, there’s a workaround at http://nwexcelsolutions.com/advanced_function_page.htm

    Hope these help you as they helped me.

    With best regards,

    Hoolio.

    PS: Bring back CA’s MS-DOS “Supercalc” spreadsheet!!!

  15. If a workbook that the INDIRECT function is indirectly referencing is closed, the function returns a #REF! error.

    To avoid this error you can imbed the indirect function inside the hyper link function. If you need to perform a sum you can insert it into the value function. The cell can be reformatted to eliminate the underline and font color.

  16. Greg wrote:

    “If a workbook that the INDIRECT function is indirectly referencing is closed, the function returns a #REF! error.

    To avoid this error you can imbed the indirect function inside the hyper link function. If you need to perform a sum you can insert it into the value function. The cell can be reformatted to eliminate the underline and font color.”

    I don’t understand this quite well, could you please give an example?

  17. I have tried the pull function (i get the value error, and I used the most recent fix-I have excel 2000), and the indirect.ext add-in (I get kicked out of excel when I use)

    any suggestions?
    I am just trying to link to a workbook without calling it up and am using this formula:

    =INDIRECT(“‘”&’assm”s’!$M$18&$A$182&”‘!”&ADDRESS(ROW()-32,COLUMN()))

    where ‘assm”s’!$M$18&$A$182=
    P:SharedFinance20062007 Premium ProposalBudget 2007[Census-by-LOS as of 09-01-06.xls]

    the indirect works when the workbook is up, but I do not want other users to have to pull the referenced link everytime.

  18. Hi I have a question i’ve been searching the net for this but I haven’t found it yet.. Can we actually have a single name for a similar cell ranges in different worksheets? Second, if it’s possible, can i use that single name range for my vlookup? Thanks

  19. =INDIRECT.EXT(A34&B34&”Sheet!”&A1)
    =INDIRECT.EXT(“[“A34&B34&”]”&”Sheet!”&A1)

    nothing freaking works all i ever get is #value with indirect EXT

    i have a cell (A34) that contains D:/Dominos/August/
    i have a cell (B34) that contains AugustWe120807.xlsx
    i have 52 rows, weekly sales reports from dominos…

    snippet:
    D:/Dominos/August/August050807.xlsx
    D:/Dominos/August/Augustwe120807.xlsx
    D:/Dominos/August/August190807.xlsx
    D:/Dominos/August/August260807.xlsx

    I need a formula on the far right that simply fetches a single data cell from the appropriate file!
    I do not wish to hard code 52 of them every year, so i need the file name and path as usable variables.
    How do i do it! o_O

  20. The file name is cell B34. The path is A34. The extra tabs got replaced with spaces when i posted.

  21. =INDIRECT.EXT(“[“&A34&B34&”]”&”Sheet!”&A1)

    It looks like you’re missing a ampersand between the opening bracket and the path. Go into the formula bar, highlight everything between the parens and hit F9. See if you get a syntactically correct path.

  22. I have been discussing a simlar problemon another forum and have produced a VBA solution using John Walkenbach’s getvalue function. For some reason that I don’t understand this function will not work as a UDF, you have to call it from a sub. I have just written a routine that will read the path and cell reference data from a named worksheet range and paste the referenced cell values back into the adjacent columns:

    JW’s function (from http://j-walk.com/ss/excel/tips/tip82.htm):

    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 &amp; “”
        If Dir(path &amp; file) = “” Then
            GetValue = “File Not Found”
            Exit Function
        End If

    ‘   Create the argument
       arg = “‘” &amp; path &amp; “[“ &amp; file &amp; “]” &amp; sheet &amp; “‘!” &amp; _
          Range(ref).Range(“A1”).Address(, , xlR1C1)

    ‘   Execute an XLM macro
       GetValue = ExecuteExcel4Macro(arg)
    End Function

    And my subroutine:

    Sub TestGetValue()

    Dim GotVal As Variant, RefList As Variant, NumRefs As Long, NumCols As Long, i As Long, j As Long
    Dim NumRefCols As Long, p As String, f As String, s As String, a As String

    RefList = Range(“refrange”).Value

    NumRefs = UBound(RefList, 1) – LBound(RefList, 1) + 1
    NumCols = UBound(RefList, 2) – LBound(RefList, 2) + 1
    NumRefCols = (NumCols – 3) / 2 + 3

    For i = 1 To NumRefs
    p = RefList(i, 1)
    f = RefList(i, 2)
    s = RefList(i, 3)

    For j = 4 To NumRefCols
    a = RefList(i, j)
    On Error Resume Next
    GotVal = GetValue(p, f, s, a)
    RefList(i, NumRefCols + j – 3) = GotVal
    Next j
    Next i
    Range(“refrange”) = RefList
    End Sub

    Refrange is a named range with the following data:

    Column 1: Path
    Column 2: Filename
    Column 3: Sheet name
    Columns 4 to x: One or more cell references
    Columns x + 1 to (x-3)*2 +4: Blank cells, i.e. the range name must be wide enough to accommodate the output values.

  23. I tried above example, but i keep getting #REF! with error 2023..
    Would you know what I might be doing wrong?
    Thanks

  24. Insoo,
    Be sure you create a named range called “refrange”.

    “Refrange is a named range with the following data:
    Column 1: Path
    Column 2: Filename
    Column 3: Sheet name
    Columns 4 to x: One or more cell references
    Columns x + 1 to (x-3)*2 +4: Blank cells, i.e. the range name must be wide enough to accommodate the output values.”

  25. Mike – I’m not sure if the range name is the problem since the macro shouldn’t run at all if the range name doesn’t exist. Maybe the code got garbled in the copying and pasting.

    Anyway, I’ve pasted my file here: http://www.interactiveds.com.au/software.html (GetValue.zip)
    which should work.

    It also includes a Pull() function from HArlan Grove which works, albeit slowly (at least in Excel 2007).

  26. Here’s to hoping that the PULL function works. This is a feature that really should be supported by Microsoft, if users are going to be able to take advantage of the “big grid” within the still too small 2GB memory limitations Excel 2007 has.

  27. […] code or to install an add-in which has the required functionality. Some choices are discussed on this thread at Daily Dose Of Excel including:MOREFUNC.XLL which includes the INDIRECT.EXT […]

  28. To follow on from Greg’s post above and to clarify what he means for those interested;-
    =======================================
    Greg says:
    August 29, 2006 at 9:53 pm

    If a workbook that the INDIRECT function is indirectly referencing is closed, the function returns a #REF! error.

    To avoid this error you can imbed the indirect function inside the hyper link function. If you need to perform a sum you can insert it into the value function. The cell can be reformatted to eliminate the underline and font color.
    ========================================

    When an Excel workbook is closed, it cannot be referenced by the INDIRECT function, however as Greg states this can be achieved via an acrimonious HYPERLINK function without having to resort to VBA/coding of any kind.

    To give an example;-

    If the data you wish to reference/lookup is in cell A2 & the INDIRECT range/table reference is in cell B2, then the following formula will correctly return the data in the 2nd column of the lookup table.

    =HYPERLINK(VLOOKUP($A2,INDIRECT($J$2),2,0))

    The formatting of the cells containing this function can then be changed to get rid of the HYPERLINK formatting (blue font colour/underlining etc.).

    Hopefully this makes sense, let me know if you have any issues

    Best regards

    Alex

  29. Sorry guys, I made a mistake in the formula above – instead of INDIRECT($J$2) the correct formula should read INDIRECT($B$2)

    =HYPERLINK(VLOOKUP($A2,INDIRECT($B$2),2,0))

    Sorry for any confusion!

  30. Alex, I really don’t understand what you mean! Are you referencing to external workbook or not? Where is your reference? I only see references to the current sheet. This function must be must more specific.

    e.g. Here’s two workbooks and the idea. Book1: A1:NameOfBook2, B1:references name from cell A1 to get a value from the other book (NameOfBook2) cell C3. So how to get them there? Must have somewhere text like “‘[NameOfBook2.xls]Sheet’!$C$3…..

  31. I’m trying to use the INDIRECT.EXT function to return the contents of a cell within a number of external closed worksheets and I’m only currently to return the correct cell contents when the external worksheets are open.

    Currently:

    Cell A1 holds a static path that will be constant
    Cell B1 holds the year half (e.g. 2011-1) which needs to be changed every six months
    Cell C1 holds a salesperson name which relates to a spreadsheet file and workbook name
    Cell D1 holds the appropriate cell to reference

    So my current test cell contents are:

    A1: ‘D:Commissions
    B1: 2011-1
    C1: [Fredd Bloggs.xls]Salesperson’!
    D1: $B$5

    F1: =INDIRECT.EXT(A1&B2&C1&D1)

    When the file D:Commissions2011-1Fredd Bloggs.xls is open I get a figure returned in cell F1.

    When the file is closed cell F1 returns #REF!

    Can anyone offer suggestions?

  32. Hey,

    I know this post is coming four years after the most recent, but I’m still looking through the board, so I figured others might be too. I used the pull function with my index (to set the range) and it worked perfectly – i just copied and pasted the code right into my excel sheet. Just make sure you follow the exact syntax (including quotation marks, etc.) as they showed in the example.

  33. I have been unable to use closed workbook ranges in formula; however, a simple cell reference i.e., =path!$cell Reference does work on closed workbooks. I usually set up a separate sheet and reference the cells I need for my formula creating a dynamic range from it and use that for my formula’s.

    It works very well. Typically I don’t need to reference more that a years worth of info and I only reference the specific fields that I need.

    It updates when you open the workbook which slows it a little, but once it’s open there is no reduction in speed.

  34. @Alex Sheenan,

    Hi,

    I am aware that Alex’ post is from 2010 but has anyone managed to find an example of that specific workaround? (e.i. HYPERLINK(VLOOKUP(INDIRECT…)) or anything similar that will pull the data from one specific cell in a closed workbook?

    Thanks in advance,

  35. HYPERLINK also fails if the referenced external workbook is NOT open. Obviously, not a valid solution.

    I am NOT allowed to use externally provided software – so no 3rd party function libraries.

    I have had to hard-code pathnames on every single cell with an external reference, and manually change these each time. Very, very UGLY.

    Any other ideas?

  36. What about using VBA for changing the reference systematically/upon request? It goes through all of them, tests whether (compared to header row and/or column entries in our destination file) it is still valid, and, if not, it changes it. Or it simply adds new references according to new header entries.

    The problem of referred-to cells in the closed source files might not be on their correct place has to be handled in advance anyway.

    Collecting data from many files is antique. A database for just all data and a frontend like Excel accessing it is state of the art.

  37. Pingback: Indirect File Link


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.