0

Every x days I have to "archive" a ton of databases by copy/pasting them into an archive folder on a shared network. This task was recently assigned to me and the previous party was doing this by hand over the course of an hour (at least). I've automated it but am getting tripped up when my code is trying to copy databases that are currently open elsewhere.

I popped some dynamic file paths in an Excel workbook and used the following code to automate this task:

Sub archive()
    'On Error Resume Next
    Dim i As Integer

    For i = 2 To 56
        FileCopy Sheets("Sheet2").Cells(i, 1), Sheets("Sheet2").Cells(i, 2)
    Next i 
End Sub

I know it's not as pretty as it could be, but that's not my problem. My problem is that at any given time, multiple users are in at least one of the dozens of database instances I need to make a copy of, and apparently Windows doesn't allow copy of an open file (?) via VBA. I can go to the specified folder and manually do it, though, which is somehow safer it would seem..

Anyways, is there a good solution here other than me manually copying the databases that are open when I run my macro? Can I force the copy somehow?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Nance Garon
  • 1
  • https://stackoverflow.com/questions/16943003/vba-to-copy-a-file-from-one-directory-to-another Looks like filesystemobject.copyfile allows it while filecopy does not – Warcupine Jul 02 '19 at 19:08
  • I saw that, but I don't think I can use 'dynamic' file paths within cells of the workbook with filesystemobject.copyfile.. i.e. FileSystemObject.CopyFile Sheets("Sheet2").Cells(i, 11), Sheets("Sheet2").Cells(i, 12) gives me an Object Required runtime error – Nance Garon Jul 02 '19 at 19:10
  • In your excel workbook you're reading the cell contents to get the source and destination paths? – Warcupine Jul 02 '19 at 19:12
  • Yes. I edited my above comment just as you commented again, sorry about that. – Nance Garon Jul 02 '19 at 19:13
  • I have a few cells to input the date, which feed into the file path cells, and then the macro copies the databases specified in those cells into the locations specified in other cells – Nance Garon Jul 02 '19 at 19:13
  • I resolved it, I just had to declare "fso" as new filesystem object, then use fso.CopyFile and it worked. Thanks for your help (: – Nance Garon Jul 02 '19 at 19:26

0 Answers0