Excel “Pull” Function: Creating dynamic links to closed workbooks

I was working on a project this week that required aggregation of data from many, many different Excel workbooks (and various tabs within those workbooks) into one unified dashboard. Now, this can be accomplished with traditional linking, but anyone who has tried to link more than a few workbooks together will tell you this is a pain to maintain and often not very reliable.

One useful function for creating dynamic links within the same workbook is INDIRECT. Instead of hardcoding cell and worksheet references into a formula, INDIRECT allows pieces of a formula to be dynamically updated using variables. Sounds pretty good right? Well, INDIRECT has an Achilles’ heel. It only works if you are linking within the same workbook.

Need dynamic links to a closed workbook? Bill Gates says, “You’re out of luck.”

Fortunately, after searching around, I found an old Usenet post from 2004 by a fellow named Harlan Grove. He wrote an Excel function in VB called PULL which essentially fixes INDIRECT’s fatal flaw and allows the user to create links to closed workbooks. Just copy the code below into a Module within the workbook of your choice. Then you can use the PULL function just like any other.

Example:
A1 =C:\work\
B1 = data\
C1 = [ClosedBook.xls]Sheet1’!$A1
E1 = PULL(“‘”&A1&B1&C1)

'----- begin VBA -----
Function pull(xref As String) As Variant
'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.
'-----------------------------------------------------------------
'2004-05-30
'still more fixes, this time to address apparent differences between
'XL8/97 and later versions. Specifically, fixed the InStrRev call,
'which is fubar in later versions and was using my own hacked version
'under XL8/97 which was using the wrong argument syntax. Also either
'XL8/97 didn't choke on CStr(pull) called when pull referred to an
'array while later versions do, or I never tested the 2004-03-25 fix
'against multiple cell references.
'-----------------------------------------------------------------

'2004-05-28
'fixed the previous fix - replaced all instances of 'expr' with 'xref'
'also now checking for initial single quote in xref, and if found
'advancing past it to get the full pathname [dumb, really dumb!]
'-----------------------------------------------------------------
'2004-03-25
'revised to check if filename in xref exists - if it does, proceed;
'otherwise, return a #REF! error immediately - this avoids Excel
'displaying dialogs when the referenced file doesn't exist
'-----------------------------------------------------------------
Dim xlapp As Object, xlwb As Workbook
Dim b As String, r As Range, C As Range, n As Long
'** begin 2004-05-30 changes **

'** begin 2004-05-28 changes **
'** begin 2004-03-25 changes **
n = InStrRev(xref, "\")
If n > 0 Then
If Mid(xref, n, 2) = "\[" Then
b = Left(xref, n)
n = InStr(n + 2, xref, "]") - n - 2
If n > 0 Then b = b & Mid(xref, Len(b) + 2, n)
Else
n = InStrRev(Len(xref), xref, "!")
If n > 0 Then b = Left(xref, n - 1)
End If

'** key 2004-05-28 addition **
If Left(b, 1) = "'" Then b = Mid(b, 2)
On Error Resume Next
If n > 0 Then If Dir(b) = "" Then n = 0
Err.Clear
On Error GoTo 0
End If

If n <= 0 Then
pull = CVErr(xlErrRef)
Exit Function
End If
'** end 2004-03-25 changes **
'** end 2004-05-28 changes **
pull = Evaluate(xref)

'** key 2004-05-30 addition **
If IsArray(pull) Then Exit Function
'** end 2004-05-30 changes **

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
'----- end VBA -----

About these ads

56 responses to “Excel “Pull” Function: Creating dynamic links to closed workbooks

  1. Jared —
    Thanks for your willingness to help!

    cell A14 = ‘M:\SECURITY\HQ Operations\Security Operation Center Weekly Report 2012\Weekly SOC Report xlsx\[SOC Weekly Report - 29-Jan-2012.xlsx]‘SOC Weekly Report’!C3

    cell F18 = =pull(A14) <== this returns a #REF! error

    Note that all the Excel files are in the same folder.

    Any help would be most appreciated. Thank you!

    — Erik Gaull

    • Eric,
      The issue may lie in Excel not recognizing the leading comma in A14. You also had an additional comma after the filename. Try this and see if it works for you.

      PULL(“‘M:\”&A$14)

      Cell A14 = SECURITY\HQ Operations\Security Operation Center Weekly Report 2012\Weekly SOC Report xlsx\[SOC Weekly Report - 29-Jan-2012.xlsx]SOC Weekly Report’!C3

  2. There’s a newer version at http://www.4shared.com/file/L_eA8s4G/pull.html

    I’d make the same caveats I made in my newsgroup postings: the pull udf is SLOW, so you want to use it sparingly. I intentionally made it nonvolatile. If the file would be closed on your computer but someone else has it open and is saving changes, you may think you want it to be volatile, but Excel is a poor tool to use for this sort of data capture. Multiuser databases are much better.

    If you really, really must make it volatile, use it in formulas like

    =IF(NOW(),pull(…)) most general but limited
    =pull(…)+0*NOW() if you’re certain it’ll return numbers
    =pull(…)&T(NOW()) if you’re certain it’ll return text

    • Can’t get this to work. Does this work with Excel 2007?

    • Hi Harlan
      I develop “one off” tools for a team of analysts to help save them from spreadsheet hell. I have been migrating to excel over the years but I started out in 123. I still have some 123 aps in use and I catch hell from the current MS generation. I have never found an equivalent for what I call “SQL in a cell” (retrieving data from multiple external database files using @dget or @dsum) I also use Lotus Approach RDBM. Do you have any new insights into doing this in Excel without getting into exotic VB programs.
      Example of a typical ap I have
      external data files (in db4) for: backlog of orders, finished goods, intransit products, forecast, etc , all of these keyed by Part number and dates
      User open a 123 spreadsheet template I developed
      123 spreadsheet with columns for all the above variables and cell formulas with the @ functions and col to col foemulas.
      All they have to do:
      Part numbers are pasted in by user in col A
      They click one macro button and in 5-10 minutes all the data is retrieved.
      Another macro copies/paste as values to a 2nd worksheet and they are done.

      I have goggled this on and off and searched several forums to no avail. When I do post I get the usual dsum, sumif suggestions. I have tried using VB to open and walk through the tables row by row but it takes forever.

      Any way. If you’ve spent the time to read all this I really appreciate it. Any help you can give me would be appreciated.
      Jerry Delaney
      Apex, NC
      9/27/12
      jdelayknee@yahoo.com

    • Hi harlan grove,

      This function is slow for only one reason. The Set xlapp = CreateObject(“Excel.Application”) has been defined in the function, which creates a new object every time a cell is changed.

      We need to initialize it only once. So you can just declare – – public xlapp as object – – in the module just above the line – – Function pull(xref As String) As Variant
      and
      PUT THE – – Set xlapp = CreateObject(“Excel.Application”) within workbook_open event under THISWORKBOOK. Believe me it works awesome. I have tried it and pulled 100 rows each from 20 files in 15 seconds.

      I can send you my excel files which are working with awesome speed. Please give me your email id.

      Pappu Pager.
      (From India that is Bharat)

      • Pappu, are there any other details? I can’t seem to get the pull function to work with your proposed changes, and at the moment it is might slow…

        Thanks

      • Pappu – can you post the changes in code please?

        Thanks

      • Hello Guys,

        Sorry for the late reply. Pasted below the code I am using. You will get a fair idea what I want to say. I just initialized the object once at workbook_open event. And for doing so I declared it as global variable.
        ‘Requires filename, sheetname as first argument and cell reference as second argument

        ‘Usage: type in an excel cell -> =getvalue(A1,B1)

        ‘Example of A1 -> C:\TEMP\[FILE1.XLS]SHEET1′
        ‘Example of B1 -> B3
        ‘This will fetch contents of cell (B3) located in (sheet1) of (c:\temp\file1.xls)

        ‘Create a module and paste the code into the module (e.g. Module1, Module2)

        Public xlapp As Object

        Private Function getvalue(ByVal filename As String, ref As String) As Variant

        ‘ Retrieves a value from a closed workbook
        Dim arg As String
        Dim path As String
        Dim file As String
        Dim count As Integer

        count = 0
        While (Left(Right(filename, count), 1) “\”)
        count = count + 1
        Wend

        count = count – 1
        path = Left(filename, Len(filename) – count)
        count = count – 1
        file = Right(filename, count)

        count = 0
        While (Left(Right(file, count), 1) “]”)
        count = count + 1
        Wend

        file = Left(file, Len(file) – count)

        If Dir(path & file) = “” Then
        getvalue = “File Not Found”
        Exit Function
        End If

        ‘Set xlapp = CreateObject(“Excel.application”) – Object must be created only once and not at each function call. Do not enable

        ‘ Create the argument
        arg = “‘” & filename & “!” & Range(ref).Range(“A1″).Address(, , xlR1C1)

        ‘ Execute an XLM macro

        getvalue = xlapp.ExecuteExcel4Macro(arg)

        End Function

        ‘Module code ends here. Following code goes into Workbook_Open event

        ‘This code goes into Workbook open event within THISWORKBOOK

        Private Sub Workbook_Open()
        Set xlapp = CreateObject(“Excel.application”)
        End Sub

      • Ashley McCash

        Pappu – I use this function but due to the data volume and amount of files it takes forever! I would love a copy of your excel file. Tried your code but it didn’t work – probably user error!

      • Pappu, can you send me your files.
        The calculations are taking forever in my file.

        Thanks!

        EF

        djfobster@hotmail.com

  3. This is great, but, in Excel 2010, it has a problem with textual data. All numeric-type data comes over fine, but anything with text, it returns a “#N/A” error. Other than that works beautifully!!!

    • Glad it worked!

      • Hi Graham. I think this is the core issue and need at the same time. You can pull numeric data also with a simple =SUM formula (e.g. =SUM([1_Table_Mapping_Overview.xlsx]Table_Overview!F182))

        Have you managed to find a solution for text data?

  4. Harlan
    Thanks for creating the pull.
    However, i need to tweak it slightly. I am using it on an excel where the pull is happening from another file on my system. Now when I email this excel, the pull results in a REF, as the other person does not have the file to pull the data from.
    Now, vlookup like functions give you an option, to update links, and if you choose not to update links, it stores the values as it had. A solution to my problem can be if pull can have a similar functionality.
    Another solution can be I create a macro to paste special values of all cells using the pull. I will have to either manually trigger it or something.
    Any advice, or solutions you may have?
    Thanks

  5. Pretty portion of content. I simply stumbled upon your weblog and in accession
    capital to claim that I get in fact loved account your blog
    posts. Any way I will be subscribing in your feeds and even I fulfillment you access persistently fast.

  6. Balasubramanian A

    I keep getting a #name error…

  7. Guys – none of you has, obviously, heard of Laurent Longre’s Morefunc add-in, which includes the INDIRECT.EXT function. This modified function enables you to create volatile links to closed workbooks. I’ve used it extensively.

  8. Rock’n’roll! It works perfectly.

    In case somebody is as dumb as I am: it will not work when you copy =PULL(“‘”&A1&B1&C1) from the website right into your spreadsheet. Because Excel does not know what these funny typographic quotation marks are, you would need to use =PULL(“‘”&A1&B1&C1).
    Took me 10 minutes to find out what the propblem was :-(, – now it works perfectly :-) thanks!!

    • Ha! Seems like this website automatically assigns the typographic quotation marks. What I wanted to say: do not copy this part “‘” but rather type it in Excel!

  9. Thanks for the function, saved me some headaches! One question…is there a way to get this to pull data from a .csv as well/instead?

  10. Hi there.
    I’m currently trying to get the pull function to work as well. First thanks for the great work already accomplished!
    I experienced the first chunk of code to work as it is posted on this blog when simply copying it as a new module in the excel – macro enabled- file.
    The second version of it, the .xla file will not work for me though, the “pull” function calls are replaced by #NAME? instead. The macro is enabled though when I look at File -> Options -> Complements, “HG’s own add-in”.
    I have the feeling, Excel does not recognize the function correctly. Autocompletion works though. Any suggestion ?

  11. Hi,
    Could someone please help me figure out what I’m doing wrong? Here’s what I have:

    Cell B1= ‘C:\Users\Cristina\Documents\My Dropbox\Cristina\Evaluacion Automatica de Pruebas\Evaluacion 6 Necesidades\Participantes\[Encuesta Necesidades S1P01.xlsx]Sheet1’!$D9

    =PULL(“‘”&B$1)

    I already tried the suggestion given above about doing the following and it is not working (except I cannot make excel not put a coma at the beginning of Cell B1 – I erase it and it keeps putting it back). So it looks like this:

    Cell B1= ‘Users\Cristina\Documents\My Dropbox\Cristina\Evaluacion Automatica de Pruebas\Evaluacion 6 Necesidades\Participantes\[Encuesta Necesidades S1P01.xlsx]Sheet1’!$D9

    =PULL(“‘C:\”&B$1)

    Still not working.. Please help. Maybe the formula is not working well? how do I make sure it is? Thank you very very much!

    Cristina

    • Try this buddy.

      ‘Requires filename, sheetname as first argument and cell reference as second argument

      ‘Usage: type in an excel cell -> =getvalue(A1,B1)

      ‘Example of A1 -> C:\TEMP\[FILE1.XLS]SHEET1′
      ‘Example of B1 -> B3
      ‘This will fetch contents of cell (B3) located in (sheet1) of (c:\temp\file1.xls)

      ‘Create a module and paste the code into the module (e.g. Module1, Module2)

      Public xlapp As Object

      Private Function getvalue(ByVal filename As String, ref As String) As Variant

      ‘ Retrieves a value from a closed workbook
      Dim arg As String
      Dim path As String
      Dim file As String
      Dim count As Integer

      count = 0
      While (Left(Right(filename, count), 1) “\”)
      count = count + 1
      Wend

      count = count – 1
      path = Left(filename, Len(filename) – count)
      count = count – 1
      file = Right(filename, count)

      count = 0
      While (Left(Right(file, count), 1) “]”)
      count = count + 1
      Wend

      file = Left(file, Len(file) – count)

      If Dir(path & file) = “” Then
      getvalue = “File Not Found”
      Exit Function
      End If

      ‘Set xlapp = CreateObject(“Excel.application”) – Object must be created only once and not at each function call. Do not enable

      ‘ Create the argument
      arg = “‘” & filename & “!” & Range(ref).Range(“A1″).Address(, , xlR1C1)

      ‘ Execute an XLM macro

      getvalue = xlapp.ExecuteExcel4Macro(arg)

      End Function

      ‘Module code ends here. Following code goes into Workbook_Open event

      ‘This code goes into Workbook open event within THISWORKBOOK

      Private Sub Workbook_Open()
      Set xlapp = CreateObject(“Excel.application”)
      End Sub

      • Hi
        I cant get the function to run. There are 2 lines that are giving compile errors.
        While (Left(Right(filename, count), 1) “\”)
        While (Left(Right(file, count), 1) “]”)

        Would someone be able to help?

      • I to am having trouble with this function in the same lines that Phil mentioned:
        While (Left(Right(filename, count), 1) “\”)
        While (Left(Right(file, count), 1) “]”)

        Any help would be appreciated

  12. Pingback: Read data from file without opening them?

  13. DOES IT WORK ON MAC EXCEL 2011?

  14. Pingback: Sum Data from Closed Work Sheets

  15. Thanks for your function, it is really good..

  16. Pingback: Anonymous

  17. Pingback: reference to a particular cell in a closed workbook (indirect)

  18. While (Left(Right(filename, count), 1) “\”)
    While (Left(Right(file, count), 1) “]”)

    There needs to be equal signs here:

    While (Left(Right(filename, count), 1) = “\”)
    While (Left(Right(file, count), 1) = “]”)

    I’m having trouble with the part in ThisWorkBook
    Private Sub Workbook_Open()
    Set xlapp = CreateObject(“Excel.application”)
    End Sub

    I’m getting an error when I run the code. Any help?

  19. specifically, I get an error saying that an object is required each time I open the workbook. I’m referring to pappu’s code

  20. one last comment. the error is a runtime error 424

    • Thanks Viren for pointing out the missing equal signs. Thant has resolved the compile errors I was getting. However, I am running into the same runtime error (424) that you are running into with pappu’s code. Error occurs upon opeing the workbook.

  21. I am trying to use that function to get values from csv, should it work as well? (It doesn’t, but that may be as I made some other mistake)

  22. I was able to make the pull function work, but is there any way to make the cell reference variable? Like if I put it in cell A1, can I drag the formula down to pull B1, C1, etc?

  23. ha, nevermind. I got it.

  24. Pingback: Referencing another workbook, where the name changes...INDIRECT?

  25. Im trying to use the pull function with a range-

    =pull($AM$46&AL13)

    AL13 is B10:Z10
    AM46 is ”P:\Coater\RAS\[B-Shift holiday planner 2013.xls]September’!

    However it does not recognise that AL13 is a range and only returns the first value of this ie. B10. Can anyone help me understand why this is? Is there a way to make it pull the whole range like with indirect?

  26. Pingback: VBA Code to look at a range in a different workbook

  27. The code didn’t work for me, but forced me to look for more options.

    Using the code I found in

    http://stackoverflow.com/questions/9259862/executeexcel4macro-to-get-value-from-closed-workbook

    written by Siddharth Rout

    I created the following version of that function, tested in Excel 2007, removing the MsgBox:

    It is almost identical to the code above, just going to the point: getting the data.

    Function indirext(wbPath As String, wbName As String, wsName As String, cellRef As String)

    Dim Ret As String

    Ret = “‘” & wbPath & “[" & wbName & "]” & _
    wsName & “‘!” & Range(cellRef).Address(True, True, -4150)

    indirext = ThisWorkbook.xlapp.ExecuteExcel4Macro(Ret)

    End Function

    This requires two definitions in ThisWorkBook:

    Public xlapp3 As Object

    Private Sub Workbook_Open()
    Set xlapp = CreateObject(“Excel.application”)
    End Sub

    I did that following the idea indicated by Papu Pagger

    Now you can test the function.

    example:
    indirext(“C:\e\”,”test.xlsx”,”sheet1″,”a1″)

    Make sure all parameters are strings

    My hat off for Harlan Grove. I thought this was not possible.

    Enjoy!

  28. I just noticed a typo.

    Where it says
    Public xlapp3 As Object
    it should say
    Public xlapp As Object

  29. Pingback: Variables within File Name References

  30. It’s great that there is a function like this user defined pull function. Although, while user defined functions are working in my excel 2003 on a winxp machine, I haven’t been able to get the pull function UDF to work. I opened a new excel spreadsheet (a .xls file) and opened up the VBE, chose ‘insert module’, then copied the pull function UDF (from the original post of this thread) to ‘module 1′. Then exited VBE. Then I did a test, by putting the number ‘4’ in cell E1, and the number ‘3’ in cell E4. I then firstly put =INDIRECT(“E”&E1) into cell A1, which resulted in a ‘3’ in cell A1. Then I put =pull(“E”&E1) into cell A2, which resulted in #REF! in cell A2. I actually want to use the pull function for external worksheets, but I first needed to see if the pull function works within its own local spreadsheet to start with. Other user defined functions are working on my system, and I’m trying to get this pull function to work (and it has been correctly copied to ‘module 1′). I would love to get this one to work because this would be a very nice feature to have. Thanks in advance!

  31. I found a much easier way to do this. The goal is to access data from closed workbooks using dynamic links, whereas INDIRECT won’t work on a closed workbook.

    But you can still use dynamic links. You just have to go through the Name Manager and create a universal reference name.

    In the workbook I created, I use the drop down box cell link value to reference an array and piece together the cell reference I want. From there, what people are typically wont to do is use that constructed cell reference with INDIRECT in their formulas (× who knows how many formulas). Instead, I used a vba sub routine to rewrite the value of a name in the Name Manager, and I used the name as a variable in my formulas.

    So I might have in one of my cells the formula:

    =nV(VLOOKUP($E9,SN,3))

    E9 is the date code reference, which I use as a key on my data table, SN is the variable named range, and 3 is the column in that range. (nV is a custom function I built that returns either a blank cell or the value you choose if the criteria equals “”, 0, or an Error).

    The name SN in the Name Manager has the value:

    =’https://d.docs.live.net/ed2bad18b7a35798/Public/[DT6438.xlsm]Data Table’!$A$5:$DH$35

    (I altered some numbers for security).

    When I change the drop down box to another selection, it reconstructs the reference with the correct file name according to the array, and the correct range of cells according to the specified month on the sheet. Using the array, the code looks like this:

    =nV(CONCATENATE(“‘https://d.docs.live.net/ed2bad18b7a35798/Public/[",VLOOKUP(CJ1,CJ2:CL24,3),".xlsm]Data Table’!”,”$A$”,(DATE(VLOOKUP(D38,C1:D37,2),B13,1)-41635),”:$DH$”,EOMONTH(DATE(VLOOKUP(D38,C1:D37,2),B13,1),0)-41635))

    Since the source files are all in the same drive, they all share https://d.docs.live.net/ed2bad18b7a35798/Public/ in common. This could be individually specified if necessary, if the files were in different drives.

    The VLOOKUP is checking the array. CJ1 is the drop down box cell reference. CJ2:CL24 is the array range. 3 is the column in the array where the file name is found.

    The specific cell reference range is built based on the date entered on the main sheet drop down box. It gives a range from the first day of the month to the last day of the month, using date codes minus the correct amount to equal the actual row number for the cell reference. The date code for January 1st, 2014, for example, is 41640. That date is row 5 in the data table. Ergo, DATE(2014,1,1)-41635, or 41640 minus 41635.

    The A and DH column parameters are just the full width of my data range on the data table.

    Once the reference is built, a vba Sub generates a variable out of the constructed reference and overwrites the value of SN in the Name Manager with this code:

    Sub Change_Reference()
    New_Ref = Sheets(“Data Sheet”).Range(“CJ26″)
    ActiveWorkbook.Names(“SN”).RefersTo = “=” & New_Ref
    End Sub

    I have 992 cells using the SN reference, pulling over 100 pieces of data from 20 separate stores on a remote drive. The file size is currently 201 KB. It works. It works beautifully. It works quickly. And it’s memory efficient.

    Relative to the requests for help I’ve seen across the web, not merely on the INDIRECT function, but INDIRECT in this specific application, I hope this is helpful to someone.

    • Thanks for the comment! I’ll have to give that a try.

      • With all the people that have tried to figure this out, it would have been wrong of me not to share such a simple solution now that I’ve figured out how to make it work. Let me know how it works out for you.

  32. Pingback: Calling a cell that contains part of a filename

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s