Dir Function Error 2

[bajo71]

Hello,

I am trying to loop through a directory of files. In the test environmnet, I have 3 files; the source file which contains the macro which is skipped over and two other files, which need evaluation. The first iteration finds a file, however on the strFile=Dir() line it throws an error-"Invalid procedure call or argument." This is very puzzling. Thank you for your help.

While strFile <> ""
If strFile <> strName Then
Set wb = Workbooks.Open(strPath & strFile)
wb.Activate
wb.Sheets("Sheet1").Delete
FinalOutputPath = Left(strPath, 55)
Call Transpose(FinalOutputPath)
strFile = Dir()

Else: strFile = Dir()
End If
Wend

 

[fumei]

I suspect that strName is your problem. What is strName, and why are you using it?

Gerry

 

[Bong]

I'm not sure it makes a difference but I've always had success when subsequent calls to Dir are without the parentheses. Like:

    a = Dir("*.htm*")
    Do While a <> ""
        Sheet1.Cells(r, c) = a
        c = c + 1
        a = Dir

_________________
Bob Rashkin

 

[bajo71]

strName = ActiveWorkbook.Name

I need to skip over the macro workbook, hence the <> strName. However, I'm moving the workbook focus back and forth and I'm thinking that when the Dir function is called, it's referencing a different book; one that I manuplated then closed.

 

[bajo71]

Here's the top part of the code...

strPath = ActiveWorkbook.Path & "\"
strName = ActiveWorkbook.Name
strFile = Dir(strPath)
Application.ScreenUpdating = False
Application.DisplayAlerts = False

 

[fumei]

Umm, "I'm thinking that when the Dir function is called, it's referencing a different book"

Dir does not reference a workbook.

Try Bong's suggestion.

Gerry