delete specific jpg files from folders and subfolders using Excel VBA

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
Andreas HermleTeam leaderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
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..
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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

0
Andreas HermleTeam leaderAuthor Commented:
Hi Saurabh,

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

Thank you. Regards, Andreas
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Andreas HermleTeam leaderAuthor 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
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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.
0
ProfessorJimJamCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
See what I mean? ;-)
0
Andreas HermleTeam leaderAuthor 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.
0
Andreas HermleTeam leaderAuthor 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
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Exactly. Make sure to change the path (two times) to a test folder, the command will really delete files!
0
Andreas HermleTeam leaderAuthor 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
0
Andreas HermleTeam leaderAuthor 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
0
ProfessorJimJamCommented:
Thank you Andreas for your nice feedback
Wish you a nice day too
Regards
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.