Dir is a rather limited function in the sense that you can have only
one Dir loop. Creating another Dir loop from within the outer loop
will cause an invalid procedure call. For example, the following code
is fine:
Sub OneLoop()
Dim FName As String
FName = Dir("C:\VBAModules\*.bas")
Do Until FName = vbNullString
Debug.Print FName
FName = Dir
Loop
End Sub
This has only one Dir loop. However, examine the following code:
Sub TwoLoops()
Dim FName As String
Dim N As Long
FName = Dir("C:\VBAModules\*.bas")
Do Until FName = vbNullString
N = N + 1
Debug.Print FName
If N = 10 Then
FName = Dir("C:\DVDs\*.*")
Do Until FName = vbNullString
Debug.Print FName
FName = Dir
Loop
End If
FName = Dir '<<<< BLOWS UP
Loop
End Sub
Here, you have the outer Dir loop looking at C:\VBAModules. At some
point (arbitrarily triggered when a counter = 10), the code enters an
inner Dir loop, looking at C:\DVDs. This inner loop resets all of
Dir's inner parameters, and when the inner loop exits and control
returns to the outer loop, which was originally looking at
C:\VBAModules, Dir is all screwed up internally and you get the
invalid proc call error. Dir does not pick up where it left off.
As a general rule, you should NEVER do anything with the Dir function
when you are using it to loop through a directory. For all but the
most simple tasks, I never use Dir. Instead, I use the
Scripting.FileSystemObject when allows you to run multiple seach loops
and provides much more information that Dir.
Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]
On Mon, 4 Jan 2010 11:34:01 -0800, BigJimmer
<(E-Mail Removed)> wrote:
>No, there is no other Dir within the other code not shown (that was something
>I did think of after I posted originally).
>
>I have found a statement I recently added to my code that is causing the Dir
>function to be reset, though I'm not sure why.
>
>The function that causes this is from Chip Pearson's website
>(http://www.cpearson.com/Excel/docprop.aspx) -
>
>Function GetProperty(PropertyName As String, PropertySet As
>PropertyLocation, _
> Optional WhatWorkbook As Workbook) As Variant
>''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>' GetProperty
>' This procedure returns the value of a DocumentProperty named in
>' PropertyName. It will examine BuiltinDocumentProperties,
>' or CustomDocumentProperties, or both.
>
>This function works as intended, and the resetting of the value of Dir is
>the only dowside for my particular purpose.
>
>
>
>"Rick Rothstein" wrote:
>
>> I cannot duplicate your problem... the code you posted works fine. Are you
>> by any chance using another Dir command (with a different argument) in the
>> section you designated "additional logic here"?
>>
>> --
>> Rick (MVP - Excel)
>>
>>
>> "BigJimmer" <(E-Mail Removed)> wrote in message
>> news:9973B53F-7A07-495A-B5E0-(E-Mail Removed)...
>> >I have the following code...
>> >
>> > Dim fName As String
>> > dim DestPath as string
>> >
>> > DestPath = ThisWorkbook.Path & "\"
>> > fName = Dir(DestPath & "*.*")
>> > Do Until fName = vbNullString
>> >
>> > 'additional logic here
>> >
>> > fName = Dir
>> > Loop
>> >
>> > I get error 5 Invalid procedure call or argument on the line fname = Dir
>> > and
>> > don't understand why.
>> >
>> > I am using Excel 2000.
>> >
>> > Thanks!
>>
>> .
>>
Similar Threads
