G
Guest
Does anyone know the code to use to be able to pull data from a closed workbook, without opening it?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
Dave Peterson
Harlan Grove posted a function that opens another instance of excel and then
retrieves the value from that closed workbook.
http://google.com/[email protected]
retrieves the value from that closed workbook.
http://google.com/[email protected]
T
Tom Ogilvy
without opening it?
I believe that opens the workbook, Dave.
Maybe suggesting John Walkenbach's method, or use of ADO or simply putting a
link in a cell (fastest) would be more appropriate.
I believe that opens the workbook, Dave.
Maybe suggesting John Walkenbach's method, or use of ADO or simply putting a
link in a cell (fastest) would be more appropriate.
K
KJTFS
I would go with a link in a cell, if the cell in the workbook you ar
looking for is always changeing try using vlookup.
Keith
www.kjtfs.com
looking for is always changeing try using vlookup.
Keith
www.kjtfs.com
D
Dave Peterson
Harlan corrected me on this one, too. I once posted that it starts another
instance of excel and opens the other file there and retrieves the value.
Actually, it uses the same techniques that John Walkenbach does to retrieve a
value from a closed workbook.
But it wouldn't be necessary to use Harlan's routine in a case where the macro
itself will retrieve the value.
To the OP: Harlan's routine is very nice when called from a worksheet where the
closed workbook can vary (based on another cell--and =indirect() would be used.
And in the most basic sense, I guess I agree with You and Keith--just use a
formula in a cell that would point to the cell you want to retrieve.
instance of excel and opens the other file there and retrieves the value.
Actually, it uses the same techniques that John Walkenbach does to retrieve a
value from a closed workbook.
But it wouldn't be necessary to use Harlan's routine in a case where the macro
itself will retrieve the value.
To the OP: Harlan's routine is very nice when called from a worksheet where the
closed workbook can vary (based on another cell--and =indirect() would be used.
And in the most basic sense, I guess I agree with You and Keith--just use a
formula in a cell that would point to the cell you want to retrieve.
D
Dave Peterson
And if the OP wants to use the John Walkenbach's techniques:
http://j-walk.com/ss/excel/eee/eee009.txt
Look for either: GetDataFromClosedFile or GetValue.
http://j-walk.com/ss/excel/eee/eee009.txt
Look for either: GetDataFromClosedFile or GetValue.
T
Tom Ogilvy
Ok, I stand corrected, but then it does incur overhead beyond John's
technique. In any event, I wouldn't use the word "nice" to describe opening
another instance of Excel in a UDF/as part of the calculation chain, but if
the requirement is essential, then it does the job. <g>
technique. In any event, I wouldn't use the word "nice" to describe opening
another instance of Excel in a UDF/as part of the calculation chain, but if
the requirement is essential, then it does the job. <g>
D
Dave Peterson
I only meant nice when you'd have to use =indirect().
And I agree wholeheartedly about overkill in some (this??) case.
But sometimes answers come from muscle memory--not the grey wet stuff.
Happy New Year.
And I agree wholeheartedly about overkill in some (this??) case.
But sometimes answers come from muscle memory--not the grey wet stuff.
Happy New Year.
Tom said:Ok, I stand corrected, but then it does incur overhead beyond John's
technique. In any event, I wouldn't use the word "nice" to describe opening
another instance of Excel in a UDF/as part of the calculation chain, but if
the requirement is essential, then it does the job. <g>