VBA to copy a file from one directory to another

Question

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?

By regularly do you mean once every x minutes? – will Jun 05 '13 at 14:50 — will, Jun 05 '13 at 14:50
No, I mean like a few times a day – harryg Jun 05 '13 at 14:52 — harryg, Jun 05 '13 at 14:52

score 81 · Accepted Answer · edited Oct 18 '19 at 23:53

81

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

edited Oct 18 '19 at 23:53

Darren Bartrup-Cook

18,362
1
23
45

answered Jun 05 '13 at 14:54

Bathsheba

231,907
34
361
483

4

Compared to the other, simple solution with FileCopy, this solution seems to have the advantage that the source file can also be copied while it is opened (e.g. by MS Access), i.e. when it's "read-only". – Jörg Brenninkmeyer Dec 01 '17 at 08:51
1

It says Syntax Error while using fso.CopyFile ........ @Bathsheba – paran May 21 '20 at 17:12
@paran just remove the parentheses – fourthquartermagic Sep 19 '22 at 17:14

score 81 · Answer 2 · edited Nov 08 '17 at 21:01

81

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

FileCopy source, destination

edited Nov 08 '17 at 21:01

Bathsheba

231,907
34
361
483

answered Mar 05 '14 at 16:23

Jon

1,189
11
17

5

Nice and simple, however when I use this solution and the Access database to be copied is opened, I get an "Access denied" error. This does not happen when using the FileSystemObject. – Jörg Brenninkmeyer Dec 01 '17 at 08:53

VBA to copy a file from one directory to another

2 Answers2

Linked

Related