Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Apr 2003
    Kansas City, Missouri
    Thanked 0 Times in 0 Posts

    Iterating through directories (Word 97/SR2 VBA)

    I'd like some input and hints from the esteemed VBA gurus. I am embarking on something I've not done before, so I'm bound to do something wrong.

    Some info: This machine does not have VB. It has Office 97, and I plan on doing this task using Word VBA, since it is the environment I'm most familiar with. So, I'm already doing that which I ought not to do.

    The purpose of my task is to cycle through all files and subdirectories. I am to rename the files in the subdirectories based on the names of some of the subdirectories. Roughly, it will be like this:

    c:FolderxxxxFolderyyyFile name.pdf

    will become

    c:FolderxxxxFolderyyyxxxx yyy File name.pdf

    I'm going to cycle through a lot of xxxx folders. I've not really worked with ChDir before, so I'd like to learn of some gotchas I should be aware of.

    I'll post the code that I'm certain will work. If anyone sees an error in my decisions, I'd appreciate a gentle nudge (or a rude push is welcome too).

    Do While strFile <> ""
        If strFile <> "." And strFile <> ".." Then
    ' Check to see if the file name is a directory.
            If GetAttr(strPath & "" & strFile) <> vbDirectory Then
                If IsNumeric(Left(strFile, 1)) Then
                    Name strPath & "" & strFile As strPath & "" & strPatientNumber & " " & strEndpoint & " " & strFile
                End If
    ' Jumping through directories goes here.        
            End If

    The strPatientNumber and strEndpoint variables will be defined through the uses of InStr and Mid as the path changes.

    I look forward to this new project but fear the havoc I can cause with it. I'm always somewhat queasy about using Word as a file manager, especially since it doesn't involved Word documents.

    Kevin Elmore
    Last edited by jscher2000; 2011-07-26 at 20:19. Reason: Fixed [code] tags.

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Silicon Valley, USA
    Thanked 93 Times in 89 Posts

    Re: Iterating through directories (Word 97/SR2 VBA)

    Two part answer. First, there was a thread here where someone (maybe that other Kevin, you know, the surprised and youthful looking one) showed how to build a loop that would iterate through all the sub folders without any special data structures. Hopefully someone has that code!

    Second, I created a VBScript application for our team for use in a document conversion. It's not directly useful to you, but illustrates some of the little things to think about in building out longer file names. Maybe. I don't really remember. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    'Jefferson Scher 10/2/2001, 10/5/01
    Sub PrefixFiles()
    Dim oFSO, strFolder, oFolder, oFiles, oFile, strPrefix, strNewName
    strFolder = InputBox("Enter mapped or local path to folder (not UNC)", _
      "Select directory to update")
    If Len(Trim(strFolder)) = 0 Then Exit Sub
    'set up objects
    '  parent object from the scripting runtime
    Set oFSO = WScript.CreateObject("scripting.filesystemobject")
    '  get selected folder
    Set oFolder = oFSO.GetFolder(strFolder)
    '  collection of files in selected folder
    Set oFiles = oFolder.Files
        strPrefix = getInput(oFolder.Name)
        If strPrefix = "*SKIPDIRECTORY*" Then Exit Do
        strPrefix = Trim(strPrefix)
        If Left(strPrefix, 1) <> "(" Then
            strPrefix = "(" & strPrefix
        End If
        If Right(strPrefix, 1) <> ")" Then
            strPrefix = strPrefix & ")"
        End If
        If MsgBox("Is this right - ''" & strPrefix & "''?", 4+32, _
                "Final Answer?") = 6 Then
            Exit Do
        End If
    If strPrefix <> "*SKIPDIRECTORY*" Then
        'rename all files in the folder
        For Each oFile In oFiles
            If Len(strPrefix) + Len(oFile.Name) < 64 Then
                If Left(UCase(oFile.Name), 5) <> "XNAME" Then
                    oFile.Name = strPrefix & " " & oFile.Name
                End If
                strNewName = oFile.Name
                strNewName = Replace(strNewName, "letter", "ltr",1,-1,1)
                strNewName = Replace(strNewName, "lttr", "ltr",1,-1,1)
                strNewName = strPrefix & " " & strNewName
                    strNewName = Trim(InputBox("Please trim the new file name down to 64 " & _
                                 "characters (including .doc).  You are over by " & _
                                 Len(strNewName) - 64 & " characters.", "File name too long", _
                    If Len(strNewName) = 0 Then
                        MsgBox "No blank file names allowed!"
                        strNewName = strPrefix & " " & oFile.Name
                    ElseIf Len(strNewName) <= 64 Then
                        oFile.Name = strNewName
                        Exit Do
                    End If
            End If
    End If
    Set oFiles = Nothing
    Set oStream1 = Nothing
    Set oFSO = Nothing
    End Sub
    Function getInput(strDefault)
    Dim strTemp
        strTemp = InputBox("Enter prefix for file names, for example, ''(Big Dispute)'', " & _
                "bearing in mind that ''shorter is better''", _
            "Enter your information here!", strDefault)
        If Len(Trim(strTemp)) = 0 Then
            If MsgBox("Do you want to skip this directory after all?", 4+32) = 6 Then
                getInput = "*SKIPDIRECTORY*"
                Exit Function
                MsgBox "Please re-enter the prefix you want to add to the file names."
            End If
            'some minimal validation for illegal characters
            If (InStr(1, strTemp, "/") > 0) Or (InStr(1, strTemp, "") > 0) Or _
              (InStr(1, strTemp, "*") > 0) Or (InStr(1, strTemp, "?") > 0) Or _
              (InStr(1, strTemp, "|") > 0) Or (InStr(1, strTemp, """") > 0) Or _
              (InStr(1, strTemp, "<") > 0) Or (InStr(1, strTemp, ">") > 0) Or _
              (InStr(1, strTemp, ":") > 0) Then
                MsgBox "Please revise the prefix so it does not contain illegal characters."
                getInput = strTemp
                Exit Do
            End If
        End If
    End Function
    Hope this helps.
    Last edited by jscher2000; 2011-07-26 at 20:19. Reason: Fixed [code] tags.

  4. #3
    Gold Lounger
    Join Date
    Dec 2000
    Hollywood (sorta), California, USA
    Thanked 0 Times in 0 Posts

    Re: Iterating through directories (Word 97/SR2 VBA)

    I too have some sample code to help you on your way. Use the FileSystemObject as Jefferson does (and I do in attached script file).
    Rename the script to FileLister.vbs and give it a go. You should have a pretty good foundation after looking over the code. Recoding to
    turn the vbs into vba is trivial.

    Without the search facility, finding the vba version of this is next to impossible. So the code J refers to is essentially lost for now. <img src=/S/sad.gif border=0 alt=sad width=15 height=15>
    Kevin <IMG SRC= alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

Posting Permissions

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