object required error when loop within subfolders in selected row from listbox on userform

Mussala

Board Regular
Joined
Sep 28, 2022
Messages
61
Office Version
  1. 2019
Platform
  1. Windows
Hi
this is the original code
VBA Code:
Private Declare Function [B]apiShellExecute[/B] Lib "shell32.dll" _
 Alias "ShellExecuteA" _
(ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _
ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

VBA Code:
Private Sub ListBox1_Click()
   [B] Dim Filename As String[/B]

    With ListBox1
        'change path to suit
        Filename = "C:\Users\MU\Desktop\pdf Folder\" &[B] .List(.ListIndex, 1)[/B] & ".pdf"
        ' Test if file exists...
        If Dir(Filename) <> vbNullString Then
            '... if so, write column 2 value to text box
            TextBox1 = .List(.ListIndex, 1)
            ' and open pdf
            [B]apiShellExecute[/B] 0, "Open", Filename, "", "", vbMaximizedFocus
            Else
            ' otherwise tell user and stop
            MsgBox "Sorry but the following file was not found:" & vbCr & Filename
            Exit Sub
        End If
    End With


    'ListBox1.ListIndex = -1
End Sub
the code will open pdf file by click selected row from listbox , but my problem the code doesn't deal with files are existed in subfolders within the main folder in the main directory
I try with this
VBA Code:
Private Sub ListBox1_Click()
    Dim Filename As String
    Set objFso = CreateObject("Scripting.FileSystemObject")

    With ListBox1
        'change path to suit
        Set ObjFolder = "C:\Users\MU\Desktop\pdf Folder\" & .List(.ListIndex, 1) & ".pdf"
        For Each objSubFile In ObjFolder.Files
        If InStr(objSubFile.Name, ".pdf") > 0 Then
            Debug.Print objSubFile.Path
        End If
    Next objSubFile
    ' call the function to loop through its subfolders (and theirs)
    LoopEachFolder ObjFolder

    ' quit FSO
    Set objFso = Nothing
        ' Test if file exists...
        If Dir(ObjFolder) <> vbNullString Then
            '... if so, write column 2 value to text box
            TextBox1 = .List(.ListIndex, 1)
            ' and open pdf
            apiShellExecute 0, "Open", ObjFolder, "", "", vbMaximizedFocus
            Else
            ' otherwise tell user and stop
            MsgBox "Sorry but the following file was not found:" & vbCr & Filename
            Exit Sub
        End If
    End With
    
    
    'ListBox1.ListIndex = -1
End Sub

Function LoopEachFolder(fldFolder As Object)

    ' With the subfolders in this folder....
    For Each objFldLoop In fldFolder.subFolders
        ' ...loop through files in this folder
        For Each objSubFile In objFldLoop.Files
            If InStr(objSubFile.Name, ".pdf") > 0 Then
                Debug.Print objSubFile.Path
            End If
        Next objSubFile
        ' ... and run the function on each subfolder found
        LoopEachFolder objFldLoop

    Next objFldLoop

End Function
but gives error object required in this line
VBA Code:
Set ObjFolder = "C:\Users\MU\Desktop\pdf Folder\" & .List(.ListIndex, 1) & ".pdf"
how can I make this code deals with folders & subfolders when I try open PDF file ?
thanks
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Forum statistics

Threads
1,215,069
Messages
6,122,959
Members
449,096
Latest member
Anshu121

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