Consulting

Results 1 to 20 of 28

Thread: Dilemma showing message box when try open file by userform

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1

    Dilemma showing message box when try open file by userform

    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.
    Last edited by Aussiebear; 06-09-2025 at 04:57 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •