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

goeman

New Member
Joined
Jan 18, 2018
Messages
13
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:


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:
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..
 
Upvote 0

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Glad you got it sorted & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top