Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002
    Location
    Glasgow, UK
    Posts
    100

    Unhappy Unanswered: Listing Folder Contents (long post)

    I've written code that creates a folder (if it does not exist) in a specific location and names it the same as the customer id (cust_ID).

    I also then check this folder to see if there are any files in it and if so I populate a list box displaying the contents of the folder and double clicking opens the file via the docmd.hyperlink code.

    It all works fine but my problem is that is runs slowly, is there a more efficient way?

    the code is on the on_current event:

    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")

    If fso.folderexists("G:\ Exec Office Docs\" & Me.Cust_ID & "") = False Then
    fso.createfolder ("G:\ Exec Office Docs\" & Me.Cust_ID & "")
    End If


    If fso.folderexists("G:\ Exec Office Docs\" & Me.Cust_ID & "") = True Then
    Dim fs As Object
    Dim fsd1 As Object
    Dim fsd2

    Set fsd1 = CreateObject("scripting.filesystemobject")
    Set fs = Application.FileSearch

    With fs
    .lookin = "G:\ Exec Office Docs\" & Me.Cust_ID & ""
    .filename = "*"
    If .Execute > 0 Then

    Dim rst As DAO.Recordset



    Set rst = db.OpenRecordset("SELECT * FROM TBL_TEMP_Folder_Details")

    For i = 1 To .foundfiles.Count

    Set fsd2 = fsd1.getfile(.foundfiles(i))

    rst.AddNew
    rst![File_Name] = .foundfiles(i)
    rst![File_date] = fsd2.datelastmodified
    rst.Update
    Next i

    End If
    End With
    End If
    Access XP & WinXP Pro

  2. #2
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238

    Re: Listing Folder Contents (long post)

    Originally posted by xander
    I've written code that creates a folder (if it does not exist) in a specific location and names it the same as the customer id (cust_ID).

    I also then check this folder to see if there are any files in it and if so I populate a list box displaying the contents of the folder and double clicking opens the file via the docmd.hyperlink code.

    It all works fine but my problem is that is runs slowly, is there a more efficient way?

    the code is on the on_current event:

    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")

    If fso.folderexists("G:\ Exec Office Docs\" & Me.Cust_ID & "") = False Then
    fso.createfolder ("G:\ Exec Office Docs\" & Me.Cust_ID & "")
    End If


    If fso.folderexists("G:\ Exec Office Docs\" & Me.Cust_ID & "") = True Then
    Dim fs As Object
    Dim fsd1 As Object
    Dim fsd2

    Set fsd1 = CreateObject("scripting.filesystemobject")
    Set fs = Application.FileSearch

    With fs
    .lookin = "G:\ Exec Office Docs\" & Me.Cust_ID & ""
    .filename = "*"
    If .Execute > 0 Then

    Dim rst As DAO.Recordset



    Set rst = db.OpenRecordset("SELECT * FROM TBL_TEMP_Folder_Details")

    For i = 1 To .foundfiles.Count

    Set fsd2 = fsd1.getfile(.foundfiles(i))

    rst.AddNew
    rst![File_Name] = .foundfiles(i)
    rst![File_date] = fsd2.datelastmodified
    rst.Update
    Next i

    End If
    End With
    End If
    The file search method can be slow, especially if there are a lot of files in the folder. It can be quicker to use a For...Each...Next loop using the Windows scripting object. You can use code such as:

    Private mfsoSysObj As Scripting.FileSystemObject

    Private Function FindFiles() As Boolean
    On Error Goto ErrorHere

    For Each fle In mfsoSysObj.GetFolder(MyFolderName).Files
    ' Do something with each file found
    Next fle
    FindFiles = True
    ExitHere:
    Exit Function
    ErrorHere:
    MsgBox "Error In: Form '" & Me.Name & "'" & vbCrLf _
    & "Procedure: FindFiles" & _
    vbCrLf & "Error Code: " & Err.Number & _
    vbCrLf & "Error: " & Err.Description, vbExclamation, "Error Alert"
    FindFiles = False
    Resume ExitHere
    End Function

  3. #3
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Listing Folder Contents (long post)

    Originally posted by xander
    I've written code that creates a folder (if it does not exist) in a specific location and names it the same as the customer id (cust_ID).

    I also then check this folder to see if there are any files in it and if so I populate a list box displaying the contents of the folder and double clicking opens the file via the docmd.hyperlink code.

    It all works fine but my problem is that is runs slowly, is there a more efficient way?

    the code is on the on_current event:

    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")

    If fso.folderexists("G:\ Exec Office Docs\" & Me.Cust_ID & "") = False Then
    fso.createfolder ("G:\ Exec Office Docs\" & Me.Cust_ID & "")
    End If


    If fso.folderexists("G:\ Exec Office Docs\" & Me.Cust_ID & "") = True Then
    Dim fs As Object
    Dim fsd1 As Object
    Dim fsd2

    Set fsd1 = CreateObject("scripting.filesystemobject")
    Set fs = Application.FileSearch

    With fs
    .lookin = "G:\ Exec Office Docs\" & Me.Cust_ID & ""
    .filename = "*"
    If .Execute > 0 Then

    Dim rst As DAO.Recordset



    Set rst = db.OpenRecordset("SELECT * FROM TBL_TEMP_Folder_Details")

    For i = 1 To .foundfiles.Count

    Set fsd2 = fsd1.getfile(.foundfiles(i))

    rst.AddNew
    rst![File_Name] = .foundfiles(i)
    rst![File_date] = fsd2.datelastmodified
    rst.Update
    Next i

    End If
    End With
    End If
    You can also use the files collection of the folder object to list the files and populate a listbox.

    Dim fso As FileSystemObject, fldr As Folder, fl As File

    Set fso = New FileSystemObject
    Set fldr = fso.GetFolder(CurrentProject.Path)
    For Each fl In fldr.Files
    Do something with fl.name
    Next

    Gregg

Posting Permissions

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