Excel SaveAs to SharePoint failing due required document library properties

In a business process we publish a snapshot from Excel workbook to SharePoint. The VBA code for this is simple: ActiveWorkbook.SaveAs "<url of document library>' & ActiveWorkbook.Name, FileFormat:=xlOpenXMLWorkbookMacroEnabled.

However, execution of this code results in error Run time error '1004': Index refers beyond end of list. The direct cause is that the document library includes a mandatory metadata field, and as this is not set in the Excel workbook, SharePoint refuses the upload. Sadly it appears not possible to pre-set Office 'Document Properties - Server' from VBA code.

2 pragmatic alternatives to workaround the issue:

1. make the field / document library property non-required,
2. or modify the field / document library property to have a default value

.