Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Apr 2003
    Location
    Kansas City, Missouri
    Posts
    66
    Thanks
    0
    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).

    Code:
    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
                Else
                    Name strPath & "" & strFile As strPath & "" & strPatientNumber & " " & strEndpoint & " " & strFile
                End If
            Else
    ' Jumping through directories goes here.        
            End If
    Loop

    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 21:19. Reason: Fixed [code] tags.

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    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>

    Code:
    'Jefferson Scher 10/2/2001, 10/5/01
    '----------------------------------------------------------------------
    PrefixFiles
      
    Sub PrefixFiles()
    Dim oFSO, strFolder, oFolder, oFiles, oFile, strPrefix, strNewName
    
    Do
    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
    
    Do
        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
    Loop
    
    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
            Else
                strNewName = oFile.Name
                strNewName = Replace(strNewName, "letter", "ltr",1,-1,1)
                strNewName = Replace(strNewName, "lttr", "ltr",1,-1,1)
                strNewName = strPrefix & " " & strNewName
                Do
                    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", _
                                 strNewName))
                    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
                Loop
            End If
        Next
    End If
    
    Set oFiles = Nothing
    Set oStream1 = Nothing
    Set oFSO = Nothing
    Loop
    End Sub
     
    Function getInput(strDefault)
    Dim strTemp
    Do
        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
            Else
                MsgBox "Please re-enter the prefix you want to add to the file names."
            End If
        Else
            '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."
            Else
                getInput = strTemp
                Exit Do
            End If
        End If
    Loop
    End Function
    Hope this helps.
    Last edited by jscher2000; 2011-07-26 at 21:19. Reason: Fixed [code] tags.

  3. #3
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    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=http://www.wopr.com/w3tuserpics/Kevin_sig.gif 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
  •