[ccrossing]

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.

[DataBase]

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.

[ccrossing]

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.

[DataBase]

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.

[OBP]

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?

[ccrossing]

Originally Posted by OBP

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?

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.

[OBP]

What about the Folder, do you want it hard coded, or browsable?

[OBP]

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?

[ccrossing]

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.

[OBP]

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.

[ccrossing]

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.

[OBP]

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.

[OBP]

There is another alternative and that is to import all the worksheets in to one workbook and work with from there.

[ccrossing]

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.

[OBP]

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.