Re: Do While only does once

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Bob Phillips (bob.phillips_at_notheretiscali.co.uk)
Date: 04/08/04 

Date: Thu, 8 Apr 2004 16:05:15 +0100

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" <williamsmi@simon.rochester.edu> wrote in message
news:Xns94C56B4EDCB81MikeWill2@207.46.248.16...
> 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

Relevant Pages

  • Re: Looping through Query to create multiple sheets in excel- Just need the loop
    ... I always get to this point and I can't figure out how to loop through ... I have put the string in the query at the bottom. ... Optional strWorkSheet As String, Optional strRange As ... Dim objXLSheet As Object 'Excel.Worksheet ...
    (microsoft.public.access.forms)
  • Re: How is this conditional able to execute?
    ... inside the loop, then each time the loop iterates then a NEW reference to ... Note that the test of returndata is not a type. ... Dim returndata As String = "" ... Not that 'Changing' is only displayed when returndata is NOT an empty string ...
    (microsoft.public.dotnet.languages.vb)
  • Re: How is this conditional able to execute?
    ... Why is this part out of the loop: ... Maybe I just don't understand the returndata = "" because of the byte array. ... How can I read the string in: ... > Dim t As Thread ...
    (microsoft.public.dotnet.languages.vb)
  • Re: How to put lines with certain text (from a file) in an array
    ... In one sense all consistent but now the Filter approach is taking about 2x longer than the loop with Instr with all sizes. ... Dim sFile As String, sText As String ...
    (microsoft.public.excel.programming)
  • Re: Dynamically Update Text Box on-slide with loop (VBA)
    ... the slide. ... checked and strDiff does not get updated during the loop. ... Dim EventDate As Date, curDate As Date ... Dim sDateFile As String ...
    (microsoft.public.powerpoint)