ReturnLastModifiedBy

[returningby]

Hey all,

I have been searching for the last 3 and a half hours so I finally admit I need some help. I currently have an excel document, and it has a list of hyperlink functions to a few different word documents. I wanted to track all of these word documents in excel. So I hyperlinked them, and made a field called "Last modified by". I found a great tool in the forums for a VBA script earlier:

Function ReturnLastModified(r As Range) As String
Dim temp
Application.Volatile True
temp = Split(r.Formula, Chr(34))(1)
If Dir(temp) <> "" Then ReturnLastModified = FileDateTime(temp)
End Function

Basically I have an excel document and I wanted to know when it was last modified. Worked great and the world was happy. So I started thinking, what if I used the nifty BuiltinDocumentProperties tool and grabbed the last author from these word documents? So I tried to alter and research the different syntax, but to no avail. The closest I got was similar scripts, but they always referenced the ActiveWorkbook. Seeing as how I am trying to point these hyperlinks, anything I do results in a #value error. Any help is appreciated. Thank you.

 

[RoryA]

You can't access that property of a closed Word document natively in VBA. You either need to download a dsofile.dll from Microsoft that will allow your code to read that, or you'd need to automate Word and open the documents.

 

[returningby]

Thank you,

While waiting for a response I had actually just read something about that very explanation. I am assuming that it can grab the last modified date because that information is published outside of the document itself. This project is on a work computer so I will not be able to download anything, but it was worth a shot anyway. Thank you.

 

[Momentman]

Except I didn't understand you correctly. I created a hyperlink in A1 to a word doc and used a UDF to get the last modified date in B1, isn't that what you want?

Excel 2013
	A	B
1	Hyperlink	Last modified
2	My Cv	01-Jun-15 17:59:28
Sheet3

Cell Formulas
Range	Formula
B2	=LastmodData(A2)

UDF is something like

Function LastmodData(filepath As Range)
    If filepath.Hyperlinks.Count > 0 Then
        LastmodData = FileDateTime(filepath.Hyperlinks(1).Address)
    End If
End Function

 

[returningby]

Thank you moment. I was accomplish what you showed in your example. However, after that information, I was looking for a way to also retrieve the last author who had made the modification, as opposed to just the time it was modified. Thank you again though for your time.

 

[Momentman]

Thank you moment. I was accomplish what you showed in your example. However, after that information, I was looking for a way to also retrieve the last author who had made the modification, as opposed to just the time it was modified. Thank you again though for your time.

I should think RoryA is right

 

[returningby]

Hey RoryA,

After some more digging around the forums, I found some code that helped me a lot and funny enough it had been posted by you: http://www.mrexcel.com/forum/excel-questions/714431-get-username-if-file-already-open.html

In the thread you mentioned:

Function GetFileOwner(strfileName As String) As String
 
    Dim secUtil As Object
    Dim secDesc As Object
 
    Set secUtil = CreateObject("ADsSecurityUtility")
    Set secDesc = secUtil.GetSecurityDescriptor(strfilename, 1, 1)
    GetFileOwner = secDesc.Owner
 
End Function

This code actually works perfectly for what I need, however secdesc.owner seems to be pulling our company ID's which are just a string of numbers, as opposed to function like BuiltInDocumentProperties, that would pull actual usernames and whatnot.
I tried looking around, but couldn't find anything past secDesc.Owner, but do you know if there is a page with the parameters, so that you could drill down to secDesc.Owner.UserName, or secDesc.Owner.SID? Any variation I try just gives me a debug error, but I just might not be looking in the correct place for the variables. Thank you again for your help.