VBA to copy a file from one directory to another
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.
fileName = "X:\Database\oldName.accdb" copyDestination = "Y:\dbstore\" newName = "newName.accdb"
Is there an easy way of doing this?
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.
This method is even easier if you're ok with fewer options:
FileCopy source, destination
Read more... Read less...
One thing that caused me a massive headache when using this code (might affect others and I wish that somebody had left a comment like this one here for me to read):
- My aim is to create a dynamic access dashboard, which requires that its linked tables be updated.
- I use the copy methods described above to replace the existing linked CSVs with an updated version of them.
- Running the above code manually from a module worked fine.
- Running identical code from a form linked to the CSV data had runtime error 70 (Permission denied), even tho the first step of my code was to close that form (which should have unlocked the CSV file so that it could be overwritten).
- I now believe that despite the form being closed, it keeps the outdated CSV file locked while it executes VBA associated with that form.
My solution will be to run the code (On timer event) from another hidden form that opens with the database.