mass data retreival from excel book in different folders

[Nigel]

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

[Peter T]

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" <[email protected]> wrote in message
news:[email protected]...
> 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

[Nigel]

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

[Peter T]

> 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)).Value = 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

[PY & Associates]

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" <[email protected]> wrote in message
news:[email protected]...
> 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