Excel VBA connected to external files

TomExcel1234

New Member
Joined
May 17, 2021
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,563
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
May 17, 2021
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Apr 5, 2021
Messages
1,563
Office Version
  1. 2010
Platform
  1. Windows
As after all here it's a VBA forum so a demonstration for starters :​
VBA Code:
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

New Member
Joined
May 17, 2021
Messages
10
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

As after all here it's a VBA forum so a demonstration for starters :​
VBA Code:
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.

1621344488608.png
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,563
Office Version
  1. 2010
Platform
  1. Windows
My bad as I forgot to include the path so it's time for Kill Bill 2 in my revised demonstration :​
VBA Code:
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
 
Solution

TomExcel1234

New Member
Joined
May 17, 2021
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Apr 5, 2021
Messages
1,563
Office Version
  1. 2010
Platform
  1. Windows
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 !​
 

Forum statistics

Threads
1,143,837
Messages
5,721,088
Members
422,339
Latest member
SHIVATVM

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
Top