Results 1 to 14 of 14
  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    File System Object (VBA/Word-Excel/2003)

    Perhaps it's an overactive imagination, but I am certain that I have seen a series of fso commands that will delete all instances of a file (filespec) within a folder, its subfolders and subsubfolders, etc. I have been unable reconstruct or locate this information.

    Any help will be most gratefully received.

    T.I.A.
    Regards
    Don

  2. #2
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: File System Object (VBA/Word-Excel/2003)

    See <post#=110871>post 110871</post#> -- or, alternatively, <post#=319305>post 319305</post#> (which uses Dir).

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: File System Object (VBA/Word-Excel/2003)

    Thanks Steve.
    When I attempt to compile the code from <!post=post 110871,110871>post 110871<!/post> in Word 2003 I get a "Sub or Function not defined" error flagged at line 40 (see below).

    >> 'Microsoft Scripting Runtime - scrrun.dll
    I have set a reference to Microsoft Scripting Runtime; I don't understand the " - scrrun.dll" segment of that comment.

    <div style="width: 100%; background-color: #FFFFFF;"><code><font color=black><font color=blue>Public</font color=blue> <font color=blue>Function</font color=blue> GetFilesIncludingSubFolders(StartFolder <font color=blue>As</font color=blue> Folder, _
    SearchSubFolders <font color=blue>As</font color=blue> <font color=blue>Boolean</font color=blue>) <font color=blue>As</font color=blue> <font color=blue>String</font color=blue>
    <font color=blue>Dim</font color=blue> fld <font color=blue>As</font color=blue> Folder
    <font color=blue>Dim</font color=blue> fil <font color=blue>As</font color=blue> File
    <font color=blue>Dim</font color=blue> sFolderList <font color=blue>As</font color=blue> <font color=blue>String</font color=blue>

    <font color=448800>'Uses recursion to get files in subfolders</font color=448800>
    10 <font color=blue>If</font color=blue> SearchSubFolders <font color=blue>Then</font color=blue>
    20 <font color=blue>If</font color=blue> StartFolder.SubFolders.Count <font color=blue>Then</font color=blue>
    30 <font color=blue>For</font color=blue> <font color=blue>Each</font color=blue> fld <font color=blue>In</font color=blue> StartFolder.SubFolders
    40 GetFilesIncludingSubolders fld, <font color=blue>True</font color=blue> <font color=448800>'Search subs of subs</font color=448800>
    50 <font color=blue>Next</font color=blue> 'fld
    60 <font color=blue>End</font color=blue> <font color=blue>If</font color=blue>
    70 <font color=blue>End</font color=blue> <font color=blue>If</font color=blue>
    80 sFolderList = "FILES IN SUBFOLDER " & UCase(StartFolder.Path) & " ARE:" & vbCr
    90 <font color=blue>For</font color=blue> <font color=blue>Each</font color=blue> fil <font color=blue>In</font color=blue> StartFolder.Files
    100 sFolderList = sFolderList & fil.Name & vbCr
    110 <font color=blue>Next</font color=blue> <font color=448800>'fil</font color=448800>
    120 GetFilesIncludingSubFolders = sFolderList
    130 ActiveDocument.Range.InsertAfter sFolderList
    End <font color=blue>Function</font color=blue>
    </font color=black></code></div hiblock>

    Any insight will be appreciated.

    T.I.A.
    Regards
    Don

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: File System Object (VBA/Word-Excel/2003)

    <code>scrrun.dll</code> is the name of the Scripting Runtime dll.
    If you were feeling lazy, you could just shell a command prompt and run <code>Del /s filename</code>!
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: File System Object (VBA/Word-Excel/2003)

    Try using GetFilesIncludingSub<big>F</big>olders in line 40 instead of GetFilesIncludingSubolders - <post:=110,871>post 110,871</post:> does have the correct version.

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: File System Object (VBA/Word-Excel/2003)

    Thank you Hawkeye Hans

    The result of erroneous parsing (i.e. fingertrouble). <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Regards
    Don

  7. #7
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: File System Object (VBA/Word-Excel/2003)

    Overcome by a Greavesian mood, I took the fso code from the earlier post and put together a generic utility function (AllFoldersInTree) that returns a string array of all the folders in a particular tree. It uses a 2nd function called AllFoldersInTree_String, which actually does all the heavy lifting, but which you're free to ignore -- as long as you copy it to a place where AllFoldersInTree can find it.

    I'll follow shortly with a 2nd post that uses AllFoldersInTree to delete files matching a filespec.
    <pre>Function AllFoldersInTree(strRootFolder As String) As String()
    On Error GoTo ErrorHandler

    AllFoldersInTree = Split(AllFoldersInTree_String(strRootFolder), ";")

    ExitLabel:
    Exit Function

    ErrorHandler:
    'Your error-handling code here.
    Resume ExitLabel

    End Function

    Function AllFoldersInTree_String(strRootFolder As String, _
    Optional fCalledBySelf As Boolean) As String
    On Error GoTo ErrorHandler

    Dim fso As Scripting.FileSystemObject
    Dim oRootFolder As Scripting.Folder
    Dim oFolder As Scripting.Folder
    Dim strFolders As String

    Set fso = New Scripting.FileSystemObject

    If fCalledBySelf = False Then
    If fso.FolderExists(strRootFolder) = False Then
    MsgBox strRootFolder & " doesn't exist. Aborting..."
    GoTo ExitLabel
    End If
    End If

    strFolders = strRootFolder & ";"

    Set oRootFolder = fso.GetFolder(strRootFolder)

    If oRootFolder.SubFolders.Count > 0 Then
    For Each oFolder In oRootFolder.SubFolders
    strFolders = strFolders & _
    AllFoldersInTree_String(oFolder.Path, fCalledBySelf:=True)
    Next oFolder
    End If

    If fCalledBySelf = False Then
    If Right$(strFolders, 1) = ";" Then
    strFolders = Left$(strFolders, Len(strFolders) - 1)
    End If
    End If

    AllFoldersInTree_String = strFolders

    ExitLabel:
    Set fso = Nothing
    Set oRootFolder = Nothing
    Set oFolder = Nothing
    Exit Function

    ErrorHandler:
    'Your error-handling code here.
    Resume ExitLabel

    End Function</pre>


  8. #8
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: File System Object (VBA/Word-Excel/2003)

    As promised, here's a sample sub that uses AllFoldersInTree (and the FileSystemObject) to delete files matching a given filespec from a tree of folders.

    Just for the heck of it, I built in a Confirm option, which you might find handy for testing and debugging. But probably needless to say, if you were going to use the Confirm feature in regular use, you'd probably be better off using Windows' built-in Search feature and then choosing and deleting the files from the results pane.
    <pre>Sub DeleteFilesInTree(strRootFolder As String, _
    Optional strFilespec As String = "*.*", _
    Optional fConfirm As Boolean = True)
    On Error GoTo ErrorHandler

    Dim fso As Scripting.FileSystemObject
    Dim oFile As Scripting.file
    Dim aFolders() As String
    Dim x As Long
    Dim fDeleteIt As Boolean
    Dim strConfirm As String

    Set fso = New Scripting.FileSystemObject

    aFolders = AllFoldersInTree(strRootFolder)

    If fConfirm = False Then
    For x = 0 To UBound(aFolders)
    fso.DeleteFile aFolders(x) & "" & strFilespec
    Next x
    GoTo ExitLabel
    End If

    For x = 0 To UBound(aFolders)
    For Each oFile In fso.GetFolder(aFolders(x)).Files
    If LCase$(oFile.Name) Like LCase$(strFilespec) Then
    strConfirm = oFile.Name & vbCrLf & vbCrLf & _
    "FOLDER: " & oFile.ParentFolder
    Select Case MsgBox(strConfirm, _
    vbYesNoCancel, "Delete this file?")
    Case vbYes
    fDeleteIt = True
    Case vbNo
    fDeleteIt = False
    Case vbCancel
    GoTo ExitLabel
    End Select
    End If
    If fDeleteIt = True Then
    fso.DeleteFile oFile.Path
    End If
    Next oFile
    Next x

    ExitLabel:
    Set fso = Nothing
    Set oFile = Nothing
    Exit Sub

    ErrorHandler:
    'Your error-handling code here.
    Resume ExitLabel

    End Sub</pre>


  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: File System Object (VBA/Word-Excel/2003)

    Dude, you need to cut out the Red Bulls! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: File System Object (VBA/Word-Excel/2003)

    Thank you Steve
    Regards
    Don

  11. #11
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: File System Object (VBA/Word-Excel/2003)

    Actually, my poison is just good old coffee (with a dash of OCD).

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File System Object (VBA/Word-Excel/2003)

    >Overcome by a Greavesian mood,
    I'm taking that as a complement, until advised to the contrary(grin!).

    I wrote a "locate and optionally delete duplicates" utility last month.
    Uses fuzzy-logic to identify duplicates.
    <pre>Type typOs
    ''' This structure holds our processing options, generally obtained with a GUI/INI interface.
    sngSizeFuzz As Single ' two files must be within this fraction e.g. 0.05
    sngNameFuzz As Single ' two files must be within this fraction e.g. 0.05
    lngContent As Long ' two files must match within length e.g. +500 or -1500 (from the end of the file)
    strFolder As String
    strSourceContent As String ' two files must contain this content, somewhere
    lngLeadingLength As Long ' the (+)leading or (-)trailing length of file content to be searched
    blnCaseSensitive As Boolean
    End Type</pre>

    It returns an array of sets of filenames; optionally deletes all but the latest Date-Last-Modified file in each set.

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: File System Object (VBA/Word-Excel/2003)

    <hr>>Overcome by a Greavesian mood,
    I'm taking that as a complement, until advised to the contrary(grin!).<hr>
    Compliment, surely? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Honorary Stand-in Co-executive Deputy Vice Lounge Pedant (in loco melis).
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File System Object (VBA/Word-Excel/2003)

    [sigh!]I miss the real pedant. When is it due back?[/sigh!]

    I really meant complement, in the sense that you are [delicate] the complementary part of me[/delicate]!
    Your comment on generalizing functions served as a complement to mine!

    For which I am grateful.

Posting Permissions

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