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: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
' Jumping through directories goes here.
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.
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>
Hope this helps.
'Jefferson Scher 10/2/2001, 10/5/01
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
If Right(strPrefix, 1) <> ")" Then
strPrefix = strPrefix & ")"
If MsgBox("Is this right - ''" & strPrefix & "''?", 4+32, _
"Final Answer?") = 6 Then
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
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
Set oFiles = Nothing
Set oStream1 = Nothing
Set oFSO = Nothing
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*"
MsgBox "Please re-enter the prefix you want to add to the file names."
'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
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>