Weird VBA problem

[Bob]

The code below works.

It is just a simple loop that lists files in my "C:\Less\In\"
directory.

Notice that the "Calculate" keyword is memo only.
Now if I activate calculate by removing the "'" I get a "Run-time
error 5 " message on the first run of the Sub. On subsequent runs it
is ok ?!
(The "str_FileName = Dir " line is highlighted on Debug)

If I adjust its position on the line (not within the code itself) I
get the message on the first Sub run again!

How can this be?





Sub L()

Dim str_FileName As String 'Name of file in AllJnls folder.
Dim str_MyPath As String 'Path to the AllJnls folder.

' Loop to get all LESS IN Files.
str_MyPath = "C:\Less\In\" ' Set the path.
str_FileName = Dir(str_MyPath) ' Retrieve the first
entry.

Do While str_FileName <> "" 'Loop for each file.

'Calculate

Debug.Print str_FileName

str_FileName = Dir ' Get next file.

Loop
End Sub

 

[Bob Phillips]

you need to specify which object the calculate method is to work on be that
the application, or a range.

--

HTH

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

 

[Bob]

you need to specify which object the calculate method is to work on be that

the application, or a range.

I can put in "Calculate", "Range("A1:F10").Calculate" or a fully
qualified path the problem is the same.

The page being calculated is nothing special.

My original loop contained a lot more code most of which works fine.
I have have traced the problem to this calculate keyword although I
don't think this is the root of the problem as I have a call to
another sub that gives the same error.

Is anyone aware of any Excel bugs that sometimes cause a DIR to fail?

Thanks.

 

[Bob]

Could this possibly be a corrupt Excel workbook ?