Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summarizing data from multiple (1000s) of workbooks into one wrksh
Working with XL2003
I am trying to write a workbook_open event that will summarize data found in 9 different cells in 1000's of workbooks with identical structure. I'd like the data to summarize into a single worksheet with 10 columns (first column to be the file name of the workbook being summarized). All of these workbooks reside in a single directory on our server (ex "L:\\Quotes\") but the summary workbook will be in a different directory (ex "M:\\Monthly Summary Report Data\" ) My Summary report structure looks like: ColA = summarized workbook file name "fname" ColB = fname'Quote Form'!$D$4 'Dealer Name ColC = fname'Quote Form'!$J$2 'Project Name ColD = fname'Quote Form'!$J$11 'sales Rep ID ColE = fname'Quote Form'!$D$1 'Date & time file last_changed ColF = fname'Bill of Materials'!$V$312 'USD Quote Value ColG = fname'Bill of Materials'!$V$311 'CDN Quote Value ColH = fname'Bill of Materials'!$V$307 'commission included as % ColI = fname'Bill of Materials'!$V$309 'Company Gross Margin_(calculated) ColJ = fname'Quote Form'!$D$20 'Product Group ColK = fname'Quote Form'!$D$21 'Quote composition These cell references all contain values derived via formulaes, I only want to copy the value (not the formula) into my summary -- I definitely do not want to alter in any way the original data and I don't want links to that original data. All of the workbooks (and the worksheets) that are being summarized are protected with a common set of passwords (PWORD_Workbook and PWORD_Worksheet) and are emailed to our ' email account by hundreds of users around the country automatically whenever they save a quote done using our quotation.xls application (Thanks to Ron De Bruin's CBO emailing code! and all of the help I've gotten here from others that I collectively refer to as my "Excellent Helpers") Each time I open the summary workbook, I want it to update itself with all of the new records rec'd since my last summary. I've been muddling around with code from Ron's site but just don't have enough VBA experience to figure this out. Will anyone here help? Many thanks in advance, Steve E |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summarizing multiple worksheet data | Excel Worksheet Functions | |||
Multiple Worksheets, multiple lines and summarizing into one works | New Users to Excel | |||
Compiling information from multiple workbooks and summarizing | Excel Worksheet Functions | |||
How to Link a horizontal row of data to a column in another wrksh. | Excel Discussion (Misc queries) | |||
Summarizing data in multiple excel files | Excel Programming |