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 -----
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
Thanks, Jared!
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?
Dave,
I’m using it currently with Excel 2007, so it should work for you.
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
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!
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
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.
Thanks?
I keep getting a #name error…
Can you put your formula in the comments? I can take a look at it.
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.
I’ll have to check it out. Thanks!
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).
, – now it works perfectly 
thanks!!
Took me 10 minutes to find out what the propblem was
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!
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?
Thanks! I don’t think so…You may want to ask Harlan. He commented on this post.
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 ?
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
Pingback: Read data from file without opening them?
DOES IT WORK ON MAC EXCEL 2011?
Pingback: Sum Data from Closed Work Sheets
Thanks for your function, it is really good..
Pingback: Anonymous
Pingback: reference to a particular cell in a closed workbook (indirect)