Hi guys, I looked all over the web for an answer to this, but i can’t seem to find anything, so i figured i’ll try asking here before i start using macros or something :(. I’m trying have a cell pull data from a external workbook using a formula with the filename being the date the report was generated. =‘C:\Reports[2009-01-12.xls]Sheet1’!B9 My problem is that i’m trying have the filepath automatically updated each day. so tomorrow it’ll be =‘C:\Reports[2009-01-13.xls]Sheet1’!B9 I tried using a today() function, and concatenating it as a string then tried using the indirect() function, but no luck, i keep getting a #REF. Has anybody here had any success with this, or know how to get it work without using macros?
I don’t know of a way to do what you’re asking, but a workaround would require minimal effort on your part. If you have one page of formulas that you want to update every day. Why don’t you open said page of formulas along with ‘C:\Reports[2009-01-12.xls]Sheet1’!B9. Save the latter on your desktop or a subfolder or whatever as ‘Daily Position’. Then each morning when the file is generated ('C:\Reports[2009-01-13.xls]) save it to the ‘Daily Position’ file and open both workbooks.
xck2000, There might be a simpler way, but what you need to do is use a combination of today(), indirect() functions and a macro that opens/closes the file in question. I believe you get the error because the data can’t be retrieved from a closed file. Good news, macro would be very simple one.
Jeez, RTFM… INDIRECT(ref_text,a1) Ref_text is a reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string. If ref_text is not a valid cell reference, INDIRECT returns the #REF! error value. If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.
vtkruk will know, that cat knows a lot about excel
In the MOREFUNC add in, there’s a function called INDIRECT.EXT() which will allow you to pull from closed workbooks. You can’t embed it in other functions, but i think there are other functions in there that will work, too
You could just update links. Go to edit-> links then select change source and select the new file. If that is too manual then use Trogdor’s advice.
You could just write a really simple function to do it so you have a function like YesterdaysWorkbook(SheetName as String, Cell as string). What’ll you give me to write one for you?
Thank guys! well the reason i wanted to avoid macros is i’m creating the spreadsheet and for someone and they use/maintain it. Apparently not everybody knows how to use and maintain macros and not everybody likes to have to update links or change filenames etc. but you’re right, the macro should be really simple, so it shouldn’t be that bad. If they complain too much, i’ll just force them to update links. and JDV? what would you like? 
Go download and install the free add-in Morefunc.xll from http://xcell05.free.fr/english/ Use the INDIRECT.EXT function instead since Indirect() does not reference a closed workbook. put this formula in your sheet: =Indirect.ext("‘C:\Reports["&TEXT(TODAY(),“yyyy-mm-dd”)&"]Sheet1’!B9") That’s your only way. Unless you want to add a VBA script that retrieves your values.
or a macro that opens and closes the referenced file as someone above suggested.
adalfu Wrote: ------------------------------------------------------- > Go download and install the free add-in > Morefunc.xll from http://xcell05.free.fr/english/ > > Use the INDIRECT.EXT function instead since > Indirect() does not reference a closed workbook. > > put this formula in your sheet: > > =Indirect.ext("‘C:\Reports["&TEXT(TODAY(),“yyyy-m > m-dd”)&"]Sheet1’!B9") > > That’s your only way. Unless you want to add a > VBA script that retrieves your values. Good skills. Only downside would be if it is not the C drive, but a network drive. Then every user would have to install the add-in. Still, that has to be limited.
Found within first Google query… There is VBA code defining the function that should do the trick. Just add it to the file that will be used and you wouldn’t have to worry about people installing plug-in on their computers. http://www.dicks-blog.com/archives/2004/12/01/indirect-and-closed-workbooks/ « Disabling Macros on StartupGetting the last value » INDIRECT and closed workbooks 1st December 2004, 12:54 pm by Frank Kabel 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: 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:temp[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:temp[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. 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. 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:temp[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. Try this: '----- begin VBA ----- Function pull(xref As String) As Variant 'inspired by Bob Phillips and Laurent Longre 'but written by Harlan Grove '----------------------------------------------------------------- 'Copyright © 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 -----