Handy resource for Excel Services external data troubleshooting

I have a 2-steps setup for a dashboard-solution provisioned via Excel-services:

1. Functional data managers / compliance officers: maintain the data offline in Excel worksheets, and when the data maintenance effort is finished publish the worksheet as datasource to SharePoint document libary
2. A separate 'View' dashboard connects via Excel Services to the 'datasource' Excel worksheets, and renders the dashboard - charts, KPIs and so on; This 'view' dashboard worksheet is via Excel Web Access rendered on the SharePoint dashboard page

Opening the dashboard page results in error message Unable to refresh data for a data connection in the workbook..... In the ULS log, only minimal relevant information was logged: "Refresh failed for <data connection> in the workbook....". Via internet search I found a very valuable resource, Excel Services data refresh flowchart (codename: Excel Services Troubleshooting). This helped me find and fix the problem.

Excel Services - 400 Bad Request due large cookie

Playing with Excel Services to compose a dashboard page, I suddenly encountered HTTP 400's on requesting the dashboard page. I monitored the request handling in Fiddler, it showed HTTP Error 400. The size of the request headers is too long. So I inspected the request, and noticed that the Cookie somehow had grown to a very large (string)value:

Pragmatic resolution to fix from the issue is to close all IE instances, and start a fresh IE session. That resolves the issue.

Convert Excel file into Excel Services compliant variant

Excel Services does not support the entire Excel feature set. A.o following aspects are not supported:

• VBA code,
• Macros,
• Comments,
• Shapes,
• External Links, to external workbooks
• (Formula) Names that refer to external workbooks,
• Data validations,
• space(s) in name of worksheet connected as Range Name

(see Differences between using a workbook in Excel and Excel Services)

If your 'source' Excel workbook contains any of the above, trying to use it in Excel Services - e.g. via Chart WebPart - results in the generic error 'Exception has been thrown by the target of an invocation'. Required step before using a 'source' Excel workbook in Excel Services is to convert it to a compliant variant. Excel itself does not include such a functionality. But you can facilitate the user (typical functional data management) via a VBA macro in the excel sheet.

VBA Code

Sub SaveWorkbookAsNewFile()
    Dim ActSheet As Worksheet
    Dim ActBook As Workbook
    Dim CurrentFile As String
    Dim NewFileName As String
    Dim NewFileType As String
    Dim NewFile As String
    Dim ws As Worksheet
        
    Application.ScreenUpdating = False   

    CurrentFile = ThisWorkbook.FullName
    
    ThisWorkbook.Save

    RemoveDataValidations ActBook:=ActiveWorkbook
    RemoveComments ActBook:=ActiveWorkbook
    RemoveShapes ActBook:=ActiveWorkbook
    BreakLinks ActBook:=ActiveWorkbook
    RemoveNamesToExternalReferences ActBook:=ActiveWorkbook
    RemoveConditionalFormatting ActBook:=ActiveWorkbook
    RemoveSpacesFromWorksheets ActBook:=ActiveWorkbook
    RemoveVBA ActBook:=ActiveWorkbook
    
    NewFileType = "Excel Workbook (*.xlsx), *.xlsx," & _
               "All files (*.*), *.*"
 
    NewFile = Application.GetSaveAsFilename( _
        InitialFileName:=NewFileName, _
        fileFilter:=NewFileType)
 
    If NewFile <> "" And NewFile <> "False" Then
        ActiveWorkbook.SaveAs Filename:=NewFile, _
            FileFormat:=xlOpenXMLWorkbook, _
            Password:="", _
            WriteResPassword:="", _
            ReadOnlyRecommended:=False, _
            CreateBackup:=False
    End If
    
    ThisWorkbook.Close False
    Workbooks.Open CurrentFile, False
 
    Application.ScreenUpdating = True
End Sub

Sub RemoveVBA(ActBook As Workbook)

On Error Resume Next
    Dim Element As Object
    With ActBook.VBProject
        For Each Element In .VBComponents
            .VBComponents.Remove Element
        Next
    
        For x = .VBComponents.Count To 1 Step -1
            .VBComponents(x).CodeModule.DeleteLines 1, .VBComponents(x).CodeModule.CountOfLines
        Next x
    End With
End Sub

Sub RemoveDataValidations(ActBook As Workbook)
    Dim ws As Worksheet
    For Each ws In ActBook.Worksheets
        ws.Cells.Validation.Delete
    Next ws
End Sub

Sub RemoveComments(ActBook As Workbook)
    Dim ws As Worksheet
    Dim xComment As Comment
    For Each ws In ActBook.Worksheets
        For Each xComment In ws.Comments
            xComment.Delete
        Next
    Next ws
End Sub

Sub RemoveShapes(ActBook As Workbook)
    Dim ws As Worksheet
    Dim sh As Shape
    For Each ws In ActBook.Worksheets
        For Each sh In ws.Shapes
            sh.Delete
        Next sh
    Next ws
End Sub

Sub BreakLinks(ActBook As Workbook)
    Dim Links As Variant
    Links = ActBook.LinkSources(Type:=xlLinkTypeExcelLinks)
    For i = 1 To UBound(Links)
        ActBook.BreakLink _
            Name:=Links(i), _
            Type:=xlLinkTypeExcelLinks
    Next i
End Sub

Sub RemoveNamesToExternalReferences(ActBook As Workbook)
    Dim nm As Name
    For Each nm In ActBook.Names
        If InStr(nm.RefersTo, "[") <> 0 Then
            nm.Delete
        End If
   Next
End Sub

Sub RemoveConditionalFormatting(ActBook As Workbook)
    Dim ws As Worksheet
    For Each ws In ActBook.Worksheets
        ws.Cells.FormatConditions.Delete
    Next ws
End Sub

Sub RemoveSpacesFromWorksheets(ActBook As Workbook)
    Dim ws As Worksheet
    For Each ws In ActBook.Worksheets
        ws.Name = Replace(ws.Name, " ", "_")
    Next ws
End Sub