CopyFiles using spreadsheet columns for source and destination

Question

I have a spreadsheet with a column "D" that has a list of file paths, and there are about 2500 files in this column. In column "E", I have a list of equal size with the new destination file path. I want to copy all the files in column D to the locations in column E. I know it's possible using a Python / Basic macro in LibreOffice, but I can't seem to get it right.

This is the VBA code I found while searching, but unfortunately it doesn't work with LibreOffice Calc.

Sub Copy_Files()
    Dim cell As Range
    For Each cell In Range("D1", Range("D" & Rows.Count).End(xlUp))
        FileCopy Source:=cell.Value, Destination:=cell.Offset(, 1).Value
    Next cell
End Sub

I don't know any Python, but if someone put me on the right track I could work it out.

Answer 1

I tested the following, and it worked:

Sub Copy_Files()
    Dim cell As Range
    Dim fs As Object
    Set fs = CreateObject("Scripting.FileSystemObject")
    For Each cell In Range("D1", Range("D" & Rows.Count).End(xlUp))
        fs.CopyFile cell.Value, cell.Offset(0, 1).Value
    Next cell
End Sub

I believe you just need to make sure the destination path also contains the file name. Also, you need to enable "Microsoft Scripting Runtime" reference ("C:\windows\system32\scrrun.dll")

Answer 2

You don't need a macro for this simple operation.

Write the formula in cell F1

="cp" & D1 & "" & E1

Multiply this cell to the end of the file list

You now have about 2500 file copy commands.

Copy them to the clipboard (Ctrl+C), open a terminal (Ctrl+Alt+T) and paste all these commands there (Ctrl+Shift+V).

In a few seconds (or a few minutes) the work will be done