Saturday, May 05, 2007

Retrieve the file properties - VBA

Sub Get_WorkBook_Properties()

Dim oWB As Workbook

' Here is the program to retrieve the file properties.

Set oWB = ActiveWorkbook

' Coded by Shasur for

'Get the Title property
sTitle = oWB.BuiltinDocumentProperties("Title").Value

'Get the Subject property
sSubject = oWB.BuiltinDocumentProperties("Subject").Value

'Get the Author property
sAuthor = oWB.BuiltinDocumentProperties("Author").Value

'Get the Keywords property
sKeywords = oWB.BuiltinDocumentProperties("Keywords").Value

'Get the Comments property
sComments = oWB.BuiltinDocumentProperties("Comments").Value

'Get the Template property
sTemplate = oWB.BuiltinDocumentProperties("Template").Value

'Get the Last author property
sLastauthor = oWB.BuiltinDocumentProperties("Last author").Value

'Get the Revision number property
sRevisionnumber = oWB.BuiltinDocumentProperties("Revision number").Value

'Get the Application name property
sApplicationName = oWB.BuiltinDocumentProperties("Application name").Value

'Get the Last print date property
sLastprintdate = oWB.BuiltinDocumentProperties("Last print date").Value

'Get the Creation date property
sCreationdate = oWB.BuiltinDocumentProperties("Creation date").Value

'Get the Last save time property
sLastsavetime = oWB.BuiltinDocumentProperties("Last save time").Value

'Get the Total editing time property
sTotaleditingtime = oWB.BuiltinDocumentProperties("Total editing time").Value

'Get the Number of pages property
sNumberofpages = oWB.BuiltinDocumentProperties("Number of pages").Value

'Get the Number of words property
sNumberofwords = oWB.BuiltinDocumentProperties("Number of words").Value

'Get the Number of characters property
sNumberofcharacters = oWB.BuiltinDocumentProperties("Number of characters").Value

'Get the Security property
sSecurity = oWB.BuiltinDocumentProperties("Security").Value

'Get the Category property
sCategory = oWB.BuiltinDocumentProperties("Category").Value

'Get the Format property
sFormat = oWB.BuiltinDocumentProperties("Format").Value

'Get the Manager property
sManager = oWB.BuiltinDocumentProperties("Manager").Value

'Get the Company property
sCompany = oWB.BuiltinDocumentProperties("Company").Value

'Get the Number of bytes property
sNumberofbytes = oWB.BuiltinDocumentProperties("Number of bytes").Value

'Get the Number of lines property
sNumberoflines = oWB.BuiltinDocumentProperties("Number of lines").Value

'Get the Number of paragraphs property
sNumberofparagraphs = oWB.BuiltinDocumentProperties("Number of paragraphs").Value

'Get the Number of slides property
sNumberofslides = oWB.BuiltinDocumentProperties("Number of slides").Value

'Get the Number of notes property
sNumberofnotes = oWB.BuiltinDocumentProperties("Number of notes").Value

'Get the Number of hidden Slides property
sNumberofhiddenSlides = oWB.BuiltinDocumentProperties("Number of hidden Slides").Value

'Get the Number of multimedia clips property
sNumberofmultimediaclips = oWB.BuiltinDocumentProperties("Number of multimedia clips").Value

'Get the Hyperlink base property
sHyperlinkbase = oWB.BuiltinDocumentProperties("Hyperlink base").Value

'Get the Number of characters (with spaces) property
sNumberofcharacters = oWB.BuiltinDocumentProperties("Number of characters (with spaces)").Value

'keywords: VBA Update File Properties, Macro to Update File Properties

End Sub


  1. Anonymous2:23 AM

    and custom properties?

  2. Andrew H3:39 AM

    This is helpful, but is it possible to pull the current user's (instead of author / last author) name from anywhere?

  3. Anonymous7:53 AM

    Here is the code to get the username instead of Author

    ' Access the GetUserNameA function in advapi32.dll and
    ' call the function GetUserName.
    Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
    (ByVal lpBuffer As String, nSize As Long) As Long
    ' Main routine to Dimension variables, retrieve user name
    ' and display answer.
    Sub auto_open()

    ' Dimension variables
    Dim lpBuff As String * 25
    Dim ret As Long, UserName As String
    ' Get the user name minus any trailing spaces found in the name.
    ret = GetUserName(lpBuff, 25)
    UserName = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1)

  4. @Andeww H: Current user can be taken from Application.UserName

  5. I need to find the author name of the track revision in table, how is it possible.kindly help out.

  6. Anonymous12:02 AM

    can anyone tel me how dis can be achieved in either java or webmethods?
    Thank you in advance.

  7. Anonymous9:33 AM

    is there a way to call the document properties for a remote or closed file?

  8. The last print date property is not working. Can you please tell me how this will work as i need this in a project?

  9. Anonymous10:52 AM

    Is it possible to morph this to get a list of previous savers? Example the past 10 or 20 or 100 (for the purposes of in-house review). How would that work please?

  10. Anonymous6:44 PM

    Is it possible to pull the document contents, which shows the names of the worksheet(s)?

  11. Is there any way to identify, is the word document is fully loaded or not in VBA macro.
    For instance if the document has 100 pages, its ready state should return saying document is ready.


Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.