Move all files from a folder to the most recently created sub folder in another destination

  • Dear members,


    I am really new to this so have been tearing my hair for the last 3 days ;(, trying to achieve something.

    There is a source folder in which we create files (.jpg, .pdf, .tiff). That folder is always the same and it doesn't contain subfolders.

    The destination folder is a bit more complex:

    It is created by using the macro from my other Thread here (works very well but needs some cosmetic tweaking),

    That existing macro is attached to a button and when a cell is selected (in column containing names of companies) and button pushed, it creates in a folder "Customers" a subfolder with the mane of that customer plus two sub_sub folders, Jobsheets" being the most recently created sub_sub folder.

    The structure looks like that:

    Code
    1. Customers
    2. └───Company 1
    3. │ └───Orders
    4. │ └───Jobsheets
    5. └───John Smith
    6. │ └───Orders
    7. │ └───Jobsheets
    8. ```

    So the sub folder .../Customers/ John Smith/ Jobsheets is the latest one.

    What I need is addition to the existing macro that moves all files form the source to the latest created /Jobsheets sub_subfolder, doesn't matter in which customers' name folder it is.


    The purpose for all that is:

    We have a big number of paper jobsheets that need scanning and a spreadsheet containing the customers names.

    We scan few jobsheets for Company 1 to folder called scans. Then we press the button inside the spreadsheet, it creates 3 folders for that customer, and moves all scanned docs to the latest created one. Then we repeat the procedure for the next customer, John Smith.

    Thanks in advance, that will clear a big mess that I have inherited.

  • Hello,

    I don't have access to the machine where that file is but I am using a variation of this Macro:

    It also makes a hyperlink in the chosen cell.

  • Try this on some test data


  • It works! It works! Thank you very much yongle!

    My problem is resolved.


    I did notice that if we run the action and already have the folders generated but there was nothing in the source, and then we scan things and run it again, it breaks it. If the folders are deleted and the hyperlink removed though, then it works again. That is not concern because our routine needs to be the same and if we follow it, it works absolutely flawlessly.

    Some extras that I think might make it more awesome:

    Could be beneficial if instead of the hyperlink being created on the text in the selected cell, to be in another cell, let's say in D column on the same row.

    Also to see the resulting path visible as text: "C:\Users\"Username\Desktop\Customers\John Smith" still hyperlinked but just to the customer's name level, not the subfolders .

    The last thing was my original request form the other Thread, is it possible to add the name of the customer to the names of the folders "Orders" and "Customers".


    Kindest Regards!


    P.S. Just a thought, I have nor tried it with network folders, have to do that at work, but even if it doesn't work, we can always run it locally on the server's Excel and point to the relevant folders there!

  • I did notice that if we run the action and already have the folders generated but there was nothing in the source, and then we scan things and run it again, it breaks it. If the folders are deleted and the hyperlink removed though, then it works again. That is not concern because our routine needs to be the same and if we follow it, it works absolutely flawlessly.

    Before moving on to your "added extras", let's resolve the above

    1. Run MoveFiles when the sub-folder is created

    - is this already working flawlessly?


    2. Run MoveFiles again later

    Additional macro required containing this line

    Call MoveFiles("Here Enter the source folder path ending path with \","Here Enter the destination folder path ending path with \")

    The source path is constant

    The destination path is fixed EXCEPT for customer name ( DestinationFolderPath & "Customer Name" & "\Jobsheets" )

    - would a line in the macro asking the user to click on the cell containing CUSTOMER name work?

    - is there only one sheet in the workbook? (if not which sheet contains customer names matching folder names ?)


    Let me know your thoughts

  • Thanks for your persistence.

    Yes, the way you have done it works perfectly and it is usable. I am really happy. From my perspective it works flawlessly.

    The behaviour that I have mentioned is observed only if we don't follow the routine of Operator to scan files, then select cell, click button -> Excel creates folders, subfolders, creates hyperlink, moves files, etc.

    I noticed it when I forgot to put files into the source. Then it makes the actions but no files are moved. When clicked again, even if we put files in the source it doesn't work because we have few things not right. One being that we already have the folders and subfolders created and another being the hyperlink generated. If need be, I will just delete the folders, clear hyperlink and it works. That is OK to me because prevents errors as folder replacement, overwriting etc. Also after we do that procedure once we will never do it again for any of the existing customers. This is just to digitise existing (thousands) of jobsheets written by hand, there is valuable information on them like specs, prices etc. After we finish, all will be done via CRM system.

    Regards, Georgi

  • The behaviour that I have mentioned is observed only if we don't follow the routine of Operator to scan files, then select cell, click button -> Excel creates folders, subfolders, creates hyperlink, moves files, etc.

    I noticed it when I forgot to put files into the source.

    You could prevent the folders being created if the source folder is empty when the macro is run

    Put something like this early in the Command Button procedure

    Code
    1. If Dir("folder\subfolder\sourcefolder\*.*") = "" Then
    2. MsgBox "Source contains no visible files"
    3. Exit Sub
    4. End If
    5. MsgBox "carry on"
    6. code to create folders goes here
  • Could be beneficial if instead of the hyperlink being created on the text in the selected cell, to be in another cell, let's say in D column on the same row.

    Also to see the resulting path visible as text: "C:\Users\"Username\Desktop\Customers\John Smith" still hyperlinked but just to the customer's name level, not the subfolders

    Test this

    Code
    1. replace:
    2. ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell, Address:=FolderPath & ActiveCell.Value
    3. with:
    4. ActiveSheet.Hyperlinks.Add Anchor:=Range("D" & ActiveCell.Row), Address:=FolderPath & ActiveCell, TextToDisplay:=FolderPath & ActiveCell
  • The last thing was my original request form the other Thread, is it possible to add the name of the customer to the names of the folders "Orders" and "Jobsheets"

    Test this

    Code
    1. replace:
    2. MkDir FolderPath & ActiveCell.Value & "\Orders"
    3. with:
    4. MkDir FolderPath & ActiveCell.Value & "\Orders" & " - " & ActiveCell.Value
    5. replace:
    6. MkDir FolderPath & ActiveCell.Value & "\Jobsheets"
    7. with:
    8. MkDir FolderPath & ActiveCell.Value & "\Jobsheets" " - " & ActiveCell.Value
  • Another solution...

    If I've been helpful, let me know. If I haven't, let me know that too.

  • Test this

    Code
    1. replace:
    2. ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell, Address:=FolderPath & ActiveCell.Value
    3. with:
    4. ActiveSheet.Hyperlinks.Add Anchor:=Range("D" & ActiveCell.Row), Address:=FolderPath & ActiveCell, TextToDisplay:=FolderPath & ActiveCell

    This works well, thanks.

  • Another solution...

    Thank you dangelor,

    This works as per my original request in this thread with the exception that it copies the folder scans to customers/nameofcustomer/ as scans and then renames it to jobsheets. That result is not suitable because we need all the content of scans to be moved to jobsheets. If I replace

    Code
    1. FSO.CopyFolder SourcePath, FolderPath & ActiveCell.Value
    2. With
    3. FSO.MoveFolder SourcePath, FolderPath & ActiveCell.Value

    it works but removes the folder scans from the Desktop. That means that for the next cycle of scans we will need to create it again so the scanner send them there automatically.

    Interesting solution though, quite spoilt for choice with two different ways to resolve the same problem.

  • You could prevent the folders being created if the source folder is empty when the macro is run

    Put something like this early in the Command Button procedure

    Code
    1. If Dir("folder\subfolder\sourcefolder\*.*") = "" Then
    2. MsgBox "Source contains no visible files"
    3. Exit Sub
    4. End If
    5. MsgBox "carry on"
    6. code to create folders goes here

    That also works well, thank you!

  • Thanks again, a summary of my observations:


    This one works:

    MkDir FolderPath & ActiveCell.Value & "\Orders" & " - " & ActiveCell.Value



    This one returns Compile Error: Expected name of statement.

    MkDir FolderPath & ActiveCell.Value & "\Jobsheets" " - " & ActiveCell.Value


    Changed to:

    Code
    1. MkDir FolderPath & ActiveCell.Value & "\Jobsheets" & " - " & ActiveCell.Value


    So it creates the subfolders "Scans - nameofcustomer" but it doesn't move the actual scanned files from the source.

    The error is "File not found" and it highlights Name oldPath & StrFile As newPath & StrFile

    I imagine that the string insertion breaks the path.


  • The new path name is now inconsistent because of adding the customer name to the folder names


    Name oldPath & StrFile As newPath & StrFile


    If need help in correcting the string, please post the whole of your amended code

  • This is the code so far, :

  • The code should now do exactly what you want

    Variables used to reduce repitition in the code