Automatic copy
Automatic copy
(OP)
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,
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,
Talk To Other Members
RE: Automatic copy
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) <> "" Then
Set S = Selection
For Each R In S.Rows
SourceFile = CurDir & "\" & R.Cells(1, 1).Hyperlinks(1).Address
DestFile = DestDir & "\" & 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