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.

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.
>
>

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.
> >
> >