Page 1 of 3 123 LastLast
Results 1 to 15 of 37
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts

    Macro to move files from one folder to another

    I have a macro to move files from C:\Journal Templates to C:\old JNLS

    However when activating the macro, it comes up with a message"file already exists" and the following code is highlighted - FSO.moveFile Source:=FromPath & FileExt, Destination:=ToPath . All I want to do is to move the files in folder "C:\Journal Templates" to"C:\old JNLS

    Your assistance in resolving this is most appreciated



    Code:
    Sub Move_JNL_Folder()
    
        Dim FSO As Object
        Dim FromPath As String
        Dim ToPath As String
        Dim FileExt As String
    
        FromPath = "C:\Journal Templates"
        ToPath = "C:\old JNLS"
    
    
        FileExt = "*.*" 
    
        If Right(FromPath, 1) <> "\" Then
        FromPath = FromPath & "\"
        End If
    
        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.moveFile Source:=FromPath & FileExt, Destination:=ToPath
        MsgBox "You can find the files from " & FromPath & " in " & ToPath
    
        End Sub
    http://www.mrexcel.com/forum/excel-q...ms-folder.html
    Last edited by RetiredGeek; 2013-11-14 at 21:18. Reason: Fix tags to Code vs HTML

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I believe with FSO it is not possible to move a file if the file already exists in the destination. If the files may already exist (as you seem to be doing with your code) then you must first copy the files (setting the overwrite parameter on the copy) and then delete (the old files)

    For further info on FSO methods see http://technet.microsoft.com/en-us/l.../ee176983.aspx

    Steve

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Howard

    As Steve says, you can't use FSO if the file already exists in the destination folder.
    In addition, you can't use FSO if any of the files are currently open.
    In addition, you can't use FSO if the file you are using (i.e containing the code) is one of the source files.

    I have attached a workbook containing a moveFile routine.
    This routine uses the very efficient vba Name function to move files.

    If you imagine that all of the files you want to move are in your house, the VBA Name function makes moving house simple by just changing the number on your mailbox. Copy file and Kill methods require you to 'load' a big truck and 'unload' the truck at the new address. That can take a while.

    My routine skips any files that are currently open, and also allows you to specify combinations of types of files to be moved by file extension (e.g. *.xls*, *.doc*, *.csv) etc.

    zeddy
    Attached Files Attached Files

  4. #4
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Zeddy & Steve

    Thanks for your explanations. Zeddy thanks for the code, it works perfectly

    Regards

    Howard

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    It would be appreciated if you could amend your code to overwrite the files in the destination folder as as use the same file name each month to copy from the source folder to the destination folder

    Regards

    Howard

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Howard

    In the attached file, I added a vba line to 'Kill' the file in the destination folder before the move.
    The 'on error resume next' will ignore the Kill command if the file being moved isn't in the destination folder.
    I haven't tested it, but I'm sure it will do the trick.
    This version does not deal with any files that are currently open, but a further amendment could automatically close any such files if required. The only requirement would be whether the opened file should be saved or not saved before closing.

    Please test and let me know if the attached version does what you require.

    zeddy
    Attached Files Attached Files

  7. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    It works perfectly-thanks very much

    Regards

    Howard

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Howard

    That's twice you said that.
    Nothing's perfect.
    Mostly.


    zeddy

  9. The Following User Says Thank You to zeddy For This Useful Post:

    HowardC (2013-08-18)

  10. #9
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    It worked perfectly the first time as I has no files in the destination folder. I have tested your macro several times and it works each time without any problem

    Regards

    Howard

  11. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Howard

    Now I follow. Thanks for the explanation.

    zeddy

  12. #11
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    A while back you assisted me to write code to move files from one folder to another. I now have a situation where I want to copy all xls files from folder "C:\Pull" including subdirectories in Pull to "C:\Summary Profit reports"

    It would be appreciated if you would amend your code accordinly

  13. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Howard

    I've been away.

    What do you want to happen if you have multiple copies of a particular filename zzzzzz.xls in several subdirectories of C:\Pull ?????
    For example, you could just overwrite the C:\Summary Profit reports\zzzzzz.xls with whatever the last one found

    zeddy

  14. #13
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    I thought that you may have neen away. Did you have a bit of a holiday? The xls files that are copied from C:\Pull + subdirectories (including C:\workfile\z_ACCNTS (forgot mention this in earlier post) are to be copied to C:\Summary Profit reports. Existing Xls files in C:\Summary Profit reports can be overwritten when the files are copied accross

    Howard

  15. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Howard

    I had a nice trip to New York.
    Thanks for asking.

    Now, I have attached a file which should do as you want.
    Let me know if this works for you.

    zeddy
    Attached Files Attached Files

  16. #15
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Glad you had a nice trip. My next holiday, I definately want to visit NY.

    Thanks for the help. Kindly amend your code to copy the files and NOT to move these

    Howard

Page 1 of 3 123 LastLast

Posting Permissions

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