Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

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


(!)

ccrossing's Avatar
ccrossing ccrossing is offline
Junior Member with 10 posts.
THREAD STARTER
 
Join Date: Sep 2011
Location: Ontario, Canada
Experience: Good @ Excel, bad at VBA
02-Sep-2011, 10:15 AM #1
Solved: Macro to Search All .xlsx in Folder and Return Column
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
File Type: xlsx PrecipitationSAMPLE.xlsx (9.3 KB, 97 views)
File Type: xlsx TemperatureSAMPLE.xlsx (8.8 KB, 77 views)
DataBase's Avatar
DataBase DataBase is offline
Computer Specs
Member with 456 posts.
 
Join Date: Jan 2004
Experience: Intermediate
02-Sep-2011, 10:22 AM #2
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's Avatar
ccrossing ccrossing is offline
Junior Member with 10 posts.
THREAD STARTER
 
Join Date: Sep 2011
Location: Ontario, Canada
Experience: Good @ Excel, bad at VBA
02-Sep-2011, 10:27 AM #3
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.

Last edited by ccrossing; 02-Sep-2011 at 10:44 AM..
DataBase's Avatar
DataBase DataBase is offline
Computer Specs
Member with 456 posts.
 
Join Date: Jan 2004
Experience: Intermediate
02-Sep-2011, 10:48 AM #4
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
02-Sep-2011, 10:58 AM #5
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?
__________________
OBP
I do not give up easily
ccrossing's Avatar
ccrossing ccrossing is offline
Junior Member with 10 posts.
THREAD STARTER
 
Join Date: Sep 2011
Location: Ontario, Canada
Experience: Good @ Excel, bad at VBA
02-Sep-2011, 11:20 AM #6
Quote:
Originally Posted by OBP View Post
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.
Attached Files
File Type: xls TemperatureSAMPLE.xls (24.5 KB, 70 views)
File Type: xls PrecipitationSAMPLE.xls (27.0 KB, 59 views)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
02-Sep-2011, 11:23 AM #7
What about the Folder, do you want it hard coded, or browsable?
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
02-Sep-2011, 11:25 AM #8
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's Avatar
ccrossing ccrossing is offline
Junior Member with 10 posts.
THREAD STARTER
 
Join Date: Sep 2011
Location: Ontario, Canada
Experience: Good @ Excel, bad at VBA
02-Sep-2011, 11:32 AM #9
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
02-Sep-2011, 11:36 AM #10
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's Avatar
ccrossing ccrossing is offline
Junior Member with 10 posts.
THREAD STARTER
 
Join Date: Sep 2011
Location: Ontario, Canada
Experience: Good @ Excel, bad at VBA
02-Sep-2011, 11:44 AM #11
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
02-Sep-2011, 11:51 AM #12
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
02-Sep-2011, 11:53 AM #13
There is another alternative and that is to import all the worksheets in to one workbook and work with from there.
ccrossing's Avatar
ccrossing ccrossing is offline
Junior Member with 10 posts.
THREAD STARTER
 
Join Date: Sep 2011
Location: Ontario, Canada
Experience: Good @ Excel, bad at VBA
02-Sep-2011, 12:01 PM #14
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.

Last edited by ccrossing; 02-Sep-2011 at 12:06 PM..
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
02-Sep-2011, 12:12 PM #15
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.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


Tags
excel, folder, macro, vba

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑