Dir function error

[BigJimmer]

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!

 

[Rick Rothstein]

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"?

 

[Ryan H]

I'm going to go out on a limp. This code isn't tested, but try:

Dim fName As String
Dim DestPath As String

DestPath = ThisWorkbook.Path & "\"
fName = Dir(DestPath & "*.*")
Do Until fName = ""

'additional logic here

fName = Dir("")
Loop

Hope this helps! If so, let me know, click "YES" below.

 

[BigJimmer]

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.

 

[RB Smissaert]

Not sure it applies to your particular situation, but I think there is an
issue with files like pagefile.sys

RBS

 

[Chip Pearson]

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]

 

[Rick Rothstein]

You might try storing all the filenames in an array by iterating Dir loop
without doing anything else in it (except saving the filenames to the array)
and then in your current Dir iteration loop, iterate the array elements
instead.

 

[BigJimmer]

Thanks you both for responses.

Rick - using an array to hold the file names, and then doing the logic I
need with each was the short term solution I also had thought of, with the
intention of continuing to look for a more eloquent solution.

Chip - I will be looking more into the Scripting.FileSystem. I have used it
before, I'm just not familiar enough with it to have it be the first thing I
would have thought of when I needed to loop through these files. Also,
thanks for all the information you have put on you web site. I use it
frequently.