Avatar of Sandra Smith
Sandra SmithFlag for United States of America asked on

List of PDF files in an ACCESS 2010 listbox

How can I change the below to list only pdf?  What I tried did not work.

Sandra


Private Sub PopulateFileList()
'Populates the Files listing with files in the directory
    Dim strfileList() As String
    Dim strName As String
    Dim strPath As String
    Dim i As Integer
     'define the directory to be searched for files
    strPath = txtScanDirectory
    If Right(strPath, 1) <> "\" Then strPath = strPath & "\"

     'build a list of the files
    strName = Dir(strPath & "*.pdf")
   
    While strName <> ""
         'add fName to the list
        i = i + 1
        ReDim Preserve strfileList(1 To i)
        strfileList(i) = strName
         'get next filename
        strName = Dir()
    Wend
     'see if any files were found
    If i = 0 Then
        MsgBox "No files found"
        Exit Sub
    End If
     'cycle through the list and add to listbox
    For i = 1 To UBound(strfileList)
        Me.lstFiles.AddItem strfileList(i)
    Next
End Sub
Microsoft DevelopmentMicrosoft ApplicationsMicrosoft Access

Avatar of undefined
Last Comment
Jeffrey Coachman

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Jeffrey Coachman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jeffrey Coachman

There are other ways to do this, but this works fine for me:
Database123.mdb
ASKER
Sandra Smith

This is my version below.  It worked once and then when I closed the form and re-opened, I keep getting a file not found error, yet there is one pdf and when I step through the code, there is a value for the strFileName variable

Private Sub PopulateFileList()
'Populates the Files listing with files in the directory
On Error GoTo ErrorHandler
Dim strPath As String
Dim strFileName As String
Dim objFSO As Object
Dim objFile As File
Dim strRowSource As String

'Define the directory to be searched for files and clean up if necessary to add the \
    strPath = txtScanDirectory
    If Right(strPath, 1) <> "\" Then strPath = strPath & "\"

Set objFSO = CreateObject("Scripting.FileSystemObject")

strFileName = Dir(strPath & "Appr_" & "*.pdf")

    Do While strFileName <> ""
        Set objFile = objFSO.GetFile(strFileName)  ERRORS OUT HERE
        strRowSource = strRowSource & "," & strFileName
        strFileName = Dir
    Loop
    Me.lstFiles.RowSource = strRowSource
    Me.lstFiles.Requery

Exit_ErrorHandler:
    Exit Sub
ErrorHandler:
    MsgBox "Error Number: " & Err.Number & vbCrLf & _
           "Description: " & Err.Description & vbCrLf & _
           "Procedure: PopulateFileList, form: frmAttachDocument", vbOKOnly
    Resume Exit_ErrorHandler
End Sub
ASKER
Sandra Smith

Found my problem and got this to work.  I have taken out the reference to the path in the Set objFile.  Belwo works fine now. (I hope!)
Private Sub PopulateFileList()
    Me.lstFiles.Requery
'Populates the Files listing with files in the directory
On Error GoTo ErrorHandler
Dim strPath As String
Dim strFileName As String
Dim objFSO As Object
Dim objFile As File
Dim strRowSource As String

'Define the directory to be searched for files and clean up if necessary to add the \
    strPath = txtScanDirectory
    If Right(strPath, 1) <> "\" Then strPath = strPath & "\"

Set objFSO = CreateObject("Scripting.FileSystemObject")

strFileName = Dir(strPath & "Appr_" & "*.pdf")

    Do While strFileName <> ""
        Set objFile = objFSO.GetFile(strPath & strFileName)
        strRowSource = strRowSource & "," & strFileName
        strFileName = Dir
    Loop
    Me.lstFiles.RowSource = strRowSource
    Me.lstFiles.Requery

Exit_ErrorHandler:
    Exit Sub
ErrorHandler:
    MsgBox "Error Number: " & Err.Number & vbCrLf & _
           "Description: " & Err.Description & vbCrLf & _
           "Procedure: PopulateFileList, form: frmAttachDocument", vbOKOnly
    Resume Exit_ErrorHandler
End Sub
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Jeffrey Coachman

OK, as I stated there are a few different ways to do this.
(One does not even need the Scripting Runtime, however if you start to need subfolders, then it would be beneficial)

Glad I could help.

;-)

JeffCoachman