Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

Can we extract information from an excel workbook if it is closed? (excel 2003)

Thanks,
JP
0
easycapital
Asked:
easycapital
3 Solutions
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

that depends on the function you want to use. Vlookup(), for example works fine with closed workbooks. Indirect() does not.

cheers, teylyn
0
 
Dave BrettCommented:
Yes

- with links (easiet way, no code)
- using XLM (prior macro language)
- using ADO (with VBA)

There are plenty of examples of the later two

Links is the best manual method - if you know where the info is to be pulled
If you are using code, then programmatically it is easiest to open the workbook in the background for the manipulation

Cheers
Dave
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
The best way to figure out if your scenario works with closed workbooks is to establish the link with both workbooks open. Then close both files, and open just the one that has the formulas linking to the closed workbook. Re-calculate with F9 and see if there are errors.

For some functions, there are free add-ins that provide alternatives to the standard Excel functions.

cheers, teylyn
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
easycapitalAuthor Commented:
This quesiton is regarding portfolio management.  There are many different files with same file structure.  So I want to do a summary of all of them and create an up to date report, instead of having to go to each file, looking at a cell value, to then put in the report.

Thanks,
JP
0
 
easycapitalAuthor Commented:
Also, what if one of those files are open by another.  Can I still pull the info?
Thanks,
JP
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

if you use macros to open the file in the background, then you may need to suppress the warning dialog that another user has opened the file. If you use formulas to extract cell values from a file, that should not be an issue.

You can reference a cell address with a statement like this when the other file is open

='[Book1.xlsm]Sheet1'!$A$1

Then, when you close the source file, Excel will change the reference to a full path, for example.

='C:\Documents and Settings\<user name>\My Documents\[Book1.xlsm]Sheet1'!$A$1

If you know the full path, file name, sheet name and cell address of the required cell value, you can type it into Excel directly

Cheers, teylyn
0
 
easycapitalAuthor Commented:
Ok. Here is my scenario.  I can determine where are each of the files are saved.  So I could write somewhere the location where each file is saved in the server.  

I then want to pull the same information from each file.  

Manually clicking on each file seems a bit, too manual.  So, what strategy can I use to change the formula so that it adapts itself to the new path.  I am looking to a fill out a report - same rows and the header is the location of the file.  

Could a manually do the formulas for one file and then just specify the other file locations and the formula will adapt itself?

Thanks,
JP
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Going down that route you will end up trying to construct a cell reference to an external file from a text string stored in another cell. This can be done using Indirect() but this command works only with open files.

You can download and install the free add-in Morefunc.xll, which has a command called Indirect.Ext() that can work with closed files. Here's the download link http://download.cnet.com/Morefunc/3000-2077_4-10423159.html

cheers, teylyn
0
 
easycapitalAuthor Commented:
Teylyn,

Very interesting function.  I am not sure that IT guys will let me install on the work computer :(

Everyone,
If I was to specify the question clearly and perhaps draw what I have in mind, could there be way where I can obtain the information using a macro?

Thanks,
JP
0
 
rbrhodesCommented:
There are Excel4 functions that will pull info from a closed file if you know the range
0
 
easycapitalAuthor Commented:
Sorry for delay accepting the solutions.  
JP
0
 
easycapitalAuthor Commented:
Thank you,
JP
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now