1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Macro to Search All .xlsx in Folder and Return Column

Discussion in 'Business Applications' started by ccrossing, Sep 2, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. ccrossing

    ccrossing Thread Starter

    Joined:
    Sep 2, 2011
    Messages:
    10
    Hello, this is my first time posting as I have hit the limit of what functions can do and I need some VBA (which I am rubbish at).

    I have a folder of 30 or so .xlsx files (we have Excel 2010) that each contain a bunch of information about a data set we have (see attached files). For example, the TemperatureSAMPLE.xlsx sheet details the length of temperature data we have for each watershed (ex. Dog, Fish, Bear). What I need a macro to do is to go through all these .xlsx files and return the entire column to a new spreadsheet when it finds a match (for ex. Fish). The result would then be a summary of all the data we have for each site. There are about 30 parameters so it would take forever to assemble this by hand, but I am not proficient enough in VBA to know how to do it, or even to modify code to get it to work.

    Any help would be appreciated! I tried searching the forums and Google, but had no luck finding something very similar. Let me know if my explanation was opaque and I will try to make it clearer.

    Thank you.
     

    Attached Files:

  2. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    456
    Hello,

    I can try help you, i write macros all day long at work, and believe me sometimes i get so baffled myself.

    if you can, could you possibly explain is very simple terms what you want to do with the two workbooks you attached?

    i am off home in a little while but will probably have a little time to write up some code. just let me know what you need the macro to do.
     
  3. ccrossing

    ccrossing Thread Starter

    Joined:
    Sep 2, 2011
    Messages:
    10
    This is as simple as I know how to explain it:
    Search all the .xlsx in a folder for a value (watershed name, for ex. "Dog") and return the entire column containing the match into a new spreadsheet.

    Sometimes there is more than one match for each .xlsx (see "Dog" in attachments).

    The two attachments are to give you an idea of the layout of all the workbooks in the folder.

    Thank you.
     
  4. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    456
    oh right i understand what you mean, thats pretty straight forward, i should be able to help. im going home from work now though, but should be able to hopefully provide some code either later tonight or tomorrow. if not then on Monday.
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    You can use the "Filesearch" function to find your file's names and then set a loop to go through each one and copy it's data in to your worksheet.
    I would assist you with this but I can't open xlsx workbooks.
    Do you want to "browse" to where the fiels are or Hard Code it in the VBA?
     
  6. ccrossing

    ccrossing Thread Starter

    Joined:
    Sep 2, 2011
    Messages:
    10
    Thanks for the tip on the Filesearch function, the only problem is I don't know enough VBA to code it, but that sounds simple enough for a VBA programmer.

    As for the fields, the term we want to search for is always in row one, but the column will vary from sheet to sheet. I assume we would want to browse within a range if that is possible.

    I attached the same files as above in .xls if you want to have a go.

    Thank you ever so much for the help.
     

    Attached Files:

  7. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    What about the Folder, do you want it hard coded, or browsable?
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Temperature has 3 "Dog" columns, do you need to transfer all 3?
    Do they go alongside each other as in the current worksheet or below in one column?
     
  9. ccrossing

    ccrossing Thread Starter

    Joined:
    Sep 2, 2011
    Messages:
    10
    The folder location can be coded in. That is no problem.

    Yes, I would like all 3 of the "Dog" columns imported into the spreadsheet side-by-side as they are in the samples.

    Cheers.
     
  10. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    OK, here is some code that I produced to do a similar type of job, except this one uses browsing to find & open the Folder and searches through any worksheets in the workbooks to find the data.

    Sub ListFiles()

    Dim Coll_Docs As New Collection, Search_path, Search_Filter, Search_Fullname As String
    Dim DocName As String, i As Long, directory As String, ws1 As Worksheet, lastrow2 As Long
    Dim name As String, wb As Workbook, ws As Worksheet, lastrow As Long, count As Integer
    Dim monthcount As Integer, bookname As String, sheetname As String, filecount As Integer
    Dim ws0 As Worksheet
    Msg = "Select a location containing the files you want to list."
    Set ws1 = Sheets("Sheet1")
    Set ws0 = Sheets("Sheet2")
    directory = GetDirectory(Msg)
    If directory = "" Then Exit Sub
    ' Insert headers
    r = 1
    'Cells.ClearContents
    ws1.Cells(r, 1) = "FileName"
    r = r + 1
    Search_path = directory ' where ?
    Search_Filter = "*.xls" ' what ?
    Set Coll_Docs = Nothing
    DocName = Dir(Search_path & "\" & Search_Filter)
    Do Until DocName = "" ' build the collection
    Coll_Docs.Add Item:=DocName
    DocName = Dir
    Loop
    With ws
    For i = 1 To Coll_Docs.count '
    Search_Fullname = Search_path & "\" & Coll_Docs(i)
    ws1.Cells(r, 1) = Search_Fullname
    r = r + 1
    Next
    End With
    lastrow2 = ws1.Cells(Rows.count, 1).End(xlUp).Row
    If lastrow2 = 1 Then Exit Sub
    For filecount = 2 To lastrow2
    lastrow = Cells(Rows.count, 1).End(xlUp).Row + 2
    name = ws1.Cells(filecount, 1)
    For count = Len(name) To 1 Step -1
    If Mid(name, count, 1) = "\" Then
    bookname = Right(name, Len(name) - count)
    Exit For
    End If
    Next count
    Workbooks.Open Filename:=name
    Set wb = Workbooks(bookname)
    For monthcount = 1 To 12
    sheetname = ws0.Cells(5, 1 + monthcount)
    Set ws = wb.Sheets(sheetname)
    ws0.Cells(lastrow, 1) = ws.Cells(3, 3)
    If ws.Cells(20, 10) = "Y" And ws.Cells(23, 10) = "Y" Then ws0.Cells(lastrow, 1 + monthcount).Interior.ColorIndex = 45 'orange
    If ws.Cells(20, 10) = "N" Or ws.Cells(23, 10) = "N" Then ws0.Cells(lastrow, 1 + monthcount).Interior.ColorIndex = 3 'Red
    If ws.Cells(21, 9) >= ws.Cells(19, 3) * 1.1 And ws.Cells(24, 9) >= ws.Cells(22, 3) * 1.1 Then ws0.Cells(lastrow, 1 + monthcount).Interior.ColorIndex = 4 'green
    If ws.Cells(19, 3) = 0 And ws.Cells(22, 9) = 0 Then ws0.Cells(lastrow, 1 + monthcount).Interior.ColorIndex = -4142 'white
    If ws.Cells(19, 3) > 0 And ws.Cells(22, 9) = 0 Then ws0.Cells(lastrow, 1 + monthcount).Interior.ColorIndex = -4142
    Next monthcount
    wb.Close (False)
    Next filecount

    End Sub

    What is the location/name of your Folder?

    By the way Access is a better method of data storage for the sort of job you are doing.
     
  11. ccrossing

    ccrossing Thread Starter

    Joined:
    Sep 2, 2011
    Messages:
    10
    The folder I am working off is simply the root folder of a USB flash drive (I:\ in this case), but if it is easier, I can drag them to a folder on the hard drive.

    I have heard that Access is superior for this type of thing, but I was handed a bunch of Excel files to work with.
     
  12. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    They can be directly imported in to Access and then worked on from there, the data entry in the first place should be in Access instead of Excel as it has superior input controls and can be shared by all the users.
    I will test it it on drive C and then change it to drive I.
    It will take me a little while to modify the code.

    So do you want a worksheet for each "Heading name"?

    I can do it for you in Access 2003 if you like, but it would obviously take much longer.
     
  13. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    There is another alternative and that is to import all the worksheets in to one workbook and work with from there.
     
  14. ccrossing

    ccrossing Thread Starter

    Joined:
    Sep 2, 2011
    Messages:
    10
    I don't think anyone in our research lab knows how to use Access at this point (I have only used it a handful of times), so if you already have VBA code for Excel then that is probably the way to go. I promise to look further into switching things over to Access in the future.

    Yes, ideally each "Heading name" (if by this you mean the term we are searching for) would have its own spreadsheet. I am happy to change the search term in the VBA and just iterate the code once per each excel workbook

    Thank you very much for the help so far. I don't want to take up too much of your time, so please don't feel rushed.
     
  15. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Don't worry about my "time" this is what I do for a hobby.
    Should the data include where it comes from, for instance if you have a "Dog" worksheet do you need know which data comes from which original worksheet?
    As I am not sure what the layout should look like.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1015610