Read external file properties (who updated file as last) using VBA

[kweers]

Hi All,

Can anybody tell me how to read the properties of a saved file using VBA.

Specifically I want to read the user who has last modified an external file, I have already found the way how to read the last modified date but have not figured out how to get the user (name) who has modified the file.

I want to make a list in Excel of (external) files with last modified date and by whom.

With kind regards.

 

[jbeaucaire]

The BuiltinDocumentProperties("Last author") method should provide that to you, you just need to attach an object to the head of that.

More info: http://www.cpearson.com/excel/docprop.htm

 

[kweers]

I do not have the right permission to install the required DLL, is there a way one do not need an extra DLL?

 

[brettdj]

I do not have the right permission to install the required DLL, is there a way one do not need an extra DLL?

As per jbeaucaire's link you can use BuiltinDocumentProperties - you dont need DSOFile.dll file for this read

MsgBox ThisWorkbook.BuiltinDocumentProperties("Last author")

Cheers

Dave

 

[Kenneth Hobson]

If it is an Excel file:

Function LastUser(strPath As String) As String
  '// Code by Helen from http://www.visualbasicforum.com/index.php?s=
  '// This routine gets the Username of the File In Use
  '// Credit goes to Helen for code & Mark for the idea
  '// Insomniac for xl97 inStrRev
  '// Amendment 25th June 2004 by IFM
  '// : Name changes will show old setting
  '// : you need to get the Len of the Name stored just before
  '// : the double Padded Nullstrings
  Dim strXl As String
  Dim strFlag1 As String, strflag2 As String
  Dim i As Integer, j As Integer
  Dim hdlFile As Long
  Dim lNameLen As Byte
  
  
  strFlag1 = Chr(0) & Chr(0)
  strflag2 = Chr(32) & Chr(32)
  
  hdlFile = FreeFile
  Open strPath For Binary As #hdlFile
  
  strXl = Space(LOF(hdlFile))
  
  Get 1, , strXl
  Close #hdlFile
  
  j = InStr(1, strXl, strflag2)
  
  #If Not VBA6 Then
    '// Xl97
    For i = j - 1 To 1 Step -1
      If Mid(strXl, i, 1) = Chr(0) Then Exit For
    Next
    i = i + 1
    #Else
      '// Xl2000+
      i = InStrRev(strXl, strFlag1, j) + Len(strFlag1)
  #End If
  
  '// IFM
  
  lNameLen = Asc(Mid(strXl, i - 3, 1))
  LastUser = Mid(strXl, i, lNameLen)
End Function

 

[kweers]

I have tried the code in Excel 2010, but it does not work, for which Excel version was the code written?
Is there a bugfix for Excel 2010?

With kind regards,

René