Copy a file from one path to another path.

[Mr_RUNDMC]

Hi,

Very new to Excel and generally find lots of help (thanks) but i would like to copy a file from one source folder to another destination folder. The filename; source folder & destination folder are detailed in a given row - each row differs.

I was hoping to place the cursor on the first cell of a given row and then the VBA code would look to (say) C3 for filename; D3 for source folder path; E3 for destination folder path.

Really appreciate any help - thank you.

 

[HighAndWilder]

Place this code in the sheet module.

Practice on a copy of your files.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim oFSO As Object
 
    Set oFSO = CreateObject("Scripting.FileSystemObject")
 
On Error GoTo Err_Handler

    If Target.Count > 1 Or Target.Value = "" Then
    
        Exit Sub
    
    End If
        
    If Target.Row > 1 And Target.Column = 1 Then
      
        If Right(Target.Offset(0, 3), 1) <> "\" Then
            Target.Offset(0, 3) = Target.Offset(0, 3) & "\"
        End If
            
        If Right(Target.Offset(0, 4), 1) <> "\" Then
            Target.Offset(0, 4) = Target.Offset(0, 4) & "\"
        End If
        
        ' Check if Source folder exists.
        If Dir(Left(Target.Offset(0, 3), Len(Target.Offset(0, 3)) - 1), vbDirectory) = "" Then
            MsgBox "The source folder does not exist.", vbInformation, "Warning"
            Exit Sub
        End If
            
        ' Check if Target folder exists.
        If Dir(Left(Target.Offset(0, 4), Len(Target.Offset(0, 4)) - 1), vbDirectory) = "" Then
            MsgBox "The destination folder does not exist.", vbInformation, "Warning"
            Exit Sub
        End If
              
        ' Check to see if the source file exists.
        If Dir(Target.Offset(0, 3) & Target.Offset(0, 2)) = "" Then
            MsgBox "The file does not exist.", vbInformation, "Warning"
        End If
      
        Call oFSO.CopyFile(Target.Offset(0, 3) & Target.Offset(0, 2), Target.Offset(0, 4) & Target.Offset(0, 2), True)
      
    End If

Exit_Handler:

    Exit Sub

Err_Handler:

    If Err.Number = 53 Then
        MsgBox "The file cannot be found.", vbCritical, "Warning"
    End If

    Resume Exit_Handler

End Sub

 

[Mr_RUNDMC]

Hi - really appreciate the above. Sorry, not sure how i initiate the code or the code is initiated? I have only ever used/come across macro driven routines, this being a 'private one' what is going to run it? Thank you (any suggestions as to the best place to get an understanding of the code side - there seems to be a lot of info out there but wondering if there was a known and well constructed site to 'draw you through' the basics?)

 

[HighAndWilder]

This code needs to be placed in the sheet code module.

Right click the mouse on the worksheet tab at the bottom that contains the file names that you want to copy.

Click on View Code.

The Visual Basic Editor (VBE) will be displayed.

Copy and paste the code into the editor on the right.

Click on the Save button.

Close the VBE window using the X on the top right of the screen.

You will now be back to your worksheet.

Click on a cell in column 1 (row 2 or below) on a row that has a file name, source folder and destination folder in it.

The file will then be copied. No confirmation will be made.
If you want a confirmation message to come up then let me know.

Check in the destination folder to see if the file has been copied.
If there is a file in the destination folder with the same name then it will be automatically overwritten.
Let know if you don't want this to happen.

Note : A trailing \ will be added to the source folder and destination in columns D and E respectively automatically
if it does not already exist.

I hope that this helps you in getting this up and running.

 

[Mr_RUNDMC]

Brilliant thanks - works perfectly!But (sorry) can i be a pain and make it a routine that does run from a macro - so i can assign it to a button and the user can select which file to copy? Thank you.

 

[HighAndWilder]

Where is the button to be placed?

How is the user to select which file is to copied?

 

[Mr_RUNDMC]

Hi,

This sheet does various things, all driven by macros that are manually (via link to a button) selected with each function acting on the row the user has updated, added to, gone to. This would be another option.

Thank you

 

[Mr_RUNDMC]

Hi Highandwilder - are you able to help please? So, the user places the cursor on the row containing the file name, path etc. If they want to make a copy then they would hit the copy button linked to this sub routine that copies the file over. Then i would also like to allow the user to copy and save the file as a .pdf format to another 'public folder' (these files are specific guides/policies that people can refer to for help).

Really appreciate your help - thank you

 

[HighAndWilder]

So is the folder into which the PDF is to created to be in column F?

Is the PDF to be created from the same button or are you to have a separate button?

 

[HighAndWilder]

What file format is the file that needs to be saved as a PDF in?

It may be easier to save the source file as a PDF each time that file is saved.