Solved

Placing FSO.GetFolder output in a Report

Posted on 2004-09-29
9
668 Views
Last Modified: 2012-05-05
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
Comment
Question by:jwah
  • 5
  • 3
9 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12178471
Easiest way would be to write the files into a table and create a subreport that lists all of the table's contents.
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12179217
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
 

Author Comment

by:jwah
ID: 12186952
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
 

Author Comment

by:jwah
ID: 12186970
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:jwah
ID: 12186980
Ignore Point 1 I needed to expand the text box !!!
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12188674
cool, so it is all working now??

Alan
0
 

Author Comment

by:jwah
ID: 12189223
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
 
LVL 26

Accepted Solution

by:
Alan Warren earned 125 total points
ID: 12192794
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
 

Author Comment

by:jwah
ID: 12196589
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now