Excel VBA connected to external files

[TomExcel1234]

Hello, i am trying macro to do following, please. Heres a brief summary:

background (not relevant to macro):
I was trying to delete duplicate frames in a video. I exported video into lets say 10’000 frames and managed to delete 30% of duplicates. However, afterwards i found video in higher quality so i exported second batch of 10’000 frames. If i had a macro to delete duplicates in second batch too, it would save me hours to manually go through second batch and do the same work all over again, please.

Desired Macro:
I have two folders in windows. Folder 1 has 7000 images (format ‘00001.jpg’ to ‘10000.jpg’) where i deleted 30% of duplicate frames. Second folder 2 has 10’000 frames in higher quality where i need to remove duplicates.
Macro should go into folder 2, see if frame 00001.jpg is in folder 1 as well. IF YES, then keep the file 00001.jpg in folder 2 (do nothing), IF NO, then delete file in folder 2 [because its a duplicate]. Then repeat for all 10’000 frames. The macro would be based on naming of files only. It would neither open nor analyze pictures themselves.

i am good with vbas within excel, but never done linking vba to external files, so im clueless. If someone could help, this would save me about 10 hours of manual work, please. Thank you for any help.

 

[Marc L]

Hi,​

​

how can a file be a duplicate if it exists only in one folder rather than in both folders ?​

​

Why using Excel VBA for such purpose ? Can be achieved without Excel and maybe faster …​

 

[TomExcel1234]

Hi,​

​

how can a file be a duplicate if it exists only in one folder rather than in both folders ?​

​

Why using Excel VBA for such purpose ? Can be achieved without Excel and maybe faster …​

Hello Marc,

thank you for your answer. In folder 1, there used to be 10'000 images out of which 3000 were duplicates that I manually deleted. In folder 2, there is same set of 10'000 images but in higher quality. It still has those extra 3'000 images that i need to delete. But because i have already done the work in folder 1, i do not want to do it second time manually by going through 10'000 images in folder 2, but in an automatic way based on naming convention.

If there is a different/faster way without excel, could you please advise what it would be, please? I'd appreciate any help.

Thank you very much

 

[Marc L]

As after all here it's a VBA forum so a demonstration for starters :​

Sub Demo1()
              Bill$ = Dir("D:\Folder1\*.jpg")
    With CreateObject("Scripting.Dictionary")
        While Bill > ""
             .Item(Bill) = Empty
              Bill = Dir
        Wend
              Bill = Dir("D:\Folder2\*.jpg")
        While Bill > ""
              If Not .Exists(Bill) Then Kill Bill
              Bill = Dir
        Wend
             .RemoveAll
    End With
        Application.Speech.Speak "Done !", True
End Sub

 

[TomExcel1234]

As after all here it's a VBA forum so a demonstration for starters :​

Sub Demo1()
              Bill$ = Dir("D:\Folder1\*.jpg")
    With CreateObject("Scripting.Dictionary")
        While Bill > ""
             .Item(Bill) = Empty
              Bill = Dir
        Wend
              Bill = Dir("D:\Folder2\*.jpg")
        While Bill > ""
              If Not .Exists(Bill) Then Kill Bill
              Bill = Dir
        Wend
             .RemoveAll
    End With
        Application.Speech.Speak "Done !", True
End Sub

oh, Dear Marc. You are amazing. Thank you so much. If this gets to work, this would save me 10 hours of my life. First of all, two things:

1. Thank you for the 'kill bill' macro. Made me laugh out loud and made my day. thank you very much
2. Secondly, i like the speech "Done !" at the end. I was very surprised and impressed where it was coming from the first time

Regarding the Macro, i have adjusted it for my file and i do get 1 run-time error:

Sub Macro()
Bill$ = Dir("C:\Users\tomas\Desktop\folder 1\*.jpg")
With CreateObject("Scripting.Dictionary")
While Bill > ""
.Item(Bill) = Empty
Bill = Dir
Wend
Bill = Dir("C:\Users\tomas\Desktop\folder 2\*.jpg")
While Bill > ""
If Not .Exists(Bill) Then Kill Bill
Bill = Dir
Wend
.RemoveAll
End With
Application.Speech.Speak "Done !", True
End Sub

I am right now reviewing files batch 009001-012664. files 009001 and 009002 are not duplicates and macro works correctly. file 009003 is then a duplicate (its only in folder 2, but not in folder 1). Macro does identify it as duplicate but when it goes to kill it, it give me run-time error 53 (no file found). So i am not sure if it is maybe looking for file 009003 to delete in folder 1 instead of folder 2? I tried adjusting directory after Kill Bill to Folder 2, but then excel looped itself in confusion and stopped responding.

While Bill > ""
If Not .Exists(Bill) Then Kill Bill
Bill = Dir("C:\Users\tomas\Desktop\folder 2\*.jpg")

Thank you very much for your help. You are a lifesaver.

 

[Marc L]

My bad as I forgot to include the path so it's time for Kill Bill 2 in my revised demonstration :​

Sub Demo1r()
                Const B = "D:\Folder2\"
              ill2$ = Dir("D:\Folder1\*.jpg")
    With CreateObject("Scripting.Dictionary")
        While ill2 > ""
             .Item(ill2) = Empty
              ill2 = Dir
        Wend
              ill2 = Dir(B & "*.jpg")
        While ill2 > ""
              If Not .Exists(ill2) Then Kill B & ill2
              ill2 = Dir
        Wend
             .RemoveAll
    End With
        Application.Speech.Speak "Done !", True
End Sub

 

[TomExcel1234]

oh my! just tried it and it worked like a charm! all duplicates gone literally not even within 1 second. Unbelievable! Thank you so much. If there is something I can do in return, please, let me know. You are a life saver as you saved me so many hours of work....

and no worries about Kill Bill 2. The movie had two parts anyway so very appropriate too xD

 

[Marc L]

​

In fact your thread was easy, well explained since the initial post, what any forum expects for, it's pretty rare so it changes, a real pleasure !​