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:
Thanks for your help
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:
Code:
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
Last edited by a moderator: