Search code examples
vbacopy

VBA to copy a file from one directory to another

I have an access file that I regularly need to copy to another directory, replacing the last version. I would like to use an Excel macro to achieve this, and would also like to rename the file in the process.

   fileName = "X:\Database\oldName.accdb"
   copyDestination = "Y:\dbstore\"
   newName = "newName.accdb"

Is there an easy way of doing this?

Solution

  • Use the appropriate methods in Scripting.FileSystemObject. Then your code will be more portable to VBScript and VB.net. To get you started, you'll need to include:

    Dim fso As Object
Set fso = VBA.CreateObject("Scripting.FileSystemObject")

    Then you could use 

    Call fso.CopyFile(source, destination[, overwrite] )

    where source and destination are the full names (including paths) of the file.

    See https://learn.microsoft.com/en-us/office/vba/Language/Reference/user-interface-help/copyfile-method