VBA code looking through files

nathan663

Board Regular
Joined
Dec 3, 2008
Messages
153
Sub Hip_File_Open()
'
' Hip_File_Open Macro
fldr = "Q:\Workforce Planning and Allocation\HIP Files"
d = Dir(fldr & "*.xls") 'looks at all xls files in folder
Do While d <> "" 'd is a string containing the file name

d = Dir
Loop

End Sub


In the worksheet this macro is run from there is a list of numbers in column A. When the macro runs i need the macro to look through the file shown above looking for the number in cell A1. It will look through all the files, and if it finds the number it is looking for it will return the title of the workbook it found the number in. If it does not find the number in any of the work books it will bring back the result (not found)

What code is needed to do this?

Thanks
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
Do you mean search all cells in the workbookms foer that number, or for that number to be part of the filename?
 

nathan663

Board Regular
Joined
Dec 3, 2008
Messages
153
sorry they are actually workbooks arent they. there is only one sheet i need to look through in each work book. so to make it clearer there are around 20 workbooks in each folder, that i need to search through for a certain number each time.


can any one help?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,732
Messages
5,833,377
Members
430,206
Latest member
Sami Gaid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top