Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
mass data retreival from excel book in different folders
Hi,
i was wondering if the following could be achieved? i have approx 600 excel books all created from a master template. originally they were named indiviually as 1234 (job number) & "Quotes & Orders".xls where later i renamed the template to Quote book giving me job number and quote book. example: 1234 Quote Book.xls the excel books are found in various client folders in 1 parent folder named "Clients" in my Company Shared folder on my server. i need to retrieve ALL specific data from ALL of these books and place in list form in a single book. the data is found as follows in all books: Client Name = C1 Job No = C2 Description = C3 Project Date = C4 Job Value = E36 Total Spend = E33 Quoted Hrs = L19 Actual Hrs = M19 Can the be achieved? basically, i will need something to look through the complete folder, filter out all of these books and retrieve the data. thanks in advance, Nigel P.S. Also included in the parent folder "Clients" are other excel books for different tasks so i would need to filter the workbooks by "Quote Book" & "Quotes and orders" if possible. They are all exactly the same with the same ranges only as detailed above, renamed and specific to different projects. thanks, Nigel |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
mass data retreival from excel book in different folders
Hi Nigel,
I posted something recently that comes close to doing what you want http://tinyurl.com/gxvc9 In Function FilesToCol, change sFile = Dir(sPath & "\*.xls") to sFile = Dir(sPath & "\Quote*.xls") Regards, Peter T "Nigel" wrote in message ... Hi, i was wondering if the following could be achieved? i have approx 600 excel books all created from a master template. originally they were named indiviually as 1234 (job number) & "Quotes & Orders".xls where later i renamed the template to Quote book giving me job number and quote book. example: 1234 Quote Book.xls the excel books are found in various client folders in 1 parent folder named "Clients" in my Company Shared folder on my server. i need to retrieve ALL specific data from ALL of these books and place in list form in a single book. the data is found as follows in all books: Client Name = C1 Job No = C2 Description = C3 Project Date = C4 Job Value = E36 Total Spend = E33 Quoted Hrs = L19 Actual Hrs = M19 Can the be achieved? basically, i will need something to look through the complete folder, filter out all of these books and retrieve the data. thanks in advance, Nigel P.S. Also included in the parent folder "Clients" are other excel books for different tasks so i would need to filter the workbooks by "Quote Book" & "Quotes and orders" if possible. They are all exactly the same with the same ranges only as detailed above, renamed and specific to different projects. thanks, Nigel |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
mass data retreival from excel book in different folders
Hi Peter T,
So your code will look through a folder into sub folders, filter out all of the excel books with the names Quote Book and Quotes & Orders, and copy the required cell ranges into a separate book in list form? i can write the code to list the data in my format but its the reteival of the information i am struggling with. thanks, Nigel "Nigel" wrote: Hi, i was wondering if the following could be achieved? i have approx 600 excel books all created from a master template. originally they were named indiviually as 1234 (job number) & "Quotes & Orders".xls where later i renamed the template to Quote book giving me job number and quote book. example: 1234 Quote Book.xls the excel books are found in various client folders in 1 parent folder named "Clients" in my Company Shared folder on my server. i need to retrieve ALL specific data from ALL of these books and place in list form in a single book. the data is found as follows in all books: Client Name = C1 Job No = C2 Description = C3 Project Date = C4 Job Value = E36 Total Spend = E33 Quoted Hrs = L19 Actual Hrs = M19 Can the be achieved? basically, i will need something to look through the complete folder, filter out all of these books and retrieve the data. thanks in advance, Nigel P.S. Also included in the parent folder "Clients" are other excel books for different tasks so i would need to filter the workbooks by "Quote Book" & "Quotes and orders" if possible. They are all exactly the same with the same ranges only as detailed above, renamed and specific to different projects. thanks, Nigel |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
mass data retreival from excel book in different folders
Hi Peter T,
So your code will look through a folder into sub folders, filter out all of the excel books with the names Quote Book and Quotes & Orders, and copy the required cell ranges into a separate book in list form? Should do. You mention list, the demo populates an array and dumps onto a sheet. i can write the code to list the data in my format but its the reteival of the information i am struggling with. Not sure what you are struggling with, try adapting changes as follow referrring to Sub Test() in the demo ReDim va(1 To col.Count, 1 To 2) ReDim va(0 To col.Count, 1 To 9) va(0,1) = "File Name" va(0,2) = "Client Name" va(0,3) = etc va(i, 2) = wb.Worksheets(1).Range("A1").Value va(i, 2) = wb.Worksheets(1).Range("C1").Value va(i, 3) = wb.Worksheets(1).Range("C2").Value va(i,4) = etc ' maybe change Worksheets(1) to Worksheets("SheetName") ' code .Worksheets(1).Range("A1:B1").Resize(UBound(va)).V alue = va ..Worksheets(1).Range("A1").Resize(UBound(va) + 1, UBound(va, 2)).Value = va Regards, Peter T "Nigel" wrote: Hi, i was wondering if the following could be achieved? i have approx 600 excel books all created from a master template. originally they were named indiviually as 1234 (job number) & "Quotes & Orders".xls where later i renamed the template to Quote book giving me job number and quote book. example: 1234 Quote Book.xls the excel books are found in various client folders in 1 parent folder named "Clients" in my Company Shared folder on my server. i need to retrieve ALL specific data from ALL of these books and place in list form in a single book. the data is found as follows in all books: Client Name = C1 Job No = C2 Description = C3 Project Date = C4 Job Value = E36 Total Spend = E33 Quoted Hrs = L19 Actual Hrs = M19 Can the be achieved? basically, i will need something to look through the complete folder, filter out all of these books and retrieve the data. thanks in advance, Nigel P.S. Also included in the parent folder "Clients" are other excel books for different tasks so i would need to filter the workbooks by "Quote Book" & "Quotes and orders" if possible. They are all exactly the same with the same ranges only as detailed above, renamed and specific to different projects. thanks, Nigel |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
mass data retreival from excel book in different folders
so you have no problem opening each workbook one by one, then try this
rows(1).insert open workbook1 range("C1").copy ThisWorkBook.range("A1") range("C2").copy ThisWorkBook.range("B1") ... ... ... workbook1.close rows(1).insert loop to open next workbook "Nigel" wrote in message ... Hi Peter T, So your code will look through a folder into sub folders, filter out all of the excel books with the names Quote Book and Quotes & Orders, and copy the required cell ranges into a separate book in list form? i can write the code to list the data in my format but its the reteival of the information i am struggling with. thanks, Nigel "Nigel" wrote: Hi, i was wondering if the following could be achieved? i have approx 600 excel books all created from a master template. originally they were named indiviually as 1234 (job number) & "Quotes & Orders".xls where later i renamed the template to Quote book giving me job number and quote book. example: 1234 Quote Book.xls the excel books are found in various client folders in 1 parent folder named "Clients" in my Company Shared folder on my server. i need to retrieve ALL specific data from ALL of these books and place in list form in a single book. the data is found as follows in all books: Client Name = C1 Job No = C2 Description = C3 Project Date = C4 Job Value = E36 Total Spend = E33 Quoted Hrs = L19 Actual Hrs = M19 Can the be achieved? basically, i will need something to look through the complete folder, filter out all of these books and retrieve the data. thanks in advance, Nigel P.S. Also included in the parent folder "Clients" are other excel books for different tasks so i would need to filter the workbooks by "Quote Book" & "Quotes and orders" if possible. They are all exactly the same with the same ranges only as detailed above, renamed and specific to different projects. thanks, Nigel |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mass move data in rows to columns. | Excel Discussion (Misc queries) | |||
Mass book data retrieval | Excel Discussion (Misc queries) | |||
grouping mass date data | Excel Discussion (Misc queries) | |||
mass cell data editing | Excel Worksheet Functions | |||
Auto Refreshing: data retreival from webpage | Excel Programming |