Open and copy all data from from dat file in a specified folder, but exclude if file name listed in worksheet

[goeman]

Hello there

I have a vba code which works perfectly... it opens and copys all data from any DAT file located in a specified folder (C:\Junk) to a worksheet called 'download' and also stores the name of any DAT file it has processed in a worksheet called 'Processed_Files'

However I need the macro not to open and copy the data if the name of the DAT file is already listed in the worksheet 'Processed_Files'

The VBA code I have is as follows:

Sub Part_1_0_MergeDataFromWorkbooks()

'DECLARE AND SET VARIABLES

Dim wbk As Workbook
Dim wbk1 As Workbook
Set wbk1 = ThisWorkbook
Dim Filename As String
Dim Path As String
Path = "C:\Junk" 'CHANGE PATH

Filename = Dir(Path & "*.dat")


'OPEN EXCEL FILES

Do While Len(Filename) > 0 'IF NEXT FILE EXISTS THEN
Set wbk = Workbooks.Open(Path & Filename)

wbk.Activate

     On Error Resume Next
     Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("EccoAdmin NPS Survey.xlsb").Activate
Application.DisplayAlerts = False
Dim lr As Double
lr = wbk1.Sheets("Download").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("Download").Select
Cells(lr + 1, 1).Select
ActiveSheet.Paste
wbk.Close True
Filename = Dir

Loop

      Sheets("Processed_Files").Select
      
    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    Range("A" & lMaxRows + 1).Select
Dim F As String
F = Dir("C:\Junk" & "*.dat")
Do While Len(F) > 0
ActiveCell.Formula = F
ActiveCell.Offset(1, 0).Select
F = Dir()
Loop

End Sub

Thanks for your help

 

[goeman]

Hi

Think the issue is fixed... I just changed the following from 0 to -1

If Not UBound(Filter(Ary, Pth & Fname, True, vbTextCompare)) > -1 Then


Thanks for taking the time to look at this for me 'Fluff' really appreciate your help..

 

[Fluff]

Glad you got it sorted & thanks for the feedback