Search and open a file that can be pdf,word,jpeg etc

[maverick_b]

Hi,

I am a newbie with macros and trying to learn but it is not easy . What I need to do should be easy but I have no clue how to achieve it.

We have lots of files (pdf,doc,docx,xls,xlsx,jpeg,png), in folders and subfolders. There are many subfolders in the main folder. It is sometimes hard to find the file we need as we need to click many times until we get to the folder we want and open the file.

We are keeping a log of the file names in excel sheet. We need to enter the name of the file we want to open in the inputbox in excel sheet and the code will search for the file name typed in the main folder and subfolders and open it. That is all we need.

Any help regarding this problem is extremely highly appreciated. Thank you

 

[Michael M]

Please do not cross post. Or at least advise you have done so, so that posters here won't be doubling up on responses

http://www.excelforum.com/excel-programming/779403-search-and-open-a-file-that-can-be-pdf-word-jpeg-etc.html

 

[Cindy Ellis]

The following isn't a specific solution...but it's what I was working on in between our questions and answers.
Run this on an Empty Worksheet. It will create a list of all of the files in the directory and sub-directories, with just the filename in column B, and a hyperlink in column A. If you use auto-filter on column B, you can select the file from the list, then click the hyperlink. This will be easier (I think) than trying to tell a macro what program should open each file, since you've mentioned there can be several different filetypes.

Private Sub recurseSubFolders(ByRef Folder As Object, _
    ByRef strArr() As String, _
    ByRef i As Long)
Dim SubFolder As Object
Dim strName As String
Dim strFileOnly As String
Dim j As Long
For Each SubFolder In Folder.Subfolders
    Let strName = Dir$(SubFolder.Path & "\*.*")
    Do While strName <> vbNullString
        Let i = i + 1
        Let strArr(i, 1) = SubFolder.Path & "\" & strName
        For j = Len(strArr(i, 1)) To 1 Step -1
        If Mid(strArr(i, 1), j, 1) = "\" Then Exit For
        Next j
        strFileOnly = Mid(strArr(i, 1), j + 1)
        Let strArr(i, 2) = strFileOnly
 
        Let strName = Dir$()
    Loop
    Call recurseSubFolders(SubFolder, strArr(), i)
Next
End Sub
Sub make_hyperlinked_file_list()
Dim FSO As Object
Dim strName As String
Dim strFileOnly As String
Dim strArr(1 To 65536, 1 To 2) As String, i As Long, j As Long
Dim LR As Long, Rw As Long
[B][COLOR=red]Const strDir As String = "C:\My Documents\xxxx" 'put the proper path here[/COLOR][/B]
Let strName = Dir$(strDir & "\*.*")
Do While strName <> vbNullString
    Let i = i + 1
    Let strArr(i, 1) = strDir & "\" & strName
 
    For j = Len(strArr(i, 1)) To 1 Step -1
    If Mid(strArr(i, 1), j, 1) = "\" Then Exit For
    Next j
    strFileOnly = Mid(strArr(i, 1), j + 1)
    Let strArr(i, 2) = strFileOnly
    Let strName = Dir$()
Loop
Set FSO = CreateObject("Scripting.FileSystemObject")
Call recurseSubFolders(FSO.GetFolder(strDir), strArr(), i)
Set FSO = Nothing
If i > 0 Then
    Range("A2").Resize(i, 2) = strArr
End If
LR = Range("A" & Rows.Count).End(xlUp).Row
For Rw = 2 To LR
    Range("A" & Rw).Hyperlinks.Add Range("A" & Rw), Range("A" & Rw).Text
 
Next Rw
End Sub

I'll work on a more specific solution...but no guarantees given the various filetypes. It will be some trial and error.

This code is adapted from Nate O's recursive file search, and some other code on the forum for extracting the filename only from the path.

Hope this helps,
Cindy