Results 1 to 14 of 14
  1. #1
    Lounger
    Join Date
    Apr 2014
    Posts
    36
    Thanks
    2
    Thanked 1 Time in 1 Post

    Copy excel files and folder structure from subfolders with vba

    Hi,

    I need to backup a folder structure with the excel files (all the other extensions aren't to copy) from a location to another.

    I'm using the following code, that work just fine for ALL types of files:
    ------------------------------------------------------------
    Sub FullBackupV2()

    Dim FSO As Object
    Dim FileItem As Object
    Dim FromPath As String
    Dim ToPath As String

    'Create backup directory
    MkDir "D:\Full Backup\"

    'Update paths
    FromPath = "C:\Users\John\Working Files"
    ToPath = "D:\Full Backup"

    Set FSO = CreateObject("scripting.filesystemobject")

    If FSO.FolderExists(FromPath) = False Then
    MsgBox FromPath & " doesn't exist"
    Exit Sub
    End If

    If FSO.FolderExists(ToPath) = False Then
    MsgBox ToPath & " doesn't exist"
    Exit Sub
    End If

    FSO.CopyFolder Source:=FromPath, Destination:=ToPath
    MsgBox "Backup completed!"

    End Sub
    ------------------------------------------------------------

    But it gives and error when I'm change the code to copy only the EXCEL files:
    ----------------ERROR 53 File Not Found-------------------------
    Dim FileExt As String 'new line added
    FileExt = "*.xl*" 'new line added
    FSO.CopyFile Source:=FromPath & FileExt, Destination:=ToPath 'line to replace the
    -----------------------------------------------------------------

    What I'm doing wrong? Any help please?

    Many thanks in advance


    LL

  2. The Following User Says Thank You to LuciaLourenco For This Useful Post:

    Maudibe (2014-08-07)

  3. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Does it work if you change to:
    FromPath = "C:\Users\John\Working Files\"
    ToPath = "D:\Full Backup\"

    Steve

  4. #3
    Lounger
    Join Date
    Apr 2014
    Posts
    36
    Thanks
    2
    Thanked 1 Time in 1 Post
    Hi Steve,

    Doesn't work as well... I'm getting the same error 53 "File not found".


    LL

  5. #4
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,348
    Thanks
    48
    Thanked 273 Times in 251 Posts
    Works fine for me with the backslashes added as sdckapr indicates. I am tossing this one into my bag of tricks.

    In folder options, try making sure that "Show hidden files, folders, and drives" is ticked and "Hide extensions for known file types" is unchecked.

    folder options.png

  6. #5
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,348
    Thanks
    48
    Thanked 273 Times in 251 Posts
    I can duplicate your error if I change FileExt = "*.xl*" to FileExt = "*.pdf" to which I have no PDFs in my source folder. Strange behavior providing that you have Excel files present in your source folder.

  7. #6
    5 Star Lounger
    Join Date
    Mar 2014
    Posts
    687
    Thanks
    0
    Thanked 63 Times in 62 Posts
    Just a question: are you using the .xls format of Excel 2003 and earlier or the .xlsx format of Excel 2007 and later, or both? Have you tried using with only one format or the other without using a wild card in the file extension?

  8. #7
    Lounger
    Join Date
    Apr 2014
    Posts
    36
    Thanks
    2
    Thanked 1 Time in 1 Post
    Thanks for the tip Maudibe but I've those configurations as you indicate and I still have the error....

    Any other idea?

    Tks

    LL

  9. #8
    Lounger
    Join Date
    Apr 2014
    Posts
    36
    Thanks
    2
    Thanked 1 Time in 1 Post
    Hi Maudibe

    Yes, that is very stange becuase I have lost of excel files in the folder.

    Even if I change the line code to FileExt = "*.xlsx", I get the same error!

    What is stange is that if I copy the entire folder with "FSO.CopyFolder..." it works fine but if I use the "FSO.CopyFile..." doens't work...

    Any ideas?

    Tks in advance

    LL


    LL

  10. #9
    Lounger
    Join Date
    Apr 2014
    Posts
    36
    Thanks
    2
    Thanked 1 Time in 1 Post
    Hi Berton,

    I'm using only Excel 2007 with .xlsx format.

    And if I use FileExt = "*.xlsx", the error is the same...

    Any ideas?

    Many thanks in advance


    LL

  11. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,476
    Thanks
    211
    Thanked 848 Times in 780 Posts
    LL,

    Try this it works on my Excel 2010 and should work on your 2007. Make sure you change the lines indicated to your information and only those lines.
    Code:
    Option Explicit
    
    Sub FullBackupV2()
    
       Dim FSO      As Object
       Dim FileItem As Object
       Dim FromPath As String
       Dim ToPath   As String
       
       Dim zDestDrive   As String
       Dim zDestDir     As String
       Dim zSourceDrive As String
       Dim zSourceDir   As String
       Dim zFileSpec    As String
       
       '*** Change the following to your values NOTE: NO trailing \ on paths!!! ****
       zDestDrive = "P:"
       zDestDir = "\FullBackup"
       zSourceDrive = "G:"
       zSourceDir = "\BEKDocs\Excel\Test"
       zFileSpec = "*.xl*"
       '*** End of change zone -----------------------------------------------  ****
       
      'Create backup directory
       MkDir zDestDrive & zDestDir
    
       'Update paths
       FromPath = zSourceDrive & zSourceDir
       ToPath = zDestDrive & zDestDir
    
    
       Set FSO = CreateObject("scripting.filesystemobject")
    
       If FSO.FolderExists(FromPath) = False Then
         MsgBox FromPath & " doesn't exist.", vbOKOnly + vbCritical, _
                "Error: Destination NOT Found!"
         Exit Sub
       End If
    
       If FSO.FolderExists(ToPath) = False Then
         MsgBox ToPath & " doesn't exist.", vbOKOnly + vbCritical, _
                "Error: Source NOT Found!"
         Exit Sub
       End If
    
       FSO.CopyFile Source:=FromPath & "\" & zFileSpec, _
                    Destination:=ToPath   'line to replace the
    
       MsgBox "Backup completed!"
    
    End Sub      'FullBackupV2()
    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  12. #11
    5 Star Lounger Browni's Avatar
    Join Date
    Dec 2009
    Location
    Rochdale, UK
    Posts
    902
    Thanks
    17
    Thanked 59 Times in 59 Posts
    I've a suspicion the op's problem stems from the space in the source folder name.

  13. #12
    Lounger
    Join Date
    Apr 2014
    Posts
    36
    Thanks
    2
    Thanked 1 Time in 1 Post
    Almost there RetiredGeek!

    This code copy the excel files in the first directory "Working Files" but doens't copy the excel files inside of the subfolders.

    The source folder structure is like this:
    C:\Users\John\Working Files\*.xlsx
    C:\Users\John\Working Files\City A\*.xlsx
    C:\Users\John\Working Files\City B\*.xlsx
    C:\Users\John\Working Files\City C\*.xlsx
    and so on.....

    The backup in the destination need to have the same folder structure as:
    D:\Full Backup\*.xlsx
    D:\Full Backup\City A\*.xlsx
    D:\Full Backup\City B\*.xlsx
    D:\Full Backup\City C\*.xlsx
    and so on.....

    What I need to add to copy the excel files also in the subfolders?

    Many thanks for the help


    LL

  14. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,476
    Thanks
    211
    Thanked 848 Times in 780 Posts
    LL,

    Ok a little extra level of complication! The FileSystemObject does NOT have a recursion parameter so we have to revert to some good ole' DOS DIR commands. This code will work as long as your structure is only ONE level below the base directory specified in the variable zSourceDir.

    Code:
    Option Explicit
    
    Sub FullBackupV3()
    
       Dim FSO          As Object
       Dim FromPath     As String
       Dim ToPath       As String
       Dim zDestDrive   As String
       Dim zDestDir     As String
       Dim zSourceDrive As String
       Dim zSourceDir   As String
       Dim zFileSpec    As String
       Dim zCurDir      As String
       
       
       '*** Change the following to your values NOTE: NO trailing \ on paths!!! ****
       zDestDrive = "P:"
       zDestDir = "\FullBackup"
       zSourceDrive = "G:"
       zSourceDir = "\BEKDocs\Excel\Test"
       zFileSpec = "*.xl*"
       
       '*** End of change zone -----------------------------------------------  ****
       
       zCurDir = Dir("G:\BEKDOCS\Excel\Test\*.", 20)  'Gets .   Directory
       zCurDir = Dir()                                'Gets ..  Directory
       zCurDir = ""                                   'Reset
       
       Do
       
         'Update Paths
         If zCurDir <> "" Then
           FromPath = zSourceDrive & zSourceDir & "\" & zCurDir
           ToPath = zDestDrive & zDestDir & "\" & zCurDir
         Else
           FromPath = zSourceDrive & zSourceDir
           ToPath = zDestDrive & zDestDir
         End If
    
         MkDir ToPath   'Create Destination folder.
    
         Set FSO = CreateObject("scripting.filesystemobject")
    
         If FSO.FolderExists(FromPath) = False Then
           MsgBox FromPath & " doesn't exist.", vbOKOnly + vbCritical, _
                  "Error: Destination NOT Found!"
           Exit Sub
         End If
    
         If FSO.FolderExists(ToPath) = False Then
            MsgBox ToPath & " doesn't exist.", vbOKOnly + vbCritical, _
                   "Error: Source NOT Found!"
           Exit Sub
         End If
    
         FSO.CopyFile Source:=FromPath & "\" & zFileSpec, _
                      Destination:=ToPath   'line to replace the
                    
         zCurDir = Dir()   'Get Next directory
                    
       Loop Until zCurDir = ""
    
       MsgBox "Backup completed!"
    
    End Sub      'FullBackupV3()
    Note: The code will create all directories below the base directory even if there are no files in there to copy. I tried to code it to check but that takes another Dir command which ruins the chain through the directory structure with the Dir() command.

    You could probably get around this by running the directory tree first and saving it to an array and then use the array to process the loop at which time you could check each directory to see if there files to copy before making the directory and attempting to copy files.

    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  15. #14
    Lounger
    Join Date
    Apr 2014
    Posts
    36
    Thanks
    2
    Thanked 1 Time in 1 Post
    Many thanks RetiredGeek

    I'll do it like that.



    LL

Posting Permissions

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