VBA to copy a file from one directory to another

Question

So I have an access file that I regularly need copied 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.

E.g.

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

Is there an easy way of doing this?

Answer 1

This method is even easier if you're ok with fewer options:

FileCopy source, destination 

Answer 2

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://docs.microsoft.com/en-us/office/vba/Language/Reference/user-interface-help/copyfile-method