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

 

[jmacleary]

Hi there. Why not empty all the rows in your master file each time you run the grab, then you wouldnt have any duplicates. This would take account of any insertions or deletions in previously captured data. Alternatively, assuming the users dont delete or insert rows in the middle of stuff you grabbed before, you could have a sheet with a record of each workbook's previous last row, and then modify your copy range to suit.

 

[braidp]

Hi there. Why not empty all the rows in your master file each time you run the grab, then you wouldnt have any duplicates. This would take account of any insertions or deletions in previously captured data. Alternatively, assuming the users dont delete or insert rows in the middle of stuff you grabbed before, you could have a sheet with a record of each workbook's previous last row, and then modify your copy range to suit.

Jmacleary, firstly thank you for replying and wiping the data in the master file each time I run the grab would absolutely work, is there a way of inserting this to happen as part of the run to save the process being done manually?

 

[jmacleary]

Ok. All thats needed is this small addition immediately before your Do While line:

erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Sheet1.Rows("2:" & Format(erow)).EntireRow.ClearContents

That assumes your data starts in row 2, if not, adjust the 2 to suit.

PS I notice you stop the run when you hit the master file name - what if you had a file like ZUmba.xlsm, which would be after your master file? It wouldnt get read at all. Is this a problem?

 

[braidp]

Ok. All thats needed is this small addition immediately before your Do While line:

erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Sheet1.Rows("2:" & Format(erow)).EntireRow.ClearContents

That assumes your data starts in row 2, if not, adjust the 2 to suit.

PS I notice you stop the run when you hit the master file name - what if you had a file like ZUmba.xlsm, which would be after your master file? It wouldnt get read at all. Is this a problem?

jmacleary, that worked like a dream and has solved the problem!!! Thankfully there will be no workbook starting with the letter Z as I did initially have this proble when I had the file just called master file........and it wasn't retreaving any data past that file as you explained so I figured that was the problem and renamed it so it would pull all the files. I'm sure it's be far not the best solution but it got it working haha

Many thanks again for all your help!!

I am planning to do a course on excel to further my knowledge as it's an amazing bit of software that really interests me

 

[jmacleary]

Thats good. You could improve the code to just skip the master file by:

Sub LoopThroughDirectory()
Dim Filepath As String
Dim erow
Filepath = "C:\Users\paulb\Desktop\EmployeeProject"
MyFile = Dir(Filepath)
erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Sheet1.Rows("2:" & Format(erow)).EntireRow.ClearContents
Do While Len(MyFile) > 0
[COLOR=#b22222]If MyFile <> "ZMasterFile.xlsm" Then[/COLOR]
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
[COLOR=#b22222]End If[/COLOR]
Loop

End Sub

This would work whatever files were in the folder.

 

[braidp]

That works even better many thanks - Now I have it working on my laptop I just need to figure out how to get it to run in a folder on a network drive. I changed the Filepath = to what I believe to be the file path but it doesn't retrieve any of the data so I am guessing I have the file path wrong some how lol

 

[jmacleary]

Hello again. If you use Filepath=<code>Application.ActiveWorkbook.Path instead of your hard-coded path, that will pick up the current directory. It then shouldn't matter where the folder actually lives.

And, I may have introduced an infinite loop in your code. The End if should be before the MyFile=Dir.

Regards
John
</code>

 

[braidp]

Thanks for the continued support in getting this to work John,

I tried the below code in a folder with the 3 Workbooks I want to extract the data from to test it however it's not pulling any data in for some reason?





Sub LoopThroughDirectory()
Dim Filepath As String
Dim erow
Filepath = Application.ActiveWorkbook.Path
MyFile = Dir(Filepath)


erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Sheet1.Rows("2:" & Format(erow)).EntireRow.ClearContents


Do While Len(MyFile) > 0
If MyFile <> "ZMasterFile.xlsm" Then


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))


End If


MyFile = Dir


Application.DisplayAlerts = True




Loop




End Sub

 

[jmacleary]

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.