mass data retreival from excel book in different folders

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nigel - 22 Mar 2006 10:31 GMT

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 to this Message

Peter T - 22 Mar 2006 11:26 GMT

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

> Hi,
>
[quoted text clipped - 35 lines]
>
> Nigel

Reply to this Message

Nigel - 22 Mar 2006 11:46 GMT

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

> Hi,
>
[quoted text clipped - 35 lines]
>
> Nigel

Reply to this Message

Peter T - 22 Mar 2006 12:14 GMT

> 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

> > Hi,
> >
[quoted text clipped - 35 lines]
> >
> > Nigel

Reply to this Message

PY & Associates - 23 Mar 2006 02:50 GMT

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

> Hi Peter T,
>
[quoted text clipped - 47 lines]
> >
> > Nigel

Reply to this Message