Extracting the Company property from a Word document using Excel VBA

Question

I'm using Excel 2010 and VBA to reorganize some Word documents on my C:\ drive and I want to pull the "Company" property of each document into a sheet containing a list of documents. The full file path of each document appears in row A (like so: "C:\folder\subfolder\file.doc"),and I would like to populate the corresponding "Company" property in row F.

I'm trying to write a macro for a custom Excel function DocCompany that will use a text string containing a local filepath to return the "Company" property of the document that the filepath identifies. I've had better luck with the Last Modified and File Size properties, as both have dedicated VBA functions (FILEDATETIME and FILELEN, respectively). In each case, all I had to do was write a macro for a custom Excel function that returns the result of the VBA function for a file path string located in the sheet.

Given the following function, =GetFileDateTime(A1) will return the last save date for the document identified by the filepath string contained in A1.

Function GetFileDateTime(FileName As String) As Date
    GetFileDateTime = FileDateTime(FileName)
End Function

Given the following function, =FileSize(A1) will return the file size in bytes of the document identified by the filepath string contained in A1.

Function FileSize(FileName As String)
    FileSize = FileLen(FileName)
End Function

However, the Company property has no corresponding VBA function, so (as I said above), I want to write a macro defining a custom Excel function DocCompany that will accept a local filepath string as input and use it to output the Company property of the document.

This is what my macro looks like right now:

Function CompanyID(FileName As String) As String
    CompanyID = Documents(FileName).Open
    Documents(FileName).BuiltinDocumentProperties (wdPropertyCompany)
End Function

When I try to save it, Excel returns the error message "Compile error: Sub or Function not defined". Then it selects the reference to the "Documents" collection in the second row.

Why does Excel insist that I define "Documents" as a variable when every reference I can find confirms that it IS in fact an object or collection? What can I do to make this code work? Do I need to take a different approach?

You can get this to work as a UDF as shown in this answer but, if you have to create a Word application object every time, then the UDF will be painfully slow to run. You would also need to ensure that you are meticulous in disposing of every Word application object that gets created — barrowc

David Zemens David Zemens · Accepted Answer · 2015-02-23T22:47:42

Why does Excel insist that I define "Documents" as a variable when every reference I can find confirms that it IS in fact an object or collection?

Since Documents is not part of the Excel object model, it is being interpreted as a variable. You need to bind Word to the Excel instance, and reference that instance of the Word Application class.

Function CompanyID(FileName As String) As String
    Dim wdApp as Object 'WOrd.Application
    Dim doc as Object 'Word.Document
    Const wdPropertyCompany as Long = 21 'Explicit reference for late-bound Word Application

    Set wdApp = CreateObject("Word.Application")
    Set doc = wdApp.Documents.Open(FileName)
    CompanyID = doc.BuiltinDocumentProperties (wdPropertyCompany)
    doc.Close False

End Function

Extracting the Company property from a Word document using Excel VBA

2 Answers