Results 1 to 10 of 10

VBA macro that copy files from multiple subfolders

Threaded View

  1. #1
    Registered User
    Join Date
    02-07-2016
    Location
    Canada
    MS-Off Ver
    13
    Posts
    65

    Exclamation VBA macro that copy files from multiple subfolders

    Hi everyone,
    I have a VBA for copying images from one folder to another based on image names. You can check macro in work in attached. Code is:

    Option Explicit
    
    Sub CopyFiles()
        Dim iRow As Integer         ' ROW COUNTER.
        Dim sSourcePath As String
        Dim sDestinationPath As String
        Dim sFileType As String
        
        Dim bContinue As Boolean
        
        bContinue = True
        iRow = 2
        
        ' THE SOURCE AND DESTINATION FOLDER WITH PATH.
        sSourcePath = "C:\Users\nhatc_000\Desktop\01010101\"
        sDestinationPath = "C:\Users\nhatc_000\Desktop\02020202\"
        
        sFileType = ".jpg"      ' TRY WITH OTHER FILE TYPES LIKE ".pdf".
       
        ' LOOP THROUGH COLUMN "A" TO PICK THE FILES.
        While bContinue
        
            If Len(Range("A" & CStr(iRow)).Value) = 0 Then    ' DO NOTHING IF THE COLUMN IS BLANK.
                MsgBox "Images have been moved. Thank you!" ' DONE.
                bContinue = False
            Else
                ' CHECK IF FILES EXISTS.
                
                If Len(Dir(sSourcePath & Range("A" & CStr(iRow)).Value & sFileType)) = 0 Then
                    Range("B" & CStr(iRow)).Value = "Does Not Exists"
                    Range("B" & CStr(iRow)).Font.Bold = True
                Else
                    Range("B" & CStr(iRow)).Value = "On Hand"
                    Range("B" & CStr(iRow)).Font.Bold = False
                
                    If Trim(sDestinationPath) <> "" Then
                        Dim objFSO
                        Set objFSO = CreateObject("scripting.filesystemobject")
                        
                        ' CHECK IF DESTINATION FOLDER EXISTS.
                        If objFSO.FolderExists(sDestinationPath) = False Then
                            MsgBox sDestinationPath & " Does Not Exists"
                            Exit Sub
                        End If
                        
                        '*****
                        ' HERE I HAVE INCLUDED TWO DIFFERENT METHODS.
                        ' I HAVE COMMENTED THE SECOND METHOD. TO THE SEE THE RESULT OF THE
                        ' SECOND METHOD, UNCOMMENT IT AND COMMENT THE FIRST METHOD.
                        
                        ' METHOD 1) - USING "CopyFile" METHOD TO COPY THE FILES.
                        objFSO.CopyFile Source:=sSourcePath & Range("A" & CStr(iRow)).Value & _
                            sFileType, Destination:=sDestinationPath
                        
                        ' METHOD 2) - USING "MoveFile" METHOD TO PERMANENTLY MOVE THE FILES.
                        'objFSO.MoveFile Source:=sSourcePath & Range("B" & CStr(iRow)).Value & _
                            sFileType, Destination:=sDestinationPath
                        '*****
                    End If
                End If
            End If
           
           iRow = iRow + 1      ' INCREMENT ROW COUNTER.
        Wend
    End Sub


    However, I need 2 more things to add to this code:
    1. When I enter the name of the file to be copied, I also want to copy files that have the same name PLUS extension _01/_02/.../_07 if those exist.
    2. I want macro to look not only inside specified folder but also in subfolders inside the folder and subfolders inside the subfolder etc.

    Can anyone help?
    Thanks!
    Attached Files Attached Files
    Last edited by alansidman; 02-07-2016 at 09:26 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] list of subfolders in folder - without files and sub-subfolders
    By MartyZ in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-11-2022, 10:56 AM
  2. Needs macro to copy excel files from folders, subfolders to new folder
    By genetist in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-08-2014, 08:53 AM
  3. Macro Code to Encrypt Multiple Excel Files in Folders and Subfolders
    By ali_1989 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-04-2014, 12:53 PM
  4. [SOLVED] Files within Multiple SubFolders and SubFolders Within It
    By codeslizer in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-18-2013, 04:18 AM
  5. copy files from subfolders
    By tryer in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-08-2012, 05:53 PM
  6. import multiple html files from multiple and subfolders
    By wali in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-04-2011, 04:33 PM
  7. copy subfolders, replace text in files and save files in copied subfolders
    By pieros in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-01-2005, 09:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1