Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Aggregating data among multiple workbooks | Excel Worksheet Functions | |||
Aggregating Data in Other Category on Pie Chart | Charts and Charting in Excel | |||
Aggregating data for a chart | Charts and Charting in Excel | |||
Aggregating data | Excel Discussion (Misc queries) | |||
Aggregating data | Excel Programming |