• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 687
  • Last Modified:

Placing FSO.GetFolder output in a Report

I have a Access 2000 app and have a form which lists the contents of several folders like below

Set folderObject = FSO.GetFolder(RefFolder)
    Set filesObject = folderObject.Files
    For Each File In filesObject
        Me.DocumentList = Me.DocumentList & File.Name & "   " & File.DateCreated & vbNewLine
    Next

I want to (also) get this listing into a Report for printing. What is the correct approach here? Should I do it all within a query? I would like the various FILE parameters as seperate fields ideally.

jwah
0
jwah
Asked:
jwah
  • 5
  • 3
1 Solution
 
shanesuebsahakarnCommented:
Easiest way would be to write the files into a table and create a subreport that lists all of the table's contents.
0
 
Alan WarrenCommented:
Hi jwah,

Create a new module and add this function:

Function getFileList() as String
    ' I assume your FSO object is declared and instantiated in previous code lines
   

   Dim strTemp as String
   
    Set folderObject = FSO.GetFolder(RefFolder)
    Set filesObject = folderObject.Files
    For Each File In filesObject
        strTemp  = strTemp  & File.Name & "   " & File.DateCreated & vbNewLine
    Next
   
   getFileList = strTemp

End Function


then in your report you can set the controlsource of a control to = getFileList ()

If that doesnt work  then use the On_Format event of the detail section to assign the result of the function getFileList to a control in the detail section.

Need more info??

Alan
0
 
jwahAuthor Commented:
Thank you. Right, I have this partly working. Sorry and a coupel of more things......  I have

    Dim FSO As Object
    Dim strTemp As String
    Dim RefFolder As String
    RefFolder = "D:\Scratch"
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set folderObject = FSO.GetFolder(RefFolder)
    Set filesObject = folderObject.Files
    For Each File In filesObject
        strTemp = strTemp & File.Name & "   " & File.DateCreated & vbNewLine
    Next
    getFileList = strTemp

1. My report only shows one record! I added a textbox which controlsource calls this function ok but only one record is shown!
2. I need RefFolder to For...Loop through a number of values from a table. Given it is a string how is this best done
3. I need to shown sub folders as well

jwah
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
jwahAuthor Commented:
Thank you. Right, I have this partly working. Sorry and a couple of more things......  I have:-

    Dim FSO As Object
    Dim strTemp As String
    Dim RefFolder As String
    RefFolder = "D:\Scratch"
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set folderObject = FSO.GetFolder(RefFolder)
    Set filesObject = folderObject.Files
    For Each File In filesObject
        strTemp = strTemp & File.Name & "   " & File.DateCreated & vbNewLine
    Next
    getFileList = strTemp

1. My report only shows one record! I added a textbox which controlsource calls this function ok but only one record is shown!
2. I need RefFolder to For...Next around through a number of values from a table I'll SELECT on later. Given it is a string how is this best done
3. I need to show sub folders as well

jwah
0
 
jwahAuthor Commented:
Ignore Point 1 I needed to expand the text box !!!
0
 
Alan WarrenCommented:
cool, so it is all working now??

Alan
0
 
jwahAuthor Commented:
I have made some progress in the last few hours...
Generally the answer is yes but it is not reliable and hard to find what is wrong. I am getting odd results where it seems to be cycling around the same folder twice and thus showing repetition in the report.

I have a seperate function which does a SQL in a While not rst.EOF loop and calls getFileList( ) many times. However the value I pass is always unique and has a folder however I sometimes get the same folders and files repeated in the report.

Here is my code

Function getFileList(Filename) As String
   
    Dim fso
    Dim folder
    Dim f
    Dim Files
    Dim File
    Dim SubFolders
    Dim SubFolder
    ' fCount is a public var declared outside a function
         
    On Error Resume Next
    If (Dir$(Filename, vbDirectory) = "") Then Exit Function
   
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set folder = fso.GetFolder(Filename)
   
    Set SubFolders = folder.SubFolders
    If SubFolders.Count <> 0 Then
        For Each SubFolder In SubFolders
            strTemp = strTemp & vbNewLine & SubFolder & vbNewLine
            fCount = 0
            getFileList (SubFolder)
        Next
    End If
   
    Set Files = folder.Files
    If Files.Count <> 0 Then
        For Each File In Files
            fCount = fCount + 1
            strTemp = strTemp & "     " & fCount & ". " & PadRight(File.Name, 45)
            strTemp = strTemp & File.DateCreated & vbNewLine
        Next
    End If
   
    getFileList = strTemp

End Function

' I note this function calls itself but seems to be ok as I guess it assumes a new instance of itself.


and here is my calling function part thereof

' decl above here.............

    Set db = CurrentDb
    Set qdef = db.CreateQueryDef("")
    qdef.SQL = "select Code FROM HeadLeases"
    Set rst = qdef.OpenRecordset()
   
    If MainFolder = "" Then
        MsgBox "MainFolder not set"
    Else
       While Not rst.EOF
            LastContents = getFileList(MainFolder & "\" & rst!Code)
            ' MsgBox LastContents
            ' Append text
            CompleteList = CompleteList & LastContents
            LastContents = ""
            rst.MoveNext
   
        Wend
    End If
   
    ListFolderContents = CompleteList

I may have to run it from a form and debug it from there....
0
 
Alan WarrenCommented:
Hi jwah,


Try this see how goes.
==============
Module1
==============

Option Compare Database
Option Explicit

Dim strFileList  As String                       ' string variable used to list files in given path


Function mGetCompleteFileList(sPath As String) As String
  ' Usage:
  ' ?mGetCompleteFileList("C:\Documents and Settings\Alan\My Documents\My Pictures\Als")
 
  mGetFileList sPath, True
  mGetCompleteFileList = strFileList
End Function


Function mGetFileList(ByRef sPath As String, bClear As Boolean) As Variant

  ' ==========================================
  ' Procedure:          mGetFileList
  ' Purpose:
  ' Recursively interrogate path provided and
  ' populate a module level string with fullpath
  ' of all files in path and all sub-folders.
  '
  ' Expects a path like:
  ' C:\Documents and Settings\Alan\My Documents\My Pictures\Als
  ' ==========================================
  ' ==========================================
 
  If bClear Then: strFileList = ""
 
  Dim fso As Scripting.FileSystemObject
  Dim fldr As Scripting.Folder
  Dim f As Scripting.File
  Dim fls As Scripting.Files
  Dim subFldrs As Scripting.Folders
  Dim subFldr As Scripting.Folder
 
 
  If (Dir$(sPath, vbDirectory) = "") Then Exit Function
 
  Set fso = New Scripting.FileSystemObject
  Set fldr = fso.GetFolder(sPath)
  Set subFldrs = fldr.SubFolders
 
  If subFldrs.Count <> 0 Then
    For Each subFldr In subFldrs
      mGetFileList subFldr.Path, False
    Next
  End If
 
  Set fls = fldr.Files
  If fls.Count <> 0 Then
    For Each f In fls
      strFileList = strFileList & f.DateCreated & "   " & f & vbCrLf
    Next
  End If
   
End Function

Alan

===== End Module1 ========
0
 
jwahAuthor Commented:
thanks,
Actually I did not have Microsoft Runtime library added which resulted in "not defined" compile on Dim fso As Scripting.FileSystemObject etc

Yes it all working well. thanks
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now