Exclude a file in dir

Question

I need to exclude a file from a DIR look up. The reason is the Macro runs out of that file and looks for information in the other files. Any ideas?

Sub button1_Click()
    Dim fn As String
    Dim rng As Range
    Dim myDir As String
    Dim fDir As String

    myDir = "C:\Users\boughtond\Desktop\PFS Macro\"
    fDir = Dir(myDir & "*.xlsm")

    Do While fDir  ""
        Workbooks.Open (myDir & fDir)

        For x = 9 To 108
            Workbooks(fDir).Activate
            If Cells(x, 1).Value = "O" Then
                fn = Cells(x, 2).Value
                Range(Cells(x, 2), Cells(x, 13)).Select
                Selection.Copy
                Windows("NAMC EIS PFS_All Shops.xlsm").Activate
                With Worksheets("Master").Range("A8:A1048576")
                    Set rng = .Find(What:=fn, LookIn:=xlValues)
                    If rng Is Nothing Then
                        ActiveSheet.Unprotect "eispfs"
                        Range("A1048576").End(xlUp).Offset(1, 0).Select
                        Selection.PasteSpecial
                    Else
                        MsgBox ("Found this sh*t" & Chr(10) & x)
                    End If
                End With
            Else
            End If
            'MsgBox x
            'x = x + 1
        Next x

        fDir = Dir()
    Loop

End Sub

Ignore the MSGBOX as I used to verify the loop was working. The "NAMC PFS_All shops.xlsm" is in the same dir and the macro is run from it. I need to exclude it from the look up. I can't seperate it out of the folder either.

Any suggestions? Thanks for taking the time.

`if fDir.name="NAMC PFS_All shops.xlsm" then continue for` perhaps? — findwindow, Aug 25 '15 at 18:15
`If fDir <> ThisWorkbook.Name` is another possibility. fDir holds the name of the workbook. It doesn't get a `.Name` until `Workbooks(fDir)`. — , Aug 25 '15 at 18:38
Jeeped I tried your approach and it keeps crashing excel. This was also the approach a friend of mine suggested. we both looked at it and can't figure it out. we placed this If fDir <> ThisWorkbook.Name Then Else End If — Drew Boughton, Aug 25 '15 at 19:38
@DrewBoughton you want to put it right after/below the do statement I think. — findwindow, Aug 25 '15 at 20:53
@findwindow, moved it this morning and the crash is fixed but it is still trying to open the document that is already open. Thanks for the help. I have reached out to a friend that got me started on VBA. I post back up if and when I get this figured out. Thanks again the VBA community is awesome. — Drew Boughton, Aug 26 '15 at 11:01
I adapted the technique in [this post](http://stackoverflow.com/a/9382034/4717755) for my own uses, as it returns the workbook whether it's already open or not. Props to @DickKusleika — PeterT, Aug 26 '15 at 12:42
Thanks everyone I finally got it working. But I ditched the DIR and just repeated the code for all the workbooks. Everyone's feedback and thoughts was much appreciated and i learned a little something with it. — Drew Boughton, Aug 26 '15 at 18:45

score 1 · Answer 1 · answered Jan 25 '23 at 01:32

You can exclude one or more files from a DIR look up by inserting two lines in your code above. First, insert an IF statement between the DO WHILE and Workbooks.Open lines. Second, insert a line label between the Next x and fDir = Dir() lines. If you use Skip: as the line label, the IF statement would be If fDir = "NAMC PFS_All shops.xlsm" Then GoTo Skip (based on your question). I'm a beginner but had the same problem and this worked for me but I used like and a wild card to exclude several files.

Many people think of using GoTo as bad design , especially if you can easily avoid it with a If … Then … End If statement. Avoid GoTo unless it is for error handling On Error GoTo it's a bad practice — Max, Jan 25 '23 at 21:45

Exclude a file in dir

1 Answers1