Solved

Placing FSO.GetFolder output in a Report

Posted on 2004-09-29
9
669 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

920 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

14 Experts available now in Live!

Get 1:1 Help Now