Removal of Duplicates when re running the report

[braidp]

Hi Guys,

I hope you can help -

The below is something I have built out to grab data from about 90 workbooks. The challenge I have is I need to run this each week as the data updates however this duplicates the data and I would like it to avoid this so that it only reports on the latest data pull from the 90 workbooks

Any help would be gratefully appreciated


Regards






Sub LoopThroughDirectory()
Dim Filepath As String
Dim erow
Filepath = "C:\Users\paulb\Desktop\EmployeeProject"
MyFile = Dir(Filepath)
Do While Len(MyFile) > 0
If MyFile = "ZMasterFile.xlsm" Then
Exit Sub
End If
Application.DisplayAlerts = False
Workbooks.Open (Filepath & MyFile)
Range("A2:M900").Copy
ActiveWorkbook.Close

erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 13))
MyFile = Dir
Application.DisplayAlerts = True

Loop

End Sub

 

[braidp]

Sorry, not sure why - I'm off home in a few minutes but I'll have a look later - might be around 9pm by then though.

Many thanks

 

[jmacleary]

OK. eventually....
Replace the line myFile = Dir(Filepath) with myFile = Dir(Filepath + "\*.*"). If you want to get clever about it and only work on excel files without macros (which I think is your case) then replace it with: myFile = Dir(Filepath + "\*.xlsx")

 

[braidp]

Thanks John,

That seems to be searching for the files but it comes up with a runtime 1004 error saying it can not find the folder

When it debugs it highlights the line

Workbooks.Open (Filepath & MyFile)

Any ideas?


Looking at it again the error is looking for the name of the folder the documents are stored as an excel file

Sorry we could not find c:\Users\braidp\Desktop\FileProject.xslx is it possible it was moved or deleted

FileProject is the name of the folder that the excel ZMaster and other workbooks are in, rather than an excel file in it's own right?

 

[jmacleary]

Hello again sorry for the delay. I guess its picking up the parent directory for some reason, so myFile = Dir(Filepath + "\FileProject\*.*") should drop down into the folder and pick up your files.