Aggregating data from various file in one.
 

I have a set of data files with the same format that I would like to
add up in a summary excel file. So for example, I would like to add
all the A1 cells of all the sheet1 worksheets of all data files to cell
A1 of the sheet1 worksheet of the summary file. How can I do it?
Thanks in advance for any help.

Aggregating data from various file in one.
 

do you have a list of data files. Or are all the data files in a single
directory with no other files. Or are they all in a single directory, with
other files, but they all have a common unique element in their name?

sub ProcessWorkbooks()
Dim cell as Range, sPath as String, sName as String
Dim bk as Workbook, v as Variant
set cell = thisworkbook.Worksheets("sheet1").Range("A1")
spath = "C:\Myfiles\"
sname = dir(sPath & "*.xls")
do while sName < ""
set bk = workbook.Open(sPath & sName)
v = bk.worksheets("Sheet1").Range("A1")
bk.close Savechanges:=False
if isnumeric(v) then
cell = cell + v
end if
sName = dir()
Loop
End sub

would be the approach for all the *.xls files in a single directory.

--
Regards,
Tom Ogilvy


"lt" wrote:

I have a set of data files with the same format that I would like to
add up in a summary excel file. So for example, I would like to add
all the A1 cells of all the sheet1 worksheets of all data files to cell
A1 of the sheet1 worksheet of the summary file. How can I do it?
Thanks in advance for any help.

Aggregating data from various file in one.
 

Thanks so much Tom!

Tom Ogilvy wrote:
do you have a list of data files. Or are all the data files in a single
directory with no other files. Or are they all in a single directory, with
other files, but they all have a common unique element in their name?

sub ProcessWorkbooks()
Dim cell as Range, sPath as String, sName as String
Dim bk as Workbook, v as Variant
set cell = thisworkbook.Worksheets("sheet1").Range("A1")
spath = "C:\Myfiles\"
sname = dir(sPath & "*.xls")
do while sName < ""
set bk = workbook.Open(sPath & sName)
v = bk.worksheets("Sheet1").Range("A1")
bk.close Savechanges:=False
if isnumeric(v) then
cell = cell + v
end if
sName = dir()
Loop
End sub

would be the approach for all the *.xls files in a single directory.

--
Regards,
Tom Ogilvy


"lt" wrote:

I have a set of data files with the same format that I would like to
add up in a summary excel file. So for example, I would like to add
all the A1 cells of all the sheet1 worksheets of all data files to cell
A1 of the sheet1 worksheet of the summary file. How can I do it?
Thanks in advance for any help.