Hello,
this code should work with files are already existed in folders and sub-folders in specific device. when try open file after click from listbox on userform, but my problem if the file is already existed will show message "file not found" after open the file , shouldn't show as long the file is existed.
I'm not sure where is the problem !
Dim i As Long
Sub ListBox1_Click()
Dim FileRoot As String
Dim objFldr As Object
Dim objFSO As Object
' initialise "found" counter
i = 0
' state target WITHOUT last \!
Const FolderPath = "C:\Users\abdd\Desktop\files"
With ListBox1
MsgBox .ListIndex & ": " & .List(.ListIndex, 2)
FileRoot = .List(.ListIndex, 2)
End With
' enable use of FSO
Set objFSO = CreateObject("Scripting.FileSystemObject")
' get folder
Set objFldr = objFSO.GetFolder(FolderPath)
' pass folder and root to the function
LoopEachFolder objFldr, FileRoot
' quit FSO
Set objFSO = Nothing
' fit results and tell user
If i <> 0 Then
MsgBox "Launched " & i & " files"
Else
MsgBox "File not found for selection= " & FileRoot
End If
'return to this file
ThisWorkbook.Activate
End Sub
Function LoopEachFolder(fldFolder As Object, fRoot As String)
Dim objFldLoop As Object
' check for xls file in this folder
Fname = Dir(fldFolder & "\" & fRoot & ".xls*")
If Fname <> "" Then
' open file
Workbooks.Open Filename:=fldFolder & "\" & Fname
' increment the "found" counter
i = i + 1
End If
' check again for a pdf of that name
Fname = Dir(fldFolder & "\" & fRoot & ".pdf")
If Fname <> "" Then
' try to open pdf
ActiveWorkbook.FollowHyperlink fldFolder & "\" & Fname
' increment the "found" counter
i = i + 1
End If
' Then check within the subfolders in this folder...
For Each objFldLoop In fldFolder.subFolders
' run this function on each subfolder found
LoopEachFolder objFldLoop, fRoot
Next objFldLoop
End Function
I hope to find solution for this dilemma.