Thread: Copy and Paste Folder With VBA

HI
it is possible. Need more details to give you specific codes
Ravi

Thanks for the reply

Basically i have a folder of files which are a sort of template. i want to be able to copy the whole folder and rename the folder.

example.

if the folder was c:/myFiles/template/

i want to copy and paste the folder template and rename the folder (again, if possible) using the value in cell A1 (which would probably have a name serial for example).

i would end up with
c:/myFiles/templateRenamed/

i hope that makes sense, if not please just let me know.

Thanks in advance!
cj

Moving a folder is the same as renaming it. No copy is needed.

Here are 2 routines to play with. Be sure to backup your folders before attempting this. Some of these require a trailing "\" and some do not.

Be sure to first add the reference in Tools > Reference, since they use the early binding method.

Sub MoveFolder()
Rem Needs Reference: MicroSoft Script Runtime, scrrun.dll
Rem Instructions: http://support.microsoft.com/default.aspx?scid=kb;en-us;186118
    Dim FSO As Scripting.FileSystemObject
    Dim sSource As String
    Dim sDest As String
     
    Set FSO = New Scripting.FileSystemObject
     
    sSource = Range("A1").Value
    sDest = Range("A2").Value
    FSO.MoveFolder sSource, sDest
End Sub

Sub CopyFolder()
Rem Needs Reference: MicroSoft Script Runtime, scrrun.dll
Rem Instructions: http://support.microsoft.com/default.aspx?scid=kb;en-us;186118
    Dim FSO As Scripting.FileSystemObject
    Dim sSource As String
    Dim sDest As String
     
    Set FSO = New Scripting.FileSystemObject
     
    sSource = Range("A1").Value
    sDest = Range("A2").Value
     
    FSO.CopyFolder sSource, sDest, True
End Sub

Hello cjconner24,

I wasn't sure exactly what you wanted to do, so I created a flexible macro that should do what you need. The macro uses late binding so you don't need to add a library reference to your project. This also allows the code to run directly on other machines. This macro allows you to rename a folder, or copy a folder to a new location, and lets you choose if the files in the folder will be overwritten when the folder is copied. The macro assumes the existence of a destination folder. It will not create one for you.

This macro has 3 arguments. The first argument is required and is the source file path. The second argument, which is optional, is the destination folder path. The source will be copied to the destination and renamed using the text of cell "A1". The third argument, which is optional, determines if the folder will overwrite th files in it. The default is False. You can change the cell that contains the new folder name by changing the variable NewName in the code. All error checking and handling is done in the macro.

Sub CopyRenameFolder(SrcFolderPath As String, Optional DstFolderPath As String, Optional OverWriteFiles As Boolean)

  Dim DstFolder As Object
  Dim FolderPath As String
  Dim FSO As Object
  Dim NewName As String
  Dim SrcFolder As Object
  
    'Initialize variables and objects.
     NewName = Range("A1").Text
     Set FSO = CreateObject("Scripting.FileSystemObject")
     
    'Check the file path string is properly formed.
     If DstFolderPath = "" Then DstFolderPath = SrcFolderPath
     If Right(SrcFolderPath, 1) <> "\" Then SrcFolderPath = SrcFolderPath & "\"
     If Right(DstFolderPath, 1) <> "\" Then DstFolderPath = DstFolderPath & "\"
    
    'Assign the source and destination folders.
     On Error GoTo CopyError
       FolderPath = SrcFolderPath
         Set SrcFolder = FSO.GetFolder(SrcFolderPath)
       FolderPath = DstFolderPath
         Set DstFolder = FSO.GetFolder(DstFolderPath)
     On Error GoTo 0
    
    'Verify the new name exists.
     If NewName = "" Then
       MsgBox "Destination Folder missing New Name.", _
         vbOKOnly + vbCritical, "Folder Not Renamed"
       GoTo Finished
      End If
    
     'Copy source folder to the destination folder if they are different
      If SrcFolderPath <> DstFolderPath Then
        SrcFolder.Copy DstFolderPath, OverWriteFiles
      End If
     
     'Rename the destination folder using the new name.
      On Error Resume Next
        DstFolder.Name = NewName
         'Ignore error if old name is same as new name.
          If Err = 58 Then GoTo Finished
     
CopyError:
        If Err <> 0 Then
          MsgBox Err.Description & vbCrLf & FolderPath, _
          vbOKOnly + vbCritical, "Folder Not Copied"
        End If
    
Finished:
   'Free object from memory
    Set FSO = Nothing
      
End Sub

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

Examples

  'Rename the folder
    $A$1 = "TemplateRenamed"
    CopyRenameFolder "C:\myFiles\template\"

  'Copy folder to new location and rename it using $A$1
    CopyRenameFolder "C:\myFiles\template\", "C:\Temp\"

    Copies the folder "Template" to the folder "Temp" and then renames the folder "TemplateRenamed". The new folder path would be   "C:\Temp\TemplateRenamed".

  'Copy folder to new location, rename it, and overwrite files
    CopyRenameFolder "C:\myFiles\template\", "C:\Temp\", True

Thanks for all the great help. This is exactly what i was after

Hello,

I am trying to accomplish exactly what cjconnor24 was. I have used Leith Ross's code, and it's almost perfect except I can't get it to work exactly how it is supposed to.

When i use the code exactly as written; instead of copying "C:\myfiles\template" to "C:\temp" directory and then renaming the new "template" folder = Range("A1").text, what it does is copy the "C:\myfiles\template" to "C:\temp", and then renames "C:\temp" = Range("A1").text.

Any help on why this is not working correctly for me would really be appreciated.