Pull data from closed file w/o opening it.

Milo_Minderbinder

Well-known Member
Joined
Mar 2, 2006
Messages
548
I am creating summary report that will pull data from several similar files (template).

All the files are in the same directory and the links will all be the same.

In the report file, I run a code that will list all the files in the directory in Col A. Then the code will insert formulas in various columns that will give me the values from certain cells for each of the listed files via links

eg
Code:
ActiveCell.Offset(0, 1).Formula = "='C:\MyDocs\Excel\[" & Filenm & "]Sheet1'!$A$2"

This way, I never have to open any of the source documents, and there could be hundreds.

This works fine, if not the best method, for individual cell values. I need a way to get more info from ranges like sum, average, count etc.

The summarysheet looks something like this:
Book1
ABCDEF
1FileNameTotalUserDateAverageCountInputs
2File1$100User110/2/2006##############
3File2$150User311/5/2005##############
4File3$200User1511/15/1955##############
Sheet1


Total, User and Date will always be in Cells A2, A3 & A4 of the source file.

I haven't had any luck with INDIRECT, either.

Any ideas?
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

INDIRECT will not work with a closed file - it will only work with open files.

What would the formulas in E2 and F2 look like if the files were open? Do they work on a range in a single file? or do they look at data from a single cell across multiple files?


Tony
 

Milo_Minderbinder

Well-known Member
Joined
Mar 2, 2006
Messages
548
Hi


What would the formulas in E2 and F2 look like if the files were open? Do they work on a range in a single file? or do they look at data from a single cell across multiple files?


Tony

These calcs would be for a range in a single file:

E2 would look like
Code:
=Average('[File1.xls]Sheet1'!$A$10:$A$100)

F2 would look like
Code:
=COUNTIF('[File1.xls]Sheet1'!$A$10:$A$100,">0")

If the files are closed, the full path is there.
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

As long as you can determine the starting and finishing cells, you should be able to take the same approach for the Average and Sum formulas.

However, countif doesn't seem to work on a closed file. However, SUMPRODUCT seems to work. So change the COUNTIF function for SUMPRODUCT and see if that helps

A replacement SUMPRODUCT for the above countif would be something like

=sumproduct(--('C:\MyDocs\Excel\[file1.xls]Sheet1'!$A$10:$a$100>0))

HTH

Tony
 

Milo_Minderbinder

Well-known Member
Joined
Mar 2, 2006
Messages
548
I like it. It's genius.

Sumproduct is truly the universal answer to excel stumpers. I need to remember to try that first.

Thanks,
 

Watch MrExcel Video

Forum statistics

Threads
1,113,811
Messages
5,544,448
Members
410,611
Latest member
JB_101920
Top