VBA code to search files

yogeshtalks · Sep 17th 2011

Hi all,

I am quite new to VBA and just started using it. I am finding it very hard to get hold of the file handling concepts. Please help.

I want to search for a file inside a directory which contains many folders and sub-folders. The file that i am looking for is inside any of those folders of sub-folders. I would like to enter the file name in a msgbox or at a specific cell in a work book so that the file is searched and saved at the destination foler that i specify.

Note: the file i am looking for is not always excel workbooks. Most of them are data files which contains text in .csv format.

Thanks

jindon · Sep 17th 2011

Re: VBA code to search files

See if this helps you to get you started.

http://www.ozgrid.com/forum/sh…57939&p=574958#post574958

yogeshtalks · Sep 17th 2011

Re: VBA code to search files

Thank you very much Jindon.

The code works perfect to get the path of the file that i am looking for. Now, I will try to improvise to copy and paste the searched file to my destination folder.

Cheers

jindon · Sep 17th 2011

Re: VBA code to search files

Try this,

Code

Sub myCopy()
Dim myFolder As String, r As Range, myFile As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show Then
            myFolder = .SelectedItems(1)
        Else
            Exit Sub
        End If
    End With
For Each r In Range("a1").CurrentRegion.Resize(, 1).Cells
    myFile = r.Value & "\" & r(, 2).Value
    FileCopy myFile, myFolder & "\" & r(, 2).Value
Next
End Sub

Display More

VBA code to search files

Participate now!

Share