使用Excel VBA从Word文档中提取Company属性 [英] Extracting the Company property from a Word document using Excel VBA

问题描述

我正在使用Excel 2010和VBA在C:\驱动器上重新组织一些Word文档，并且我想将每个文档的"Company"属性拉到包含文档列表的工作表中.每个文档的完整文件路径都出现在A行中(例如:"C:\ folder \ subfolder \ file.doc")，我想在F行中填充相应的"Company"属性.

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.

我正在尝试为自定义Excel函数DocCompany编写宏，该宏将使用包含本地文件路径的文本字符串返回该文件路径标识的文档的"Company"属性.我最好使用Last Modified和File Size属性，因为它们都具有专用的VBA函数(分别为FILEDATETIME和FILELEN).在每种情况下，我所要做的就是为自定义Excel函数编写一个宏，该宏为位于工作表中的文件路径字符串返回VBA函数的结果.

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.

给出以下功能， = GetFileDateTime(A1)将返回由A1中包含的文件路径字符串标识的文档的最后保存日期.

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

给出以下功能， = FileSize(A1)将返回由A1中包含的文件路径字符串标识的文档的文件大小(以字节为单位).

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

但是，Company属性没有相应的VBA函数，因此(如上所述)，我想编写一个定义自定义Excel函数DocCompany的宏，该宏将接受本地文件路径字符串作为输入并使用它来输出Company文档的属性.

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

当我尝试保存它时，Excel返回错误消息编译错误:未定义子函数或函数".然后，在第二行中选择对文档"集合的引用.

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.

当我可以找到的每个引用都确认它实际上是对象或集合时，为什么Excel坚持要求我将文档"定义为变量?我该怎么做才能使此代码正常工作?我需要采取其他方法吗?

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?

推荐答案

当我可以找到的每个引用都确认它实际上是对象或集合时，为什么Excel坚持要求我将文档"定义为变量?

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?

由于 Documents 不是Excel对象模型的一部分，因此将其解释为变量.您需要将Word绑定到Excel实例，并引用Word Application类的该实例.

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

这篇关于使用Excel VBA从Word文档中提取Company属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！