Solved

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

Posted on 2011-03-22
12
316 Views
Last Modified: 2012-08-14
Thanks,
JP
0
Comment
Question by:easycapital
12 Comments
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
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
Comment Utility
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:
teylyn earned 334 total points
Comment Utility
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
 

Author Comment

by:easycapital
Comment Utility
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
Comment Utility
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:teylyn
teylyn earned 334 total points
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:easycapital
Comment Utility
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

Expert Comment

by:teylyn
Comment Utility
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
Comment Utility
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
Comment Utility
There are Excel4 functions that will pull info from a closed file if you know the range
0
 

Author Comment

by:easycapital
Comment Utility
Sorry for delay accepting the solutions.  
JP
0
 

Author Closing Comment

by:easycapital
Comment Utility
Thank you,
JP
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now