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

.

Tip: Use Productivity Tool to setup code structure for Open XML generation

A customer requirement is the ability to offline manage business data within a downloaded excel sheet, and upload for processing into the business administration. Strong demand is that the input and actions in the excel sheet must be user-prescribtive as well as restricting. Microsoft Excel supports this via capabilities as input cell validation, cell format, protecting sheets and so on. We provided our customer with an example Excel 2010 sheet as functional specification. After we reached agreement on the Excel sheet behaviour, next step is to realize the runtime Excel sheet generation, and bind it to the user-selected data retrieved from the business administration.

The Open XML SDK can be used for server-based Excel sheet generation. A problem however is that (usage of the) the Open XML API / language is not very well documented. Setting up the generation of a simple Excel sheet is not a problem [as there are also sufficient code examples online]. But when it comes to including capabilities like data validation, the situation changes. The Open XML generation is very fragile, and you easily end up with an incorrect Open XML structure. Due the badly documented Open XML API, fixing the programmatically generation is a frustrating and time-consuming task.

In case of a complexer Excel sheet, a better approach is to utilize Open XML Productivity Tool. Just open the Excel sheet in this tool, and then export the Open XML code generation for the imported sheet. Mind you, typically you will want to refactor the generated code to improve on its maintainability. Also in our example we bind it to the data retrieved from the business administration.

Excel 2010 Protected View hinders browser-opening of downloaded .xlsx file

An user requirement in one of our SharePoint 2010 projects is to export at any moment the displayed contents of an External List (with content originating from SAP ERP, retrieved via SharePoint BCS connecting to BAPI based web services) to an offline file. The functional rationale is version-administration for history and auditing purposes. The SharePoint platform supports this out-of-the-box for regular Lists, by the Export into Excel functionality. However, not so for BCS External Lists. But you can realize it yourself via some custom code. First retrieve the External List contents, and next construct a .xlsx file via Open XML SDK. The .xlsx file is generated server-side in memory, and send to the browser as HttpResponse content. The end-user can next either open the file, or save it somewhere at client-side:

Strange thing I noticed was that when saving the file, that saved file can next be opened successfully. But when instead choose to directly open the file, Excel 2010 displays the error message “The file is corrupt and cannot be opened”.

This must be a client-side issue; the server-side is not aware of the context in which the client-side handles the received HttpResponse (Note: via Fiddler I even analyzed that the HttpResponse contents were identical).

The resolution is hinted at in the File Download window, by the trust-warning about internet downloaded files. The default Excel 2010 TrustSettings are to distrust all downloaded content from non-trusted locations. To validate this I unchecked in Excel 2010 the default settings (via File \ Options \ TrustCenter \ TrustCenter Settings \ Protected View):

This helps, Excel 2010 now direct opens the downloaded file.