Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-03-22
12
Medium Priority
?
322 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 1336 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
Independent Software Vendors: 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 1336 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 664 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

Independent Software Vendors: 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!

Question has a verified solution.

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

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;…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

715 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