Results 1 to 14 of 14
  1. #1
    Lounger
    Join Date
    Apr 2014
    Posts
    32
    Thanks
    1
    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. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

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

    Maudibe (2014-08-07)

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

    Steve

  5. #3
    Lounger
    Join Date
    Apr 2014
    Posts
    32
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Steve,

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


    LL

  6. #4
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,107
    Thanks
    39
    Thanked 197 Times in 184 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

  7. #5
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,107
    Thanks
    39
    Thanked 197 Times in 184 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.

  8. #6
    3 Star Lounger
    Join Date
    Mar 2014
    Location
    Forever West
    Posts
    359
    Thanks
    0
    Thanked 24 Times in 24 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?

  9. #7
    Lounger
    Join Date
    Apr 2014
    Posts
    32
    Thanks
    1
    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

  10. #8
    Lounger
    Join Date
    Apr 2014
    Posts
    32
    Thanks
    1
    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

  11. #9
    Lounger
    Join Date
    Apr 2014
    Posts
    32
    Thanks
    1
    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

  12. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,058
    Thanks
    196
    Thanked 764 Times in 698 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


  13. #11
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Rochdale, UK
    Posts
    824
    Thanks
    13
    Thanked 52 Times in 52 Posts
    I've a suspicion the op's problem stems from the space in the source folder name.

  14. #12
    Lounger
    Join Date
    Apr 2014
    Posts
    32
    Thanks
    1
    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

  15. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,058
    Thanks
    196
    Thanked 764 Times in 698 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


  16. #14
    Lounger
    Join Date
    Apr 2014
    Posts
    32
    Thanks
    1
    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
  •