How can I change the text to reference?

  • Thread starter Thread starter Christopher Panadol
  • Start date Start date
C

Christopher Panadol

I have used INDIRECT() function to get the data from a cell in a file from a cobined text
but it should under the condition that the file should be opened, otherwise the data will
not be retrieved. Is there any function rather than INDIRECT() enable me to retrieve data
without opening the related file?
 
Not really.

You can use a macro to build a hard coded link, same as entering it by had.
Have the macro change it as appropriate using an event perhaps.
 
You could use a defined name to point to a starting point in your closed
workbook. For example, if Book2.xls is closed and it contains 3 sheets
(Sheet1, Sheet2, Sheet3) and you want to get an indirect reference to
Sheet1!A1, Sheet2!A1, or Sheet3!A1, then you could define:

sht1 = "[Book2.xls]Sheet1!A1"
sht2 = "[Book2.xls]Sheet2!A1"
sht3 = "[Book2.xls]Sheet3!A1"

Then use OFFSET(INDIRECT(sht1),x,y) to get a reference...

Modify as appropriate, obviously.

/i.
 
This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/UsingSqlRequest.zip
It's in the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

This workbook demonstrates how to get data direct from an MS Access table,
or from an open or closed MS Excel workbook using the workbook function
SQL.REQUEST.

Recently updated to show the use of SQL.REQUEST in the same workbook.

The code is open and commented.
--

Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"
 
I have used INDIRECT() function to get the data from a cell in a file from a
cobined text but it should under the condition that the file should be opened,
otherwise the data will not be retrieved. Is there any function rather than
INDIRECT() enable me to retrieve data without opening the related file?
Click to expand...

Macro trickery, add-in function or udf required to do this. See

http://www.google.com/[email protected]
 
Not really.
Click to expand...

No, really!

It requires launching a separate Excel application instance via Automation, then
calling ExecuteXL4Macro from that other instance, but it *CAN* be done from a
udf (at least in XL97 and XL2K - maybe XLXP and later are crippled in this
respect).
 
Back
Top