Automating Custom Document Properties In Excel And Word (1 Viewer)

[BillyWiz]

Hi!,
I need to add custom document properties to Word and Excel documents, prior to saving them, which have been created using...
DoCmd.OutputTo acOutputQuery, queryName, acFormatXLS, ReportName

I've tried using...
Set objXL = CreateObject("Excel.Application")
objXL.workbooks.Open ReportName
With objXL.workbooks(1)
.CustomDocumentProperties.Add Name:="Classification", LinkToContent:=False, Type:=msoPropertyTypeString, Value:="PROTECTED"

and...

.CustomDocumentProperties.Item.Add Name:="Classification", LinkToContent:=False, Type:=msoPropertyTypeString, Value:="PROTECTED"

..without success.

Similar code works within Excel and Word VBA but not when automated from Access 2010

Part of my problem is that the
DoCmd.OutputTo acOutputQuery, queryName, acFormatXLS, ReportName
...doesn't seem to allow for a template to be used. If it did, I could add the custom properties in advance. Alternatively could I inject an AutoExec into the unopened spreadsheet that is created by this process?

Can anyone advise?

Thanks
Bill

 

[SforSoftware]

Don't use the OutputTo method, but use Automation to create a new file from a template. Then open a recordset and use CopyFromRecordset to put the data into your Excel document.