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


(!)

OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,610 posts.
 
Join Date: Mar 2005
Location: UK
02-Sep-2011, 12:21 PM #16
OK, lets do it a step at a time.
I want you to test this code which should load all your Folder names in to Sheet2 and then open them one at a time, at this stage it doesn't actually do anything with them.
You may have to convert this to an xlsx workbook and enable macros etc.
Attached Files
File Type: xls New Get file data..xls (58.0 KB, 53 views)
__________________
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, 12:35 PM #17
It does not need to say where it comes from as it is already labeled in the column (I did not show this on the .xls files I uploaded). So ideally it would look something like this attachment. The order that the files are searched, and thus the order they show up in the spreadsheet is not important.
Attached Files
File Type: xls layout.xls (27.5 KB, 43 views)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,610 posts.
 
Join Date: Mar 2005
Location: UK
02-Sep-2011, 12:47 PM #18
Have you tested the code in the Workbook I posted, to run the code click on the Sheet2 "Get Files" button?
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, 01:25 PM #19
Sorry for the delay--I had to run to a meeting. I ran it once but it hit a Runtime error when it tried to open "~Temperature.xlsx" which is an autobackup file I think.

Running again...I will keep you posted.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,610 posts.
 
Join Date: Mar 2005
Location: UK
02-Sep-2011, 01:52 PM #20
OK, if you can get that to work, this version does all the rest.
It opens the files and transfers the data.
What you need to do is ensure that the Worksheet names match the items that you are going to search for ie. dog fish etc.
You also need to ensure that the headings in column A match your actual values.
It works fine with the 2 workbooks that you provided. It is just a case of getting it set up correctly for your actual data.

It runs pretty quckly, so if yours isn't it probably has a problem.
Attached Files
File Type: xls New Get file data..xls (73.5 KB, 69 views)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,610 posts.
 
Join Date: Mar 2005
Location: UK
02-Sep-2011, 01:53 PM #21
If really necessary you could email some real data to set it up with.
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, 02:01 PM #22
It seems to have run well. It ran quickly considering the amount of data it has to churn through. I am going to run the newer version you uploaded and see how it goes.
Attached Files
File Type: xls Output after running.xls (56.5 KB, 52 views)
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
06-Sep-2011, 12:32 PM #23
Ok, so I finished making sure my spreadsheets were in the exact format the macro required and...everything worked brilliantly!

Thank you very much for your help and I am deeply indebted to you. This script literally saved me from days of copying and pasting and any accompanying errors.
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 ↑