Importing a Value from many Excel Files

[Guest]

Hello All,

I use a template to complete a weekly Expense Report. On every report the
cell J30 is always the Total dollar amount

What I would like to do is create another Excel worksheet that imports just
cell J30 from all of my past Expense Reports to get a running tally. Other
than opening each file (200+) and linking the data or cutting and pasting, is
there an easier way?

Thank you

 

[Gord Dibben]

Check out Ron de Bruin's site for code to pull from closed workbooks.

http://www.rondebruin.nl/ado.htm


Gord Dibben MS Excel MVP

 

[Ragdyer]

This can be accomplished with functions.

The trick is to be able to enter the various paths with the WB and WS names
into the formulas without having to do so individually.

Give an example of the full path together with a couple WB and WS names, so
that the naming "pattern" is exhibited.

You do realize that if you opened 1 of these expense reports together with
your new "master" report, and then created a link between them, and then
closed the expense report, the 'master' would display the formula you're
looking to create, with the full path.
All you would have to do is copy this formula down, and just change the WB
name to get exactly the formulas you're looking for.

Trying to make this *changing of the WB name* automatic, is what you're
actually looking to accomplish.

Post some examples of their names and paths, and let's see what can be done.