CODEWITHSUNDEEP
excelvba
Drew Boughton
Drew Boughton

Reputation: 23

Exclude a file in dir

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.

Upvotes: 1

Views: 905

Answers (1)

Ed Seyk
Ed Seyk

Reputation: 11

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.

Upvotes: 1

Related Questions