Automatic copy

[luckyluci]

I have a folder with few hundred of doc files with significant names.
I put also here an excel file as a master-index.
One column from the excel file has hyperlink formula in order to open quickly (no matter what) doc files.
Questions:
- is it a formula that could insert me in other column of the table the data of each doc file (I need to know when was the last update of the files (that means the xls file contains a command to read from a doc file the data of the file)?
- how can I do the following:
(1) mark several rows in the excel file
(2) the corresponding files that have the names in the marked crowd will be copied to other folder?
Thank you,

 

[joerd]

Well, I thought of some VB code:

Getting a file date is easy enough:

Function FileDate(R As Range) As Variant
On Error GoTo Fail
    FileDate = FileDateTime(R.Hyperlinks(1).Address)
    Exit Function
Fail:
    FileDate = CVErr(xlErrNA)
    Resume Next
End Function

You can use this function in your worksheet, argument is the cell containing the hyperlink, result is a DATE value, so you have to format the cell with the function as a date to display something understandable.

Selecting multiple rows:
Hold CTRL key while you click on the row number.

Copying the selected files, assuming that their address is in a hyperlink in the A column:

Sub CopySelection()
Dim S As Range, R As Range, SourceFile As String, DestDir As String, DestFile As String
    On Error Resume Next
    DestDir = InputBox("Destination directory:")
    DestDir = CurDir & "\" & DestDir
    If Dir((DestDir), vbDirectory) <> &quot;&quot; Then
        Set S = Selection
        For Each R In S.Rows
            SourceFile = CurDir & &quot;\&quot; & R.Cells(1, 1).Hyperlinks(1).Address
            DestFile = DestDir & &quot;\&quot; & R.Cells(1, 1).Hyperlinks(1).Address
            FileCopy SourceFile, DestFile
        Next R
    End If
End Sub

This uses the VBA filecopy method, so the files must be "free", i.e. not opened by Word, Excel, ...

I have not tested it really, so use it at your own risk


Regards,

Joerd