Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Macro to move file from one folder to another

    I have attached a worbook containing a macro to move data from one folder to another. Where there is an existing workbook in the destination folder, this must not be overwritten.

    It would be appreciated if someone could please assist me
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Howard

    ..try testing this version.
    I added a test to check if the file was already in the specified destination folder.
    The file will now only be moved if it isn't already in the destination folder.

    zeddy
    •Compliance Policy Instructor
    .
    Attached Files Attached Files

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks very much. If I only want to copy the files from one Folder to another and not move these, please amend that portion of the code

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Howard

    ..why not have a go using previous examples on this topic?
    If you get stuck, we can help you.
    The idea is that you learn from the stuff.
    ..we don't just want to do it all for you.

    zeddy
    •Carbon Accounting Support
    .

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 648 Times in 591 Posts
    Howard,

    Take a look at this very easy method to help amend the code:
    http://www.rondebruin.nl/win/s3/win026.htm

  6. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Agree 100%. Will attempt and get back to you if I get stuck

    Howard

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

    I have the following code to copy data from one folder to another

    It would be appreciated if you would kindly amend this so that the files are not overwritten


    Code:
     Sub Copy_Files_To_New_Folder()
         
         
        Dim objFSO As Object, objFolder As Object, PathExists As Boolean
        Dim objFile As Object, strSourceFolder As String, strDestFolder As String
        Dim x, Counter As Integer, Overwrite As String
         
        Application.ScreenUpdating = False
        Application.EnableEvents = False
         
         
        strSourceFolder = "C:\Vat Reports.*" 'Source path
        strDestFolder = "C:\Old Vat Report" 'destination path, does not have to exist prior to execution
         
         
        On Error Resume Next
        x = GetAttr(strDestFolder) And 0
        If Err = 0 Then 'if there is no error, continue below
            PathExists = True 'if there is no error, set flag to TRUE
            Overwrite = MsgBox("The folder may contain duplicate files," & vbNewLine & _
            "Do you wish to overwrite existing files with same name?", vbYesNo, "Alert!")
             
            If Overwrite <> vbYes Then Exit Sub 'if the user clicks YES, then exit the routine..
    Else: 'if path does NOT exist, do the next steps
            PathExists = False 'set flag at false
            If PathExists = False Then MkDir (strDestFolder) 'If path does not exist, make a new one
        End If 'end the conditional testing
         
        On Error GoTo ErrHandler
        Set objFSO = CreateObject("Scripting.FileSystemObject") 'creates a new File System Object reference
        Set objFolder = objFSO.GetFolder(strSourceFolder) 'get the folder
        Counter = 0 'set the counter at zero for counting files copied
         
        If Not objFolder.Files.Count > 0 Then GoTo NoFiles 'if no files exist in source folder "Go To" the NoFiles section
         
        For Each objFile In objFolder.Files 'for every file in the folder...
             
             If LCase(objFSO.GetExtensionName(objFile.Path)) Like "csv*" Then
                objFile.Copy strDestFolder & "\" & objFile.Name 'Copy file
                Counter = Counter + 1
             End If
             
        Next objFile
         
        MsgBox "All " & Counter & " Files from " & vbCrLf & vbCrLf & strSourceFolder & vbNewLine & vbNewLine & _
        " copied/moved to: " & vbCrLf & vbCrLf & strDestFolder, , "Completed Transfer/Copy!"
         
        Set objFile = Nothing: Set objFSO = Nothing: Set objFolder = Nothing 'clear the objects
         
        Exit Sub
         
    NoFiles:
         'Message to alert if Source folder has no files in it to copy
        MsgBox "There Are no files or documents in : " & vbNewLine & vbNewLine & _
        strSourceFolder & vbNewLine & vbNewLine & "Please verify the path!", , "Alert: No Files Found!"
        Set objFile = Nothing: Set objFSO = Nothing: Set objFolder = Nothing 'clear the objects
         
        Application.ScreenUpdating = True 'turn screenupdating back on
        Application.EnableEvents = True 'turn events back on
         
        Exit Sub 'exit sub here to avoid subsequent actions
         
    ErrHandler:
         'A general error message
        MsgBox "Error: " & Err.Number & Err.Description & vbCrLf & vbCrLf & vbCrLf & _
        "Please verify that all files in the folder are not currently open," & _
        "and the source directory is available"
         
        Err.Clear 'clear the error
        Set objFile = Nothing: Set objFSO = Nothing: Set objFolder = Nothing 'clear the objects
        Application.ScreenUpdating = True 'turn screenupdating back on
        Application.EnableEvents = True 'turn events back on
    End Sub

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Howard

    ..try this code:
    Code:
    Sub copyFilesToNewFolder()                          'v1a zeddy
    
    Set fso = CreateObject("scripting.FileSystemObject")    'use late binding (doesn't need Reference to be set)
    
    '*******************************************
    'DEFINE FOLDERS HERE..                              '*** INCLUDE LAST \ CHAR IN FOLDER NAME ***
    '*******************************************
    zFromFolder = "C:\Vat Reports\"                     '<< copy-from folder; e.g. "C:\Pull\"
    zToFolder = "C:\Old Vat Report\"                    '<< copy-to folder; e.g. "e:\testReports\"
    
    '*******************************************
    'CHECK FOLDERS EXIST..
    '*******************************************
    If fso.folderexists(zFromFolder) = False Then       'source folder missing
    MsgBox zFromFolder & " doesn't exist."              'display message
    Exit Sub                                            'nothing else to do
    End If                                              'end of test for source folder
    
    If fso.folderexists(zToFolder) = False Then         'destination folder missing
    MkDir zToFolder                                     'create folder
    End If                                              'end of test for destination folder
    '*******************************************
    'CHECK IF FILE OVERWRITE ALLOWED..
    '*******************************************
    saywhat = "The folder may contain duplicate files, " & vbCr                     'message text
    saywhat = saywhat & "Do you wish to overwrite existing files with same name?"   'add text
    saywhat = saywhat & vbCr & vbCr                                                 'add 2 lines
    btns = vbYesNo + vbQuestion + vbDefaultButton2      'message box buttons
    boxtitle = "Alert!"                                 'message box heading
    answer = MsgBox(saywhat, btns, boxtitle)            'display message box
    
    If answer = vbYes Then Overwrite = True             'set overwrite flag
    '*******************************************
    
    Set zSourceFolder = fso.GetFolder(zFromFolder)      'define shortcut
    
    zCounter = 0                                        'set file counter to zero
    For Each zFile In zSourceFolder.Files               'loop through all files in folder
    zFile = zFile.Name                                  'fetch filename
    If zFile Like "*.txt" Then                          'check for csv file
        zSourceFile = zFromFolder & zFile               'full copy-from path and filename
        zDestFile = zToFolder & zFile                   'full copy-to path and filename
        If Dir(zDestFile) = "" Then                     'file NOT already there..
        FileCopy zSourceFile, zDestFile                 'copy file to destination folder
        zCounter = zCounter + 1                         'increment number of files copied
        Else                                            'existing file found, so check..
        If Overwrite = True Then                        'replace existing file
        On Error Resume Next                            'skip if error encountered
        Kill zDestFile                                  'delete file prior to copy
        FileCopy zSourceFile, zDestFile                 'copy file to destination folder
        zCounter = zCounter + 1                         'increment number of files copied
        On Error GoTo 0                                 'reset error trap
        End If
        End If
    End If                                              'If zFile Like "*.csv"
    Next                                                'process next file in folder
    
    '**************************************
    'DISPLAY COMPLETION MESSAGE..
    '**************************************
    saywhat = "DONE!" & vbCr & vbCr                                         'message text
    saywhat = saywhat & "All " & zCounter & " Files from " & vbCr & vbCr    'add text
    saywhat = saywhat & zFromFolder & vbCr & vbCr                           'add text
    saywhat = saywhat & " copied/moved to: " & vbCrLf & vbCrLf & zToFolder  'add text
    saywhat = saywhat & vbCr & vbCr                     'add 2 lines
    btns = vbOKOnly + vbInformation                     'message box buttons
    boxtitle = "Completed Transfer/Copy!"               'message box heading
    answer = MsgBox(saywhat, btns, boxtitle)            'display message box
    End Sub
    '********************************************************************************
    zeddy
    •Infusion Motors Mechanic

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

    HowardC (2015-07-21)

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

    Thanks very much for the help


    Regards

    Howard

  11. #10
    New Lounger
    Join Date
    Jul 2015
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi, I am trying to move xls files from one folder to another so that they can be picked up as part of a FTP process.

    I have thousands of files however i need to move them one at a time with a timed interval in between (10 mins), so they are processed all day rather than one at a time. Is there a way of doing this???

  12. #11
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Stotter33


    Attached please find a workbook, where the code was kindly supplied by Zeddy, which I amended slightly

    You will need to change the change the source folder as well as the destination folder, where I have indicated on sheet1

    Hope this helps
    Attached Files Attached Files

  13. #12
    New Lounger
    Join Date
    Jul 2015
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for this ....I have setup 2 test folders as per below:

    C:\Users\xxx\Desktop\to
    C:\Users\xxx\Desktop\From (files added here)

    have added these into the required fields on the spreadsheet, with a file type of *.xls however when I select "move files" they are deleted from folder "from" and I can't locate them on my PC anywhere? Can it be amended so they move please?

    Also I was looking to have these files move indiviually with a time interval (that I could select) not all at once ...however I can't see this in the source code. Can you help with this please...its very much appreciated :-)

  14. #13
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi

    Thanks for the reply. I am sorry that I cannot help further as I not sure how accomodate your requirements. If you send Zeddy a private message, I am certain he will be able to amend the code to suit your needs as he wrote the code for me or one of the other experienced VBA programmers should easily be able to help

  15. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts

    macro to move files to a folder in timed intervals

    Hi stotter

    No need to send private messages!
    I prefer to respond in this Lounge, so everyone can see and contribute.

    ..I've prepared a file, but I still need to run tests before posting here.
    I've included buttons to select Source and Destination folders, list matching files to be moved, a cell to specify the time interval, and a [Start] and [Stop] button.

    With so many files, a file may get deleted or moved by the time it is due to be moved.
    ..or another User may have one of the files currently 'open', so it cannot be moved.
    ..or a file may be currently 'shared'
    etc etc etc

    I'm assuming that if a file with the same name is already in the destination folder, it will be overwritten without warning??

    zeddy
    •Excel Cricket Squad
    Last edited by zeddy; 2015-07-30 at 12:37.

  16. #15
    New Lounger
    Join Date
    Jul 2015
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Many thanks Zeddy and no problem will post on lounge in future.

    It won't necessarily be overwritten the file destination folder will have a FTP process running so they will get automatically get moved out....what i'm trying to stagger is the transfer into this folder.

Page 1 of 2 12 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
  •