Microsoft Office Tutorials and References
In Depth Information
USE A CUSTOM PULL FUNCTION INSTEAD OF INDIRECT WITH A CLOSED WORKBOOK
NewName = NewName & ThisChar
ws.Name = Left(NewName, 31)
Summary: You can use a short macro to rename worksheets.
USE A CUSTOM PULL FUNCTION INSTEAD
OF INDIRECT WITH A CLOSED WORKBOOK
Challenge: As you know from several other topics, the INDIRECT function is
fairly cool. Say that you want to use INDIRECT to go out to a variable workbook
and grab a value. You store each day’s records in a workbook with a name
similar to C:\aaa\Sales20090801.xls . INDIRECT does not work in this
situation because it cannot point to a closed workbook. It can point to an open
workbook, but you would not want to open 365 workbooks every time you had
to calculate the worksheet.
Solution: As documented by Frank Kabel, at http://www.dailydoseofexcel.
com/archives/2004/12/01/indirect-and-closed-workbooks/, there are three
workarounds for this problem. The one I use is Harlan Grove’s custom function
PULL . The function can even return a range of cells from the closed workbook,
so it can be used as the table range in a VLOOKUP .
Follow these steps to install PULL in your workbook:
Browse to http://members.aol.com/hrlngrv/pull.zip to get the latest version
Unzip pull.bas from the zip ﬁ le.
Open the workbook where you want to use PULL .
Switch to the VBA editor by pressing Alt+F11.
From the VBA editor menu, choose File, Import.
Browse to and select pull.bas.
Figure 132 illustrates the use of PULL . Column A contains a date. Based on
this date, you want to look up sales from a different daily workbook. All the
workbooks are stored in C:\aaa\, with names such as SALESyymmdd.xls.
Cell C3 formats the date in YYYYMMDD format using the formula
Cell D3 builds the ﬁ le path and name with ="C:\aaa\[Sales"&C3&".xls]" .