Excel Pull Function
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'!$A
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-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-28 changes **
'** begin 2004-03-25 changes **
n = InStrRev(Len(xref), 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)
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 -----
Update 1/22/20
The original article had a lot of comments - one from the developer. I retained them below. The Wordpress to Hugo conversion isn’t perfect, but I like having them in the post.
Comments:
Harlan Grove - Feb 1, 2012
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
Jared - Feb 2, 2012
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
Erik Gaull - Feb 5, 2012
Thanks, Jared!
Erik Gaull - Feb 1, 2012
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
Dave - Mar 2, 2012
Can’t get this to work. Does this work with Excel 2007?
Jared - Mar 2, 2012
Dave, I’m using it currently with Excel 2007, so it should work for you.
Graham - Jun 5, 2012
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!!!
Jared - Jun 5, 2012
Glad it worked!
Jared - Sep 6, 2012
Thanks?
anshuman - Jul 6, 2012
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
Card Transaction Process - Sep 6, 2012
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.
Jerry Delaney - Sep 4, 2012
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 [email protected]
Balasubramanian A - Oct 0, 2012
I keep getting a #name error…
Jared - Oct 0, 2012
Can you put your formula in the comments? I can take a look at it.
Jared - Nov 5, 2012
I’ll have to check it out. Thanks!
The Horse - Nov 5, 2012
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.
paradroid - Nov 1, 2012
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!!
paradroid - Nov 1, 2012
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!
Jason - Jan 5, 2013
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?
Jared - Jan 5, 2013
Thanks! I don’t think so…You may want to ask Harlan. He commented on this post.
Lars - Feb 1, 2013
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 ?
Cristina - Feb 3, 2013
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
Read data from file without opening them? - Feb 1, 2013
[…] You might want to check out Harlan Grove’s PULL function Excel “Pull” Function: Creating dynamic links to closed workbooks | Numbermonger […]
Chris Burr - Mar 1, 2013
DOES IT WORK ON MAC EXCEL 2011?
Pappu Pager - May 3, 2013
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
Brad - Jun 2, 2013
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
Viren - Jun 2, 2013
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?
Viren - Jun 2, 2013
specifically, I get an error saying that an object is required each time I open the workbook. I’m referring to pappu’s code
Viren - Jun 2, 2013
one last comment. the error is a runtime error 424
VBA Code to look at a range in a different workbook - Sep 0, 2013
[…] I think that you willneed something like Harlan Grove’s Pull function Excel “Pull” Function: Creating dynamic links to closed workbooks | Numbermonger […]
Brad - Jun 3, 2013
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.
[…] take a look at this: Excel “Pull” Function: Creating dynamic links to closed workbooks | Numbermonger […]
Steve - Jul 3, 2013
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?
Steve - Jul 3, 2013
ha, nevermind. I got it.
Alex Frazier - Jan 3, 2014
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.
Calling a cell that contains part of a filename - Jan 5, 2014
[…] the file is closed, use a custom function called PULL Excel “Pull” Function: Creating dynamic links to closed workbooks If you do a web search for something like Excel Pull function, you’ll find several threads on this […]
EF - Aug 4, 2013
Pappu, can you send me your files. The calculations are taking forever in my file. Thanks! EF [email protected]
Roman - Sep 4, 2013
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?
Jared - Jan 3, 2014
Thanks for the comment! I’ll have to give that a try.
Alex Frazier - Jan 3, 2014
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.
hsn - Apr 5, 2013
Thanks for your function, it is really good..
Clayton - Sep 5, 2014
Thanks Alex, with that I was able to figure out some of it. The thing I still can’t figure out is how you handle multiple workbooks. Do you have a different “Name” for each cell*workbook value? For example, if I have 3 workbooks each with 20 values to be imported, do I have to have 60 “Names” (workbook1cell1, workbook2cell1, workbook3cell1, workbook1cell2, etc.?) The thing I am trying to accomplish is essentially pull in 100 cells worth of data each from a series of workbooks that are all set up exactly the same just by changing the cell that contains the reference to the workbook.
Jhon - Oct 5, 2013
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!
Jhon Doe - Oct 5, 2013
I just noticed a typo. Where it says Public xlapp3 As Object it should say Public xlapp As Object
Kenny - Nov 1, 2013
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!
Variables within File Name References - Oct 4, 2013
[…] Maybe Harlan Grove’s PULL function Excel “Pull” Function: Creating dynamic links to closed workbooks | Numbermonger […]
Referencing another workbook, where the name changes…INDIRECT? - Sep 5, 2013
[…] INDIRECT will not work with closed workbooks. You could try Harlan Grove’s PULL function Excel “Pull” Function: Creating dynamic links to closed workbooks | Numbermonger […]
Pappu Pager - May 3, 2013
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)
File name references - Jan 1, 2015
[…] document.write(’’); Excel doesn’t create an external link from a concatenated string. There is a custom User Defined function (UDF) called PULL that will link to a close file from a concatenated string. Excel PULL Function […]
Clayton - Aug 4, 2014
Alex, Would you be able to share a copy of an excel workbook with this functioning in it, it sounds exactly like what i’m looking for but I am having time visualizing what exactly you did.
Alexander Frazier - Sep 1, 2014
Clayton, I apologize, but the only examples I have are company spreadsheets. I’m not at liberty to share them with the public. But I’ll do the best I can to be less technical and explain this a little better. If you were to piece together a cell reference for use in an INDIRECT command, you might have “A” in A1 and “27” in B1, so that you can build the cell reference “A27” in C1 with a formula like =A1 & B1. Then in D1 you could use the formula =INDIRECT(C1), which would return, not the value A27, but the value OF A27. The values of “A” in A1 and “27” in B1 may change depending on other formulas you have, so that INDIRECT(C1) might return the value of A27, or D32, or whatever. That’s the power of the INDIRECT command. What most people are trying to do is use it to access closed workbooks or workbooks on remote servers, which INDIRECT simply will not do. What you CAN do is use a file or web path, with workbook and cell reference directly in your formula to retrieve the information you want. But this is not variable. If you have something set up to manage multiple stores, like I do, then you might want the same value from the same cell, but across ten or twelve workbooks. That means you have to either change your formulas, write a lengthy, complicated script that rewrites your formulas, or you have to find a way to make INDIRECT work on remote workbooks … which, again, just can’t be done. So what I did was use a Name in my formulas. In the Name Manager, you can create a Name that stands in place of a value, formula, or whatever you need it to stand in place of. The value of the Name Manager is that if you have a repeated, lengthy formula in numerous cells, you can clear up the congestion by creating a Name that IS the formula. And as fate would have it, a Name that stands in place of a file or web path, complete with workbook and cell reference, works just fine in a formula. As fate would also have it, a simple vb script can rewrite that single Name reference. So what you have to do is create a list of file or web paths and build your cell reference, just as in the example of building a cell reference for INDIRECT. Then have vb take the value of that cell (vb doesn’t distinguish between a constructed reference and a bonafide reference) and have it rewrite what the Name you’ve created refers to. Like so: Sub Change_Reference() New_Ref = Sheets(“Sheet”).Range(“Cell″) ActiveWorkbook.Names(“Name”).RefersTo = “=” & New_Ref End Sub New_Ref takes its value from the sheet and cell you specify. Then the Name you specify in the active workbook you’re using is made to refer to the new value you’ve specified for New_Ref. It’s really quite simple once you see it work.
Pull vba from Harlan Grove - Need Code - Sep 2, 2014
[…] document.write(’’); The vba will pull information from a closed workbook instead of requiring that it be open. I am using the function to link more than 30 workbooks for a weekly management report. A link I found was: Excel “Pull” Function: Creating dynamic links to closed workbooks | Numbermonger […]
Jan Pavuk - Jul 2, 2013
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?
reference to a particular cell in a closed workbook (indirect) - May 6, 2013
[…] INDIRECT does not work with closed workbooks. Try Harlan Groves’ PULL function Excel “Pull” Function: Creating dynamic links to closed workbooks | Numbermonger […]
Rob - May 1, 2013
Pappu - can you post the changes in code please? Thanks
aleksandra - Jul 2, 2013
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)
Ashley McCash - Jul 0, 2013
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 Pager - May 3, 2013
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
Alexander Frazier - Dec 3, 2014
If you want the 100 cells worth of values all on the same page at the same time, then each workbook you are referencing would need its own name. The purpose of the name is to allow variables. If you change the value the name represents, it changes the data you retrieve. If you have 100 specific cells you want to pull information from in a specific sheet, you can access that data directly. If the 100 cells you pull data from change within that sheet due to variables, you need to use a name so you can dynamically alter the cells you are retrieving data from, since INDIRECT won’t work on closed workbooks stored on a remote drive. If you are pulling up data from one workbook at a time, then you only need a single name. When you rewrite the name’s reference value, you can change both the workbook it refers to and the cells it retrieves at the same time. Just think of the indirect command and how it would be done if the command worked the way we want it to. You would piece together the drive, file name, etc., and you would piece together the cell reference. Then you would put them together using the INDIRECT command to access the data you want. For this method, build the reference the very same way. Then use vb to rewrite the reference value of the NAME to the fully constructed reference you’ve created.
Sum Data from Closed Work Sheets - Apr 6, 2013
[…] Checkout Harlan Grove’s custom user defined Pull function […]
Danesh - Feb 1, 2015
Pappu, I know that this post is years old, but I would really appreciate it if you could further explain how to get your alterations working in this code. I simply don’t understand how it works in conjunction with the pull code. Thanks!
Phil - May 4, 2013
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?
J - May 3, 2013
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
Trygve Wastvedt - Sep 3, 2015
I had the same issue. When I copy/pasted I got different characters for double quotes, single quotes, and minus signs than what I get when I type those on my keyboard. I ran a search and replace for those three characters and now it works for me.
Trygve Wastvedt - Sep 3, 2015
I had the same issue. I had to run a search and replace to convert the double quotes, single quotes, and minus signs to the characters I get from my keyboard. When I copy/pasted from the post above I got different symbols than the standard for those three.
Anderson Santos - Dec 5, 2014
Hello Guy, I tried to use de pull function but I’m had some problems. When the another workbook was into “C:” the function worked, but I have workbooks in a server, (H:, W:, F:) and when I used these workbooks that were into W: for exemple, the function didn’t work. The result was an error #REF. What can I do? Thanks.
peter Bedson - Mar 4, 2015
This seems to be a very elegant solution - you can use an automatic procedure on Worksheet Change to update the name reference if the control cells which build the reference change - that would let you have multiple different names driven off changes in the same WS. I knew that names don’t have to refer to a cell or to a range but can refer to anything including text or entire file paths (which I have used in the past) but had not made the jump to have them dynamically update like this. You learn something new every day…
Keith - Jun 5, 2017
I receive a #VALUE! error when I run this code. Any ideas why that would be?
Rex Barker - Dec 3, 2014
Simplified, yet again. Here, there is no need to add the ThisWorkbook_Open() method; the function checks if the object exists and creates one if it doesn’t. Paste this into a new VBA module in your Excel workbook. Watch for character translations that may have occurred due to pasting in the website: ‘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 filename = Trim(filename) path = Mid(filename, 1, InStrRev(filename, “\”)) file = Mid(filename, InStr(1, filename, “[”) + 1, InStr(1, filename, “]”) - InStr(1, filename, “[”) - 1) If Dir(path & file) = "" Then getvalue = “File Not Found” Exit Function End If If xlapp Is Nothing Then ‘Object must be created only once and not at each function call. Do not enable Set xlapp = CreateObject(“Excel.application”) End If ’ Create the argument arg = “’” & filename & “’!” & Range(ref).Range(“A1”).Address(, , xlR1C1) ‘Execute an XLM macro getvalue = xlapp.ExecuteExcel4Macro(arg) End Function
Jerm - Jul 4, 2015
Hi! Will anyone know if the PULL function would works for dynamic name range reference to a closed workbook? The name range is for example ‘[Data.xlsx]Leads’!$B$5:$B$23742 and the row number 23742 would change according to the last row of data in the closed workbook hence I need it to be dynamic and still able to use it within Sumproduct formula. Any help would be great!!
Can a spreadsheet update a spreadsheet that is not open - May 4, 2015
[…] document.write(’’); have read here Excel “Pull” Function: Creating dynamic links to closed workbooks | Numbermonger […]
Jay - Jun 4, 2015
Rex, your final function is awesome. Easy to use (other than the many copy-paste issues) and absurdly fast compared to Harlan’s pull function. Just wanted to say thanks, it saved me on a project that would have been impractically slow using the other method.
Norman - Sep 4, 2015
This line is giving me an error: file = Mid(filename, InStr(1, filename, “[”) + 1, InStr(1, filename, “]”) – InStr(1, filename, “[”) – 1) Any ideas why?
Numbermonger - Nov 2, 2015
Rex, Would you be able to post and link to a .txt file with this function? Having difficulty and I think it likely has to do with copying/pasting from the wordpress comment.