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
Sandra SmithRetiredAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
<What I tried did not work.>
Can you be a bit more descriptive?

You did not state what did or did not happen or what the results were (if any)

If all you want to do is list PDF files in a folder then use code like this:

Dim strFileName As String
Dim objFSO As Object
Dim objFile As File
Dim strRowSource As String

Set objFSO = CreateObject("Scripting.FileSystemObject")

strFileName = Dir("C:\YourFolder\*.pdf")

    Do While strFileName <> ""
        Set objFile = objFSO.GetFile("C:\YourFolder\" & strFileName)
        strRowSource = strRowSource & "," & strFileName
        strFileName = Dir
    Loop
    Me.Listbox1.RowSource = strRowSource
    Me.Listbox1.Requery


You must add a reference in your VBA Editor to the Microsoft Scripting Runtime Library.
And set the RowSourceType property of your listbox to : Value list.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
There are other ways to do this, but this works fine for me:
Database123.mdb
0
Sandra SmithRetiredAuthor Commented:
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
0
Sandra SmithRetiredAuthor Commented:
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
0
Jeffrey CoachmanMIS LiasonCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.