Do While only does once

[Mike Williams]

I'm having trouble getting a do loop to advance. I'm used to working
with for next loops, and this one is new to me. I'm trying to cycle
through files in a folder, look their names up in a table, find a new
name, and rename the file. This code works on the first iteration, and
then errors out the second time through on the Name line, with a type
mismatch. Any suggestions would be greatly appreciated. THANKS.


Dim OldName As String
Dim NewName As String
Dim File
Dim myPath As String

myPath = ActiveWorkbook.Path
File = Dir(myPath & "\*.WMV")

Do While File <> ""
'Application.StatusBar = "Renaming files."
If File <> ThisWorkbook.Name Then
OldName = File
' MsgBox OldName
' MsgBox NewName

NewName = Application.VLookup(OldName, Range("sortdata"), 4,
False)

POldName = myPath & "\" & OldName
PNewName = myPath & "\" & NewName

MsgBox POldName
MsgBox PNewName

Name POldName As PNewName
' OldName = ""
' NewName = ""
End If
'Exit Sub
Loop

 

[Tom Ogilvy]

I am not sure why you get a type mismatch, but you never change the variable
file in your loop, so it it is entered, it should loop forever.

Dim OldName As String
Dim NewName As String
Dim File
Dim myPath As String

myPath = ActiveWorkbook.Path
File = Dir(myPath & "\*.WMV")

Do While File <> ""
'Application.StatusBar = "Renaming files."
If File <> ThisWorkbook.Name Then
OldName = File
' MsgBox OldName
' MsgBox NewName

NewName = Application.VLookup(OldName, Range("sortdata"), 4,
False)

POldName = myPath & "\" & OldName
PNewName = myPath & "\" & NewName

MsgBox POldName
MsgBox PNewName

Name POldName As PNewName
' OldName = ""
' NewName = ""
End If
file = Dir() ' get the next filename
'Exit Sub
Loop


However, it isn't advisable to change your directory while doing a loop
based on Dir. This can cause problems. It is better to gather your file
list to an array in the Dir loop, then loop through the array making your
changes.

 

[Bob Phillips]

Hi Mike,

I think it errors because you are not changing File in the loop, so each
iteration is using the same value. But as it got changed in iteration 1,
trying to rename it again in loop 2 throws the error, it doesn't exist by
that name then.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Mike Williams]

Thanks, Tom & Bob. I couldn't make out in the documentation on Do While
just where the thing got incremented; it's more obvious with For Each Next
statements. I'm assuming it's in the "file = ..." line Tom added, quoted
below -- although I still don't get how that line works. One additional
question. I didn't realize I was changing the directory at all, and I
certainly don't need to. Did I do that somewhere, or is it in the line you
added? I'm definitely not up to speed on Tom's last suggeston: gathering
the file list into an array...; could you by chance point me to a relevant
example? THANKS AGAIN...!

I am not sure why you get a type mismatch, but you never change the
variable file in your loop, so it it is entered, it should loop
forever.

 

[Bob Phillips]

Mike,

I am missing what Tom says myself. I don't think that he is saying that you
are changing the directory, but as I don't see that he is doing it either, I
don't get the point. However, assuming that you want to process all files
called *.WMV in the directory, Tom's amendment seems to do the job fine.
Can't see how an array will help in this case.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

You are misinterpreting the term directory. If he is renaming the file, he
is changing the data stored in the directory section of the file management
system.

http://support.microsoft.com/default.aspx?scid=kb;en-us;254889&Product=xlw
OFF2000: Endless Loop When Macro Modifies Files in a Folder

However, renaming the files may not cause the problem - I said it is
possible.


some sample code on looping through a directory
http://support.microsoft.com/default.aspx?scid=kb;en-us;139724&Product=xlw
Macro to Loop Through All Files in a Folder (Directory)

http://support.microsoft.com/default.aspx?scid=kb;en-us;213369&Product=xlw
XL2000: How to Programmatically Display All Files in a Folder


Here is some code that builds an array of filenames:

Sub Tester10()
LookForfiles "C:\Data"
End Sub
Sub LookForfiles(ByVal DirToSearch As String)
Dim i As Long
Dim Files() As String
Dim Contents As String
Dim counter As Long

counter = 0
DirToSearch = DirToSearch & "\"
Contents = Dir(DirToSearch, vbDirectory)
Do While Contents <> ""
If Contents <> "." And Contents <> ".." Then
If (GetAttr(DirToSearch & Contents) And vbDirectory) <> vbDirectory _
Then
If InStr(Contents, ".xls") Then
counter = counter + 1
ReDim Preserve Files(1 To counter)
Files(counter) = DirToSearch & Contents
End If
End If
End If
Contents = Dir()
Loop
If counter = 0 Then Exit Sub
For i = 1 To counter
Debug.Print Files(i)
Next i
End Sub