delete specific jpg files from folders and subfolders using Excel VBA

Andreas Hermle
Andreas Hermle used Ask the Experts™
on
Dear Experts:

I understand that it is possible to delete files from a folder using Excel VBA

I am running Excel 2010 on Windows 7

I would like to delete specific jpg files from a directory with many folders and subfolders. The directory's name is ...
... C\Graphics\...

The jpg files that are to be deleted are only those that have the following characters at the end:

_h.jpg or _n.jpg, i.e. for example 90-147-58-30_h.jpg or 94-111-24-39_n.jpg. All other jpg files that do not match these criteria are not to be touched.

Help is very much appreciated. Thank you very much in advance.

Kind regards, Andreas
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2015

Commented:
So just to make sure if i understand this correctly you want to delete all the jpg files from folder and sub-folder which ends with h or n?

Is my understanding correct?

Saurabh..
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
This can be done much easier with a DOS command. The scripting approach is useful if you need to apply some smart rules.
del /s C:\Graphics\*_h. jpg C:\Graphics\*_n. jpg

Open in new window

Andreas HermleTeam leader

Author

Commented:
Hi Saurabh,

yes you are right, that is exactly what I want.

Thank you. Regards, Andreas
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Andreas HermleTeam leader

Author

Commented:
Hi Olemo,

thank you very much for your professional help. I am sure that your DOS-command works just fine, but I am afraid to tell you that I use Windows 7 64 bit and I understand that DOS-command won't run on a Windows 7 64 bit system.

I got a workaround (http://windows.microsoft.com/en-us/windows7/install-and-use-windows-xp-mode-in-windows-7) but as a matter of fact I do not want to install anything (Windows XP Mode, and Windows Virtual PC).

Thank you again for your great help. Regards, Andreas
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
You were mislead by my usage of "DOS" - it's used sometimes for the good old-fashioned batch file stuff, as I used it here. I meant "cmd.exe batch". Start a command prompt, and try my code.
Doing that in VBA or VBS requires much more of code.
Microsoft Excel Expert
Top Expert 2014
Commented:
AndreasHermle,

please see attached workbook.

it has the following macro in it.  Two important points to be taken into account before running the macro.

A) you should change the file path from C:\J  to whatever path your folder with jpg files is.
B) if it happens that you copy this code and put it in another workbook module, then you need to add the "Microsoft Scripting Runtime" as Reference   under the Tools> Reference in VBA  otherwise it will not work.   if you see the attached workbook. the "Microsoft Scripting Runtime" reference is already added into that workbook.

Sub DeleteFiles()
    Call RecursiveFolder("C:\J") ''' change the path
End Sub
 
Sub RecursiveFolder(MyPath As String)
 
    Dim FileSys As FileSystemObject
    Dim objFolder As Folder
    Dim objSubFolder As Folder
    Dim objFile As File
 
    Set FileSys = CreateObject("Scripting.FileSystemObject")
    Set objFolder = FileSys.GetFolder(MyPath)
 
    For Each objFile In objFolder.Files
        If Left(Right(objFile.Name, 5), 1) = "h" Or Left(Right(objFile.Name, 5), 1) = "n" Then
            objFile.Delete
        End If
    Next objFile
 
    For Each objSubFolder In objFolder.SubFolders
        RecursiveFolder MyPath & "\" & objSubFolder.Name
    Next objSubFolder
 
    Set FileSys = Nothing
    Set objFolder = Nothing
    Set objSubFolder = Nothing
    Set objFile = Nothing
 
End Sub

Open in new window

Main.xlsm
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
See what I mean? ;-)
Andreas HermleTeam leader

Author

Commented:
Hi Qlemo, I am currently trying your DOS command.

Am I right, I press the Windows Key + R to bring up the Run Window. And then I enter cmd.exe to launch the command prompt. The window in the command prompt says: C:\Users\Andreas>

I then enter your command prompt right after the 'greater than' sign, am I right?

Regards, andreas and thank you very much for your help.
Andreas HermleTeam leader

Author

Commented:
Hi ProfJimJam,

great coding, I am really impressed, works just fine. Thank you very much for it.

I will try Qlemo's command prompt and then award the points.

Again, thank you very much for your swift and professional support. Regards, Andreas
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
Exactly. Make sure to change the path (two times) to a test folder, the command will really delete files!
Andreas HermleTeam leader

Author

Commented:
Qlemo, thank you very much for your help. Will make the testing this evening, hence it'll be tonight when I award points.

ProfJimJam, again thank you very much for your superb code.

Regards, Andreas
Andreas HermleTeam leader

Author

Commented:
Dear Qlemo,

works really great, thank you very much for your professional support. I really appreciate it.

Dear Qlemo and ProfJimJam,

Since I was initially asking for a VBA solution, I will award more points to ProfJimJam.

You both deserve 500 points, but regrettably this is not possible. Again, thank you very much for your superb, swift and professional help. This forum has saved me days and months of tedious and exhausting work.

Have a nice day, regards, Andreas
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
Thank you Andreas for your nice feedback
Wish you a nice day too
Regards

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial