Link to home
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
ASKER CERTIFIED SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
There are other ways to do this, but this works fine for me:
Database123.mdb
Avatar of Sandra Smith

ASKER

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
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
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