Working with multiple Excel files

[WayneF]

I have about 10 excel files called Accounts 1 - 10

In each Accounts Excel file in Cell A1 I have a value ( can be anything )

I want to create another excel file with the name totals.

Inside totals I want it to go into each of the 10 account files and get the
value stored in Cell A1

Any suggestiong on the best way of doing this ?

 

[Ron de Bruin]

Hi WayneF

Try this
http://www.rondebruin.nl/summary2.htm

 

[hansyt]

Open all the workbooks. In A1 of your summary sheet type: =
Then navigate to the first Accounts workbook and there to the cell to
include in the total
go back to the summary sheet,
click in the formula bar and type: +
navigate to the second Accounts workbook etc

Repeat the procedure until you are done and hit return

Close all Accounts workbooks and then save your summary worksheet.


Hans

 

[WayneF]

Hi,

Nice code but I cant get it to work ...

I copied and pasted the code ... I changed 1 lines ...

Set Rng = Range("A1,D5:E5,Z10") '<---- Changed to ...
Set Rng = Range("R20") '<---- Changed

The other line ....
ShName = "Sheet1" '<---- Change
I did not change this line because in my workbook the sheet name is
still Sheet1.

The result i get after running this is ...

A new workbook, with 1 line,
A1 = The name of the workbook (Coloured yellow)
A2 = Blank (Coloured yellow)

Any ideas why this aint working ? I get no value at all for A2 ( I guess
the value from R20 should appear here ?)

Cheers,

Wayne

 

[WayneF]

Hi Hans,

This works BUT .... see this post I have already sent and had no answer to
????

Dear all,

After some advise here,

I have several workbooks all with different names but in the same
directory

I.E.

Accounts1.xls
Accounts2.xls
Accounts3.xls
Accounts4.xls

In each of the above I want to get out the value for say R20.
I have a new workbook called AccountTotals.xls

I open all workbooks and put = then select the different R20 in each
book.

This works fine ... I get the totals.
In each cell I get this ...
='[Excel1.xls]Account1'!$R$20 <-------- This returned the
correct value.
Saved the document ...

When i opened the document again it asked if i wanted to "update" the
workbook ... which i did want to have the updated info, the problem now is
that it changed the cell value to this ...

='C:\Documents and Settings\Wayne\Desktop\Wayne''s Account\Weeks
2006\[Excel1.xls]Account1'!$R$20

Which is really not what i want because i will not be able move this
folder and have this Totals sheet working. AKA if i want to give it a
college they can not use it !!!

Any ideas or better ways to do this ?

Cheers,

Wayne

 

[Ron de Bruin]

Hi WayneF

That means that there is no "Sheet1" in the worbooks you select