Copying jpeg files located in different folders using an Excel macro

Dear Experts:

I got thousands of jpeg files nested into subfolders such as:

C:\Catalogue\MyGraphics\10\10-150\10-150-00-07\JPEG\10-150-00-07.jpg
C:\Catalogue\MyGraphics\10\10-170\10-170-00-07\JPEG\10-170-00-07.jpg
C:\Catalogue\MyGraphics\20\20-190\20-190-00-08\JPEG\20-190-00-08.jpg
etc.

I wonder whether an Excel macro is capable of looping through all these subfolders and copy all the jpg-files into a folder on Drive D with the following path: D:\My_JPEG_Graphics\

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

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.

tel2Commented:
Hi AndreasHermle,

I'm not planning to answer this question, but I think it would be good for you to state why you want this done in Excel, and whether you would mind having a batch file or PowerShell script instead, because they are probably more appropriate.  Also, is this to be for one-off or regular use?

Thanks.
0
Andreas HermleTeam leaderAuthor Commented:
Hi tel,

thank you very much for your swift response.

If a batch file or PowerShell script is able to tackle this task, the better. In the past I got so many tasks done by running an excel macro (creating folders from cell entries, deleting specific files in the operating system etc.), that I thought that my current problem could also be solved by running an excel macro.

Hence if my macro requirements do not exceed the scope of this forum, it would be great if you could come up with some type of batch file for my task.

Thank you very much

Regards, Andreas
0
Andreas HermleTeam leaderAuthor Commented:
Hi tel,

I forgot to tell you that this will be for regular use.

Thank you
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try something like this....

Place the following code on a Standard Module (say Module1) and save your workbook as Macro-Enabled Workbook.

You may insert a button on Sheet1 and assign this code to that button and may click to run the code or you may directly run the code from the module itself.

To make this code work, you will need to add reference to the Microsoft Scripting Runtime Library.

Library Reference Required: Microsoft Scripting Runtime



Here is the code....
Dim fso As Scripting.FileSystemObject
Dim fil As Scripting.File
Dim sFolderPath As String
Dim dFolderPath As String

Sub ChooseTargetFolderForFiles()
Application.ScreenUpdating = False
DoEvents
Set fso = New Scripting.FileSystemObject

sFolderPath = "C:\Catalogue\MyGraphics"
dFolderPath = "D:\My_JPEG_Graphics\"
Call WorkOnEachFileInFolderAndSubFolder(sFolderPath)

Application.ScreenUpdating = True
MsgBox "Task Complete!", vbInformation

End Sub

Sub WorkOnEachFileInFolderAndSubFolder(StartFolderPath As String)
    
    Dim SourceFolder As Scripting.Folder
    Dim SubFolder As Scripting.Folder
    Set SourceFolder = fso.GetFolder(StartFolderPath)
    
    Application.DisplayAlerts = False
    For Each fil In SourceFolder.Files
        If Left(fso.GetExtensionName(fil.Path), 2) = "jp" Then
            fil.Copy dFolderPath
        End If
    Next fil
    For Each SubFolder In SourceFolder.SubFolders
        Call WorkOnEachFileInFolderAndSubFolder(SubFolder.Path)
    Next SubFolder
    Application.DisplayAlerts = True
End Sub

Open in new window


For details refer to the attached workbook.
Copy-All-JPEG-Files-From-One-Folder-To-A
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
Andreas HermleTeam leaderAuthor Commented:
Hi sktneer,

thank you very much for your post. I will do the testing this evening and then get back to you with a feedback.

Regards, andreas
0
Andreas HermleTeam leaderAuthor Commented:
Wow, sktneer, I am really deeply impressed. An initial test produced the desired results.

I will do some more testing, but I am sure subsequent tests will produce the same results.

Again thank you very much for your professional help. I really appreciate it.

Regards, Andreas
0
Andreas HermleTeam leaderAuthor Commented:
Hi sktneer, this is really a superb code which saves me hours and days of work. I am so glad.

Thank you very much for your great, swift and professional help. I really appreciate it.

Regards, Andreas
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Glad to help.
0
[ fanpages ]IT Services ConsultantCommented:
Andreas:

Sorry I missed your question.

My solution (in response to your previous question about deleting folders) could easily have been modified to copy the files:

[ http://www.experts-exchange.com/questions/28694077/Delete-multiple-subfolders-in-Windows-Explorer-using-Excel-VBA-Excel-2010-running-on-Windows-7-Enterprise.html#a40862801 ]
0
Andreas HermleTeam leaderAuthor Commented:
Hi fanpages,

sorry for not getting back to you earlier. Thank you very much for this follow-up, this is very kind.

Would that be asking too much if you could modify the code to accommodate the new requirements as you suggested in your post? It would be great to have another approach ;-)

Kind regards, Andreas
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.