Excel – SharePoint 2013 Export To Excel – “An unexpected error has occurred.”

I have found that putting an '=' before the double quotes will accomplish what you want. It forces the data to be text.

eg. ="2008-10-03",="more text"

EDIT (according to other posts): because of the Excel 2007 bug noted by Jeffiekins one should use the solution proposed by Andrew: "=""2008-10-03"""

An Excel Web Query (iqy) is just not going to work. Its only purpose is to define a "web query" for Excel to open.

First frustration, I am not positive, but digitally signing the workbook may avoid the blocked data connection prompt. Also, if I remember correctly, there is a Global Policy setting for this, so if you are part of a domain, you can have this setting changed when the user logs into the domain.

Second frustration. Not much you can do here.

Suggestions, all kinds. We would need to understand your requirements and constraints. But, I suggest you dynamically create the workbook using XMLSS. You have full control over formatting, layout, and nearly everything else, without the need to have Excel installed on the server. The served workbook must then be saved locally and cannot be saved back to server.

See XML Spreadsheet Reference at http://msdn.microsoft.com/en-us/library/aa140066(office.10).aspx

Here is a search for "visual basic asp xml workbooks site:microsoft.com", http://www.bing.com/search?q=visual+basic+asp+xml+workbooks+site%3Amicrosoft.com. Search for ASP generates more results. Note it is easy to translate/migrate to ASP.NET so do not let the ASP throw you.

I recommend starting with Using Visual Basic and ASP with XML to Generate Excel 2003 Workbooks at http://msdn.microsoft.com/en-us/library/aa203722(office.11).aspx, especially near the end of the article because there is an example on creating a workbook and setting the page print orientation. Moreover, there is an example on how to create a template.