Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jul 2005
    Location
    Enfield, Middlesex, United Kingdom
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filtering/searching multiple workbooks/sheets (2002)

    First time, so please be gentle...

    I need to analyse a series of workbooks (Someone else, so don't start on the format...)

    There are 7 workbooks in one folder on the server. Each workbook is for one month, and contains 1 worksheet for each day of the month.

    I want a new workbook that pulls data from all the worksheets in all the workbooks...if this is possible...

    I have attached a sample worksheet. I need to pull all the rows for which column "H" = "F Rental" or "F/Rental".

    I don't really know even where to start, so any ideas will be gratefully received, and please treat me like I know nothing...I may know a bit more, but best to assume not!

    Thanks Guys,

    Alaric.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtering/searching multiple workbooks/sheets (2002)

    Welcome to Woody's Lounge!

    I have assumed that you want to combine the rows into one worksheet.

    You will need to make sure that the 7 workbooks you mention are the ONLY .xls files in the folder; that enables us to loop through the files in code.

    I have attached the code for a macro. To use it. do the following:
    - Create a new workbook with one worksheet.
    - Set column headers in row 1 the way you want (you can copy the headers from one of the sheets in one of the workbooks)
    - Activate the Visual Basic Editor (Alt+F11)
    - Create a new module (Insert | Module)
    - Copy the code from the attachment and paste it into the module.
    - Change the path F:XLTest to the path of the folder containing your workbooks. The path must end in a backslash .
    - Switch back to Excel (Alt+F11).
    - Save the workbook now.
    - Select Tools | Macro | Macros... or press Alt+F8
    - Select ImportFromFolder from the list of available macro names.
    - Click Run.

  3. #3
    New Lounger
    Join Date
    Jul 2005
    Location
    Enfield, Middlesex, United Kingdom
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering/searching multiple workbooks/sheets (2002)

    That's excellent, thanks Hans - worked perfectly.

    It's nice to know that it's a bit more complicated than just clicking buttons in excel...I tried that!

    Thanks again...I'd like to offer to return the favour in the future, but I think the chances of my be able to help are small...

    Alaric.

  4. #4
    New Lounger
    Join Date
    Jul 2005
    Location
    Enfield, Middlesex, United Kingdom
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering/searching multiple workbooks/sheets (2002)

    Hans,

    Is it possible to make a slight amendment to this search?

    I would like to be able to include both the name of the workbook and the name of the worksheet against the rows of data. Is it possible to add this information to either column a and b of the final report, or in a separate row preceeding each row of data?

    Thanks,

    Alaric.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtering/searching multiple workbooks/sheets (2002)

    Alaric,

    You can replace the line that copies a row (i.e. wsh.Rows(...).Copy ...) with the following lines. The first places the name of the workbook in column A, the second the name of the worksheet in column B, and then the row is copied into column C and following. The Copy instruction has changed slightly because we can't copy the entire row now.

    wshCur.Range("A" & lngCurRow) = wbk.Name
    wshCur.Range("B" & lngCurRow) = wsh.Name
    wsh.Range("A" & lngRow & ":P" & lngRow).Copy Destination:= _
    wshCur.Range("C" & lngCurRow)

  6. #6
    New Lounger
    Join Date
    Jul 2005
    Location
    Enfield, Middlesex, United Kingdom
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering/searching multiple workbooks/sheets (2002)

    Excellent - thank you very much.

    Alaric.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •