Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    looping (windows 2000)

    I am working on Excell, writing a VBA program
    How do I loop between folders named sequencially aa,ab,ac...........az,ba,bb.........bz.
    Basically I am counting # files in folders with name aa, ab etc and writing the count to a spreadsheet.
    I do not want to repeat the process by specifying the folder name specifically, therefor I tred to loop from aa to bz - it returns an error - I assume it doec not recognise my counter of aa to bz.

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

    Re: looping (windows 2000)

    For ... Next only works with a numeric loop index.

    Dim i As Long
    Dim j As Long
    Dim strFolderName As String

    For i = 1 To 2
    For j = 1 To 26
    strFolderName = Chr(96 + i) & Chr(96 + j)
    ' write your own code to process the folder here
    Next j
    Next i

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: looping (windows 2000)

    If you want to count the files in all of the subfolders of a particular folder, you could do something like this:

    <pre>Public Sub LoopFolders()
    Dim oFS As Object, oSearchFolder As Object, oSubFolders As Object
    Dim oFolder As Object, oFile As Object
    Dim lCount As Long
    Set oFS = CreateObject("Scripting.FileSystemObject")
    Set oSearchFolder = Nothing
    On Error Resume Next
    Set oSearchFolder = oFS.GetFolder("C:Work")
    On Error GoTo 0
    If Not oSearchFolder Is Nothing Then
    Set oSubFolders = oSearchFolder.subfolders
    For Each oFolder In oSubFolders
    lCount = 0
    For Each oFile In oFolder.Files
    lCount = lCount + 1
    Next oFile
    MsgBox oFolder.Name & " " & lCount
    Next oFolder
    End If
    End Sub
    </pre>

    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: looping (windows 2000)

    Thanks Hans & Legare. It looks so easy & work like a dream

Posting Permissions

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