VBA Help

mgm05267

Well-known Member
Joined
Nov 11, 2011
Messages
615
Hi,
Can someone please tell me how to use a file dialog object to search for the files(whose names are present in a column say A)using VBA.
The code actually has to search for set of files in a specific directory whose filenames are present in the column A.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
What should happen when you find or don't find the files. Also I don't think the file dialog can be used to search.
 
Upvote 0
This is a basic macro to return the names of files in a directory with a For ...Each...Next loop to check against a list of files in column A.
The message box is there so you can see that the files are being returned, but it is unnecessary if you want to delete that line. That is where you would insert code to do something with the returned file name.

Code:
Sub fileSrch()
Dim sh As Worksheet, rng As Range, lr As Long, fSrch As String
Set sh = Sheets(1)
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A2:A" & lr)
myPath = ThisWorkbook.Path
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If
fSrch = Dir(myPath & "*.xls*")
Do
For Each c In rng
If LCase(c.Value) = LCase(fSrch) Then
MsgBox fSrch
End If
Next
fSrch = Dir
Loop While fSrch <> ""
End Sub
Code:
 
Upvote 0

Forum statistics

Threads
1,211,853
Messages
6,104,371
Members
447,902
Latest member
chriswebs23

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
Back
Top