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?
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
I like it. It's genius.

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

Thanks,
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top