-1

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.

endolith
  • 25,479
  • 34
  • 128
  • 192
Treckez
  • 11
  • 1
  • 5

2 Answers2

0

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

JohnSUN
  • 2,268
  • 2
  • 5
  • 12
-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")

brWHigino
  • 260
  • 1
  • 2
  • 10
  • Not working for me, It is saying "BASIC syntax error. Unknown data type Range". The destination path does contain the file name. I forgot to include also I am using libre calc on ubuntu. – Treckez Jan 07 '14 at 11:01