Excel VBA - update BuiltinDocumentProperties using value from different workbook
<p>I want to create a "driver" workbook where someone can update values that will be applied to another workbook. The contents of cell B8 of "macros.xlsm" contains the text string I want to use for the author of "report1.xlsx".  I have written the following macro but keep getting a 
"Object doesn't support this property or method" error on the last line.</p>

<pre><code>Sub add_properties()

Dim xL As Excel.Application
Set xL = New Excel.Application

Dim mainWB As Excel.Workbook
Dim reportWB As Excel.Workbook

Set mainWB = xL.Workbooks.Open("C:\Users\ga1085\adHoc\macros.xlsm")
Set reportWB = xL.Workbooks.Open("C:\Users\ga1085\adHoc\report1.xlsx")

MsgBox mainWB.Sheets("adHoc").Range("B8").Value

mainWB.Sheets("adHoc").Range("b8").Copy
reportWB.BuiltinDocumentProperties("author").PasteSpecial (xlPasteValues)

End Sub
</code></pre>

<p>I am also using "macros.xlsm" to update margins, headers, etc for "report1.xlsx" - will this work on those too?</p>