Solved

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

Posted on 2011-03-22
12
320 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
autofill formulas using macro 8 54
drag and drop a row in excel 2010 5 44
Excel - IF criterion 2 24
Excel VBA to convert a string into a formula 19 13
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
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 …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

751 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