Solved

Placing FSO.GetFolder output in a Report

Posted on 2004-09-29
9
670 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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
 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

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…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

770 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