Solved

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

Posted on 2011-03-22
12
321 Views
Last Modified: 2012-08-14
Thanks,
JP
0
Comment
Question by:easycapital
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 50
ID: 35187831
Hello,

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

cheers, teylyn
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 35187834
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
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 334 total points
ID: 35187841
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
Industry Leaders: 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!

 

Author Comment

by:easycapital
ID: 35192161
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
 

Author Comment

by:easycapital
ID: 35192186
Also, what if one of those files are open by another.  Can I still pull the info?
Thanks,
JP
0
 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 334 total points
ID: 35193386
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
 

Author Comment

by:easycapital
ID: 35203185
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
 
LVL 50
ID: 35203501
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
 

Author Comment

by:easycapital
ID: 35203620
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
 
LVL 6

Assisted Solution

by:rbrhodes
rbrhodes earned 166 total points
ID: 35205433
There are Excel4 functions that will pull info from a closed file if you know the range
0
 

Author Comment

by:easycapital
ID: 35385272
Sorry for delay accepting the solutions.  
JP
0
 

Author Closing Comment

by:easycapital
ID: 35385308
Thank you,
JP
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question