"An error has occurred. Please contact your system administrator if this problem persists."
The error seemed suspicious and that prompted me to look into the issue in the events viewer on the server running the Excel Services. And I found lots of errors with Error 5226.
Bad news!, where did it all come from... on a closer look at the error details I noticed that some account does not have permissions to create a folder or a file location for the services to run.
Event Type: Error
Event Source: Office SharePoint Server
Event Category: Excel Calculation Services
Event ID: 5226
Unable to create or access workbook cache at C:\WINDOWS\TEMP\Excel Server\FileCache\InteXDRESF. Excel Services is unable to function without a workbook cache.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
1. Manually created the file location "C:\WINDOWS\TEMP\Excel Server\FileCache\InteXDRESF"
2. Applied permissions as follows; granted WSS_ADMIN_WPG - Full Control on the "TEMP" folder.
3. Applied permissions as follows; granted WSS_WPG - Read and Write on the "TEMP" folder.
Then I tested the web part and I still got an error;
4. I then manually created the "Ranges, SavedWorkbooks and Charts" folder (see above). These folders help to display the ranges and charts in the Excel workbook.
Then that solved the problem!