Tech Support Guy banner
Status
Not open for further replies.

vlookup in all .xlsm in folder

Solved 
4K views 13 replies 2 participants last post by  Rollin_Again 
#1 ·
I would like to be able to search for a particular string through vlookup in all excel workbooks (xlsm) in a folder and return the value 7 columns over. Does anyone know if this is possible?

thanx
 
#2 · (Edited)
Yes it's possible using a VBA macro. Do all the files to be searched have similar format/layout? Will the search range of cells always be in the same column? Do you just want to write the found values to a new workbook or are you actually wanting to create/write the actual VLOOKUP formulas to the workbook?

Run the macro in the attached workbook to see if it does what you want. It will read the values from the remote directory workbooks and write them to the active sheet without actually using any formulas.
 

Attachments

#3 ·
Yes the search cell will always be in the same column.
when I run your macro it asks me for the string and path but doesn't produce anything. I have made sure the path and search criteria are right and run it a few times.
what I am trying to do is keep a running total of time on projects. so I have a folder with all my timesheets in it and I would like to be able to enter a project number in cell A1 and it would find all cases of that number in all the spreadsheets in that folder and add them up and put the sum in cell A2. then the same in cell B1, B2 and C1,C2 etc...

thanx Rollin_Again
 
#5 ·
Here is the timesheet (see sheet1). There will be 24 of these in a folder. January - December timesheets. I would like to build a separate spreadsheet in this folder where i can enter the project number (e.g. 6576, 6580 etc...) in column A (see sheet2) and i would find all 6576 in all timesheeets in this folder and grab the totals from column 7 and add them up in column B. Hope this is understandable.

thanks for you help
 

Attachments

#7 ·
yes it is definitely xlsm. I have put lookup.xlsm in my timesheet folder where it is supposed to search the spreasheets and have run the macro. When the screen message comes up i type 06576 and when the second screen message comes up i type "C:\Users\tertom01\Desktop\2016" where my folder is located and i get nothing obvious that comes up. I have made sure my macro security is turn off. Maybe Im not sure what the outcome should be. Can you send me a screenshot of the results. sorry for the hassel.

thanks much for you help
 
#10 ·
When I run the macro from my workbook it performs a lookup on the workbooks in the remote directory and then writes the summary to the workbook where the macro is being run from (column A will show you the remote workbook name, Column B shows the cell address where the value was found, Column C shows the value that was found)
 

Attachments

#12 ·
Yes it will loop through all the workbooks in that directory and write a separate line for each instance found in all the workbooks.

Try opening the macro in the VB editor and step through the code line by line by pressing F8 key and see if the source workbooks open at some point.
 
#13 ·
I figured out the problem. I had the lookup.xlsm in the same folder. It all looks good now but there is just one thing. It opens the spread sheets but it asks if i want to save them every time. is there away of tell it to Open, Close and dont save?

Thanks for all you help
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top