Archive for the ‘Excel’ Category

Marrying CSV file type with Microsoft Excel with SharePoint as the Chaperone

Tuesday, February 24th, 2015

Version: 1.3, Modified On: 03/25/2015

The title of this post can be read as following in SharePoint terms:
How to open CSV files in Microsoft Excel when using CSV from SharePoint?

Scenario

.CSV file type is not tied with Microsoft Excel in SharePoint. When users click on a CSV file in a SharePoint Library, it opens the CSV file in Notepad on the user’s computer. In contrast, if user clicks on a .CSV file on their PC, it opens the file in Microsoft Excel.

Antoher scenario where a user has the CSV file checked out, but he closed the CSV file without checking in the file. Now the users clicks on the checked out CSV and it opens in the notepad.

Another issue, the CSV files don’t have any icon associated with the file in SharePoint.

CSV File in SharePoint – Default Behavior

Let’s review the default behavior of CSV file in SharePoint. We have a test CSV file already uploaded to a library in SharePoint.

1. You can see there is no descriptive icon associated with the CSV file unlike Excel file. See the Excel file and .TXT file has relevant icon.

SharePoint - CSV File with no icon - Default behavior
SharePoint – CSV File with no icon – Default behavior

2. Click on the CSV file and you will get a prompt to open, save or save as the file

SharePoint - CSV File type clicked - Default behavior
SharePoint – CSV File type clicked – Default behavior

3. Click Open and it will open the CSV file in Microsoft Excel.

SharePoint – CSV File Opened in MS Excel – Default behavior

4. Now let’s check out the CSV file

SharePoint - Check Out CSV File - Notice Edit in Excel not an Option in menu - Default behavior
SharePoint – Check Out CSV File – Notice Edit in Excel not an Option in menu – Default behavior

5. Click on the checked out CSV file

SharePoint – Check Out CSV File – Click to open – Default behavior

6. Click ok on the Open Document dialog box

SharePoint - Check Out CSV File opens Document dialog box - Default behavior
SharePoint – Check Out CSV File opens Document dialog box – Default behavior

7. The opens up in Notepad!!!

SharePoint - Check Out CSV File opens up in Notepad - Default behavior
SharePoint – Check Out CSV File opens up in Notepad – Default behavior

We will review the inside behavior and how we can enhance the user experience for the CSV files.

Different Areas To Review and Resolution

We’ll review few areas related to this issue:

a. Default Programs settings on the user’s PC
i. Set your default programs
ii. Associate a file type or protocol with a program
b. IIS Web Server MIME Settings for .CSV on SharePoint Servers
c. Adding line item for CSV file and behavior in DocIcon.xml on SharePoint Servers
d. Changing Hkey_Classes_Root.csv on user’s PC

a. Default Programs settings on the user’s PC

Excel is the default program for CSV and is usually not the issue for the .CSV file to open in Microsoft Excel when the CSV file is on the PC or network share.

i. Set your default programs

1. Go to Control Panel > Default Programs
2. Click on Set your default programs

CSV File Association - Control Panel > Default Programs
CSV File Association – Control Panel > Default Programs

3. Click on Excel from the list of program on left side. Then click on ‘Choose defaults for this program’

CSV File Association – Control Panel > Default Programs – Excel Defaults

4. Make sure the csv extension is checked for Excel. Then click Save.

CSV File Association – Control Panel > Default Programs – Excel Defaults

ii.    ii. Associate a file type or protocol with a program

Let’s review the file type association under Default Programs.

1.  Go to Control Panel > Default Program
2. Click on ‘Associate a file type or protocol with a program’

CSV File Association - Control Panel > Default Programs - Associate File Type or Protocol
CSV File Association – Control Panel > Default Programs – Associate File Type or Protocol

3. On next page, make sure .csv file type is associated with Excel. Click on Change Program button if it is not or if you want to change it.

CSV File Association - Control Panel > Default Programs - CSV File Type Associate
CSV File Association – Control Panel > Default Programs – CSV File Type Associate

4. From Open With dialog screen, choose Excel. If Excel is not listed then expand ‘Other Programs’ section by clicking on the small arrow on right side. If Excel is not listed there then use Browse button to go to folder location where Microsoft Excel is installed and select the Excel.exe executable file. Make you to check the box that says ‘Always use the selected program to open this kind of file’

CSV File Association - Control Panel > Default Programs - CSV File Type Association with Excel
CSV File Association – Control Panel > Default Programs – CSV File Type Association with Excel

5. Click OK and close Default Program window.

b. IIS Web Server MIME Settings for .CSV on SharePoint Servers

We’ll now review the MIME type in IIS.

1. On SharePoint Servers, open IIS Manager
2. Click on the Server name on the left side of the console. On the right side of console make sure you have Features View tab highlighted

Note: You can make this change for selective web application (IIS site) rather than to the whole server.

3. From the features list, click on the MIME Types

SharePoint Servers - IIS Manager > MIME Types for CSV Changes
SharePoint Servers – IIS Manager > MIME Types for CSV Changes

4. Look for .CSV under the MIME Types. [Hint: You can select the first item in list and type .csv on kleyboard. It will take you right to the CSV extension]

The default .CSV MIME Type config is set to: application/octet-stream

5. Double click on the .CSV (or select .cSV and choose Edit from actions area on right).
Enter this value for CSV: application/vnd.ms-excel

Note: I’ve also noticed few people used below .CSV MIME Type value:
application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

SharePoint Servers - IIS Manager > Updated MIME Types for .CSV Extension
SharePoint Servers – IIS Manager > Updated MIME Types for .CSV Extension

The IIS MIME type change will make the CSV file open in Microsoft Excel. Read below for details. Some scenarios may require below changes as well.

c. Adding line item for CSV file and behavior in DocIcon.xml on SharePoint Servers

Now to better user experience, we would like to add some icon with CSV file and enabled ‘Edit in Microsoft Excel’ in file menu in SharePoint. Look into the icon for CSV file in the first image at the top of this post.

On SharePoint Servers, we have DocIcon.xml file at following location that holds the key to the file type and behavior.

Location:
C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions14TEMPLATEXMLDOCICON.xml

Under the ByExtension section add the line for CSV file type. Notice the CSV16.gif is the icon file that I’m using for the CSV. You can use other icons like icxltx.png
<Mapping Key=”csv” Value=”CSV16.GIF” EditText=”Microsoft Excel” OpenControl=”SharePoint.OpenDocuments”/>

The location to store your own file icon or use one already there:
C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions14TEMPLATEIMAGES

Note:
I’ve noticed people have used one of these configuration for the CSV file type in DocIcon.xml. I choose the above line entry.
<Mapping Key=”csv” Value=”CSV16.GIF” OpenControl=””/>

<Mapping Key=”csv” Value=”CSV16.GIF” OpenControl=”SharePoint.OpenDocuments”/>

<Mapping Key=”csv” Value=”CSV16.GIF” EditText=”Microsoft Excel” OpenControl=”SharePoint.OpenDocuments” OpenApp=”ms-excel”/>

Important:
After performing the MIME Type change and this DocIcon.xml addition on all SharePoint Servers, make sure to perform the IISreset/noforce on SharePoint Servers.

d. Changing Hkey_Classes_Root.csv on user’s PC

On few user systems, I noticed the checked out files will still open in notepad if clicked by the same user.

To resolve that issue, I had to delete “PerceivedType”=”text” from the following registry location. [Use at your own risk and with backups].

HKEY_CLASSES_ROOT.csv

SharePoint - Deleted PerceivedType entry from Registry for .CSV Extension on user PC
SharePoint – Deleted PerceivedType entry from Registry for .CSV Extension on user PC

That registry location had these value:

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT.csv]
“PerceivedType”=”text”
@=”Excel.CSV”
“Content Type”=”application/vnd.ms-excel”

[HKEY_CLASSES_ROOT.csvExcel.CSV]

[HKEY_CLASSES_ROOT.csvExcel.CSVShellNew]

[HKEY_CLASSES_ROOT.csvPersistentHandler]
@=”{guid}”

Few other notable Registry Location:
HKEY_CLASSES_ROOT.***
HKEY_CLASSES_ROOT<ProgId>shellopencommand
HKEY_CLASSES_ROOTMIMEDatabaseContent Type

HKEY_LOCAL_MACHINESOFTWAREClasses.csv

After these changes, the CSV filetypes will show the icon on the left, the SharePoint file menu will show Edit in Microsoft Excel as an option and edit the file will open the file in Microsoft Excel.

SharePoint - CSV File Types Showing Icon, Menu Showing Edit in Microsoft Excel Option
SharePoint – CSV File Types Showing Icon, Menu Showing Edit in Microsoft Excel Option

The short summary of this overall jargon can be explained in this Softvative Graphics.

Quick Process to Make CSV filetype work in MS Excel on SharePoint
Quick Process to Make CSV filetype work in MS Excel on SharePoint

@@@@@ At the end, the CSV and MS Excel lived happily ever after. 🙂  @@@@@

Few good resource to read on MIME Types and CSV

Keywords:

  • How to open CSV file in Sharepoint
  • How to open CSV file in Microsoft Excel
  • How to open CSV file in Excel
  • Open CSV in Excel
  • Open CSV from SharePoint
  • open .CSV from in Excel
  • open .CSV from SharePoint
  • CSV MIME Type changes
  • CSV MIME Type changes in SharePoint
  • CSV MIME Type changes for SharePoint
  • CSV MIME Type changes for Excel
  • CSV open document behavior
  • CSV default program association
  • Marry the CSV to Excel
  • CSV Registry locations
Visit www.softvative.com for professional services.

Excel Web Services Unable to process request

Thursday, November 20th, 2014

Excel Web Services error in SharePoint

Unable to process the request. Wait a few minutes and try performing this operation again – Event Viewer Error 5231, 5239, 5240

Version: 1.5, Created: 11/20/2014, Last Updated: 12/15/2014

Error:

When you click on an excel file to open it in Excel web services, you get this error:
Unable to process the request. Wait a few minutes and try performing this operation again.

SharePoint Excel Services - Unable to Process Request Error
SharePoint Excel Services – Unable to Process Request Error

Few Suggestions from other sources:
1. Stopped and started Excel Calculation services from SharePoint Central Admin > Servers in Farm page > ServerName > Services

2. Make sure Excel Service app is associated with the SharePoint web app

3. Under CA > SA > Excel Services > Global Settings > Load Balancing Scheme > change the settings from ‘Workbook URL’ to ‘Local’. but same issue

4. App Pool Recycle
http://blogs.technet.com/b/excel_services__powerpivot_for_sharepoint_support_blog/archive/2013/01/24/excel-services-2010-quot-unable-to-process-the-request-quot-maximum-private-bytes-size-exceeded-unable-to-complete-the-operation.aspx

5. Excel Service Account for CDB permisions
http://support.microsoft.com/kb/981293

6. Edit host entry and loopback FQDN
7. IISReset
8. Reboot
9. Re-run psconfigui

10 no route site collection had been create when testing the service. https://<your application name>/_layouts/EwaStringsHandler.ashx/

11. Disable ASP.Net Impersonation in IIS http://blog.armgasys.com/?p=133

ULS Log Errors:

System.TypeInitializationException: The type initializer for ‘Microsoft.Office.Excel.Server.CalculationServer.ExcelServerApp’ threw an exception. —> System.IO.FileLoadException: Loading this assembly would produce a different grant set from other instances. (Exception from HRESULT: 0x80131401)     at Microsoft.Office.Server.Diagnostics.FirstChanceHandler.ExceptionFilter(Boolean fRethrowException,

TryBlock tryBlock, FilterBlock filter, CatchBlock catchBlock, FinallyBlock finallyBlock)     at Microsoft.Office.Server.Diagnostics.ULS.SendWatsonOnExceptionTag(UInt32 tagID, ULSCatBase categoryID, String output, Boolean

fRethrowException, TryBlock tryBlock, CatchBlock catchBlock, FinallyBlock finallyBlock)     at Microsoft.Office.Excel.Server.CalculationServer.ExcelServerApp..ctor()     at Mic…

There was an error in communicating with Excel Calculation Services

http://SPAppServer013:32843/0753ec6cff654ce09f369241f98d06a7/ExcelService*.asmx exception: An error has occurred. [Session:  User: domainSPFarmAdmSvcAct].

ServerSession.ProcessServerSessionException: An exception thrown during ExecuteWebMethod failed to convert to ECSException for server:

http://SPAppServer013:32843/0753ec6cff654ce09f369241f98d06a7/ExcelService*.asmx, method: GetHealthScore, ex:

Microsoft.Office.Excel.Server.CalculationServer.Proxy.ServerSessionException: An error has occurred. —>

System.ServiceModel.FaultException: The server was unable to process the request due to an internal error.  For more information about the error, either turn on IncludeExceptionDetailInFaults (either from ServiceBehaviorAttribute or from the <serviceDebug> configuration behavior) on the server in order to send the exception information back to the client, or turn on tracing as per the Microsoft .NET Framework 3.0 SDK documentation and inspect the se…..

Event Viewer Logs

Error 1:
I saw this error logged every 5 mins in on a WFE server.

Log Name:      Application
Source:        Microsoft-SharePoint Products-SharePoint Server
Date:          12/15/2014 1:04:20 PM
Event ID:      2159
Task Category: Unified Logging Service
Level:         Error
Keywords:      
User:          companySPFarmSvcAct
Computer:      SPWFEServer01.company.com
Description:
Event 5240 (Excel Services Application) of severity ‘Error’ occurred 10 more time(s) and was suppressed in the event log
Event Xml:
<Event xmlns=”http://schemas.microsoft.com/win/2004/08/events/event”>
  <System>
    <Provider Name=”Microsoft-SharePoint Products-SharePoint Server” Guid=”{C33B4F2A-64E9-4B39-BD72-F0C2F27A619A}” />
    <EventID>2159</EventID>
    <Version>14</Version>
    <Level>2</Level>
    <Task>31</Task>
    <Opcode>0</Opcode>
    <Keywords>0x4000000000000000</Keywords>
    <TimeCreated SystemTime=”2014-12-15T19:04:20.673990100Z” />
    <EventRecordID>510279</EventRecordID>
    <Correlation />
    <Execution ProcessID=”7012″ ThreadID=”6000″ />
    <Channel>Application</Channel>
    <Computer>SPWFEServer01.company.com</Computer>
    <Security UserID=”S-1-5-21-1627688274-1190192956-3999157559-1223″ />
  </System>
  <EventData>
    <Data Name=”int0″>5240</Data>
    <Data Name=”string1″>Excel Services Application</Data>
    <Data Name=”int2″>10</Data>
  </EventData>
</Event>

Error 2:
On WFe 3 server:

Log Name:      Application
Source:        Microsoft-SharePoint Products-Excel Services Application
Date:          12/15/2014 1:13:41 PM
Event ID:      5240
Task Category: Web Front End
Level:         Error
Keywords:      
User:          companySPFarmSvcAct
Computer:      SPWFEServer03.company.com
Description:
There was an error in communicating with Excel Calculation Services 

http://SPAppServer03:32843/0753ec6cff654ce09f369241f98d06a7/ExcelService*.asmx exception: An error has occurred.
[Session: 
User: companySPFarmSvcAct].
Event Xml:
<Event xmlns=”http://schemas.microsoft.com/win/2004/08/events/event”>
  <System>
    <Provider Name=”Microsoft-SharePoint Products-Excel Services Application” Guid=”{278E40D0-FDAA-4EB4-AB6B-9E0AD6BDBE79}” />
    <EventID>5240</EventID>
    <Version>14</Version>
    <Level>2</Level>
    <Task>8</Task>
    <Opcode>0</Opcode>
    <Keywords>0x8000000000000000</Keywords>
    <TimeCreated SystemTime=”2014-12-15T19:13:41.610718500Z” />
    <EventRecordID>529546</EventRecordID>
    <Correlation />
    <Execution ProcessID=”5588″ ThreadID=”7280″ />
    <Channel>Application</Channel>
    <Computer>SPWFEServer03.company.com</Computer>
    <Security UserID=”S-1-5-21-1627688274-1190192956-3999157559-1223″ />
  </System>
  <EventData>
    <Data Name=”string0″>http://SPAppServer03:32843/0753ec6cff654ce09f369241f98d06a7/ExcelService*.asmx</Data>
    <Data Name=”string1″>An error has occurred.</Data>
    <Data Name=”string2″>
    </Data>
    <Data Name=”string3″>companySPFarmSvcAct</Data>
  </EventData>
</Event>

Error 3:
Log Name:      Application
Source:        Microsoft-SharePoint Products-Excel Services Application
Date:          12/15/2014 11:28:31 AM
Event ID:      5239
Task Category: Web Front End
Level:         Error
Keywords:      
User:          companySPFarmSvcAct
Computer:      SPWFEServer02.company.com
Description:
There was an error in communicating with Excel Calculation Services 

http://SPAppServer03:32843/0753ec6cff654ce09f369241f98d06a7/ExcelService*.asmx exception: The remote server returned an error: (503) Server 

Unavailable.
[Session: 
User: companySPFarmSvcAct].
Event Xml:
<Event xmlns=”http://schemas.microsoft.com/win/2004/08/events/event”>
  <System>
    <Provider Name=”Microsoft-SharePoint Products-Excel Services Application” Guid=”{278E40D0-FDAA-4EB4-AB6B-9E0AD6BDBE79}” />
    <EventID>5239</EventID>
    <Version>14</Version>
    <Level>2</Level>
    <Task>8</Task>
    <Opcode>0</Opcode>
    <Keywords>0x8000000000000000</Keywords>
    <TimeCreated SystemTime=”2014-12-15T17:28:31.611714800Z” />
    <EventRecordID>502287</EventRecordID>
    <Correlation />
    <Execution ProcessID=”2068″ ThreadID=”8976″ />
    <Channel>Application</Channel>
    <Computer>SPWFEServer02.company.com</Computer>
    <Security UserID=”S-1-5-21-1627688274-1190192956-3999157559-1223″ />
  </System>
  <EventData>
    <Data Name=”string0″>http://SPAppServer03:32843/0753ec6cff654ce09f369241f98d06a7/ExcelService*.asmx</Data>
    <Data Name=”string1″>The remote server returned an error: (503) Server Unavailable.</Data>
    <Data Name=”string2″>
    </Data>
    <Data Name=”string3″>companySPFarmSvcAct</Data>
  </EventData>
</Event>

Error 4:
Log Name:      Application
Source:        Microsoft-SharePoint Products-Excel Services Application
Date:          12/15/2014 11:28:31 AM
Event ID:      5231
Task Category: Web Front End
Level:         Error
Keywords:      
User:          companySPFarmSvcAct
Computer:      SPWFEServer02.company.com
Description:
Unable to reach Excel Calculation Services http://SPAppServer03:32843/0753ec6cff654ce09f369241f98d06a7/ExcelService*.asmx.
[Session: 
User: companySPFarmSvcAct]
Event Xml:
<Event xmlns=”http://schemas.microsoft.com/win/2004/08/events/event”>
  <System>
    <Provider Name=”Microsoft-SharePoint Products-Excel Services Application” Guid=”{278E40D0-FDAA-4EB4-AB6B-9E0AD6BDBE79}” />
    <EventID>5231</EventID>
    <Version>14</Version>
    <Level>2</Level>
    <Task>8</Task>
    <Opcode>0</Opcode>
    <Keywords>0x8000000000000000</Keywords>
    <TimeCreated SystemTime=”2014-12-15T17:28:31.611714800Z” />
    <EventRecordID>502286</EventRecordID>
    <Correlation />
    <Execution ProcessID=”2068″ ThreadID=”8976″ />
    <Channel>Application</Channel>
    <Computer>SPWFEServer02.company.com</Computer>
    <Security UserID=”S-1-5-21-1627688274-1190192956-3999157559-1223″ />
  </System>
  <EventData>
    <Data Name=”string0″>http://SPAppServer03:32843/0753ec6cff654ce09f369241f98d06a7/ExcelService*.asmx</Data>
    <Data Name=”string1″>
    </Data>
    <Data Name=”string2″>companySPFarmSvcAct</Data>
  </EventData>
</Event>

Resolution:

There are few different resolutions you can try. In my case mostly restarting the Excel Calculation Services resolved the issue.

1. Restart excel calculations services
2. Recycle app pool
3. Make sure the account running excel calculation service is not locked
4. Reboot the server

1. Restart excel calculations services

I stopped and started the Excel Calculation services from within SharePoint Central Administration on following servers in this order:

SPAppServer01 – App
SPAppServer02 – App
SPAppServer03 – App

To stop and then start the Excel Calculation Services follow steps:
1. Go to SharePoint Central Admin site

2. Click System Settings > Manage Servers in This Farm

3. Find the servers in the farm that are running ‘Excel Calculation Services’

4. Click on one server name from that list that is running the service. Start with Web Server (if one is running the service),

5. On next page titled ‘Services on Server: servername ‘ locate the ‘Excel Calculation Services’ and click stop under action column

SharePoint Excel Services Restart Excel Calculation Services in Central Admin
SharePoint Excel Services Restart Excel Calculation Services in Central Admin

6. Refresh the page after few seconds and make sure the service is in stopped status

7. Once the Service show stopped status, click on Start under action column to start the service

8. Refresh the page for few seconds to make sure the Excel Calculation Service shows Started under status column

9. Go back to the page under step 2 above and follow steps for next server

10. Test one Excel file on the Sharepoint site by opening it in browser

Instead of restarting the servers or performing doing IISReset, stopping and starting the Excel service helped resolve the issue. Due to farm serving other critical apps out of SharePoint, server reboot and IISReset were not an option.

References:

  1. Error communicating with Excel Services Application – Events 5231 5239 5240
  2. Stefan Johansson – http://www.stefanjohansson.org/2013/03/excel-services-unable-to-process-the-request-wait-a-few-minutes-and-try-performing-this-operation-again/
  3. Armgasys – http://blog.armgasys.com/?p=133

Keywords:
  • SharePoint Unable to process the request
  • Excel Unable to process the request
  • error in communicating with Excel Calculation Services
  • Excel Services Unable to process the request
  • SharePoint farm Unable to process the request
  • SharePoint Excel Services Gotchas
  • SharePoint Excel Services issues
  • SharePoint Excel Services remedies
  • SharePoint Excel Services fixes
  • Event Viewer Error 5231
  • Event Viewer Error 5239
  • Event Viewer Error 5240
  • Event Viewer Error 2159
  • Event ID 5231
  • Event ID 5239
  • Event ID 5240
  • Event ID 2159
  • Fix for Event Viewer Error 5231
  • Fix for Event Viewer Error 5239
  • Fix for Event Viewer Error 5240
  • Fix for Event Viewer Error 2159

SharePoint Excel Services Unable to Save Workbook

Friday, November 14th, 2014

SharePoint Excel Services Unable to Save Workbook

Version: 1.0

Scenario:

A user opens an Excel file for editing using ‘Edit in Browser’ option.

SharePoint Excel Web Services - Edit In Browser an Excel File
SharePoint Excel Web Services – Edit In Browser an Excel File

Error:

After sometime in the edit mode, the user gets the message

Unable to Save Workbook

We will try again automatically, but you should try to save your workbook to another location or click Open in Excel.

There will be an error dialog box that says:
Your changes could not be saved. It is recommended that you select Open in Excel or try to save your changes to a different location immediately. If your changes can still not be saved after a few more minutes, your session will be ended to prevent any more loss of data.

SharePoint Excel Web Services - Unable to Save Workbook Error
SharePoint Excel Web Services – Unable to Save Workbook Error

Event Viewer Errors on SharePoint Application Servers running Excel Services:

1. Error on App Server 1 Event ID 7935

Log Name:      Application
Source:        Microsoft-SharePoint Products-Excel Services Application
Date:          11/6/2014 1:53:25 PM
Event ID:      7935
Task Category: Excel Calculation Services
Level:         Information
Keywords:    
User:          domainSPFarmAct
Computer:      SPAppServer1.domain.com
Description:
Unable to save the following workbook: http://sharepoint.domain.com/sites/BizSegment/Dept1/Financial

Documents/MonthlyKPIReport2015.xlsx.
Event Xml:
<Event xmlns=”http://schemas.microsoft.com/win/2004/08/events/event”>
  <System>
    <Provider Name=”Microsoft-SharePoint Products-Excel Services Application” Guid=”{278E40D0-FDAA-4EB4-AB6B-9E0AD6BDBE79}” />
    <EventID>7935</EventID>
    <Version>14</Version>
    <Level>4</Level>
    <Task>2</Task>
    <Opcode>0</Opcode>
    <Keywords>0x8000000000000000</Keywords>
    <TimeCreated SystemTime=”2014-11-06T19:53:25.232766900Z” />
    <EventRecordID>720780</EventRecordID>
    <Correlation ActivityID=”{00020DF6-D14B-13A9-0000-000050F7B00B}” />
    <Execution ProcessID=”5088″ ThreadID=”7456″ />
    <Channel>Application</Channel>
    <Computer>SPAppServer1.domain.com</Computer>
    <Security UserID=”S-1-5-21-1627688274-1190192956-3999157559-1223″ />
  </System>
  <EventData>
    <Data Name=”string0″>http://sharepoint.domain.com/sites/BizSegment/Dept1/DocumentLib/MonthlyKPIReport2015.xlsx</Data>
  </EventData>
</Event>

2. Error on App Server 1 with Event ID 0 – Symantec Protection for SharePoint Servers

Log Name:      Application
Source:        Symantec Protection for SharePoint Servers Service
Date:          11/6/2014 1:57:35 PM
Event ID:      0
Task Category: None
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      SPAppServer1.domain.com
Description:
Module=ScanObject.Scan | Exception message=Failed to connect to an IPC Port: The system cannot find the file specified.
  | Stack=
Server stack trace:
   at System.Runtime.Remoting.Channels.Ipc.IpcPort.Connect(String portName, Boolean secure, TokenImpersonationLevel impersonationLevel,

Int32 timeout)
   at System.Runtime.Remoting.Channels.Ipc.ConnectionCache.GetConnection(String portName, Boolean secure, TokenImpersonationLevel

level, Int32 timeout)
   at System.Runtime.Remoting.Channels.Ipc.IpcClientTransportSink.ProcessMessage(IMessage msg, ITransportHeaders requestHeaders, Stream

requestStream, ITransportHeaders& responseHeaders, Stream& responseStream)
   at System.Runtime.Remoting.Channels.BinaryClientFormatterSink.SyncProcessMessage(IMessage msg)

Exception rethrown at [0]:
   at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
   at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
   at Symantec.Sharepoint.Shared.InterfaceLibrary.ILoadBalancingService.GetAvailableScanEngine(ScanEngine& oScanEngine)
   at Symantec.Sharepoint.RTCommandClient.RTCommand.GetAvailableScanEngine(ScanEngine& oScanEngine)
   at Symantec.Sharepoint.ScanLibrary.ScanObject. (IntPtr inILockBytes, Int32& pdwStatus, String& wzVirusInfo)
Event Xml:
<Event xmlns=’http://schemas.microsoft.com/win/2004/08/events/event’><System><Provider Name=’Symantec Protection for SharePoint Servers

Service’/><EventID Qualifiers=’0′>0</EventID><Level>2</Level><Task>0</Task><Keywords>0x80000000000000</Keywords><TimeCreated

SystemTime=’2014-11-

06T19:57:35.000000000Z’/><EventRecordID>720783</EventRecordID><Channel>Application</Channel><Computer>SPAppServer1.domain.com</Computer

><Security/></System><EventData><Data>Module=ScanObject.Scan | Exception message=Failed to connect to an IPC Port: The system cannot

find the file specified.
  | Stack=
Server stack trace:
   at System.Runtime.Remoting.Channels.Ipc.IpcPort.Connect(String portName, Boolean secure, TokenImpersonationLevel impersonationLevel,

Int32 timeout)
   at System.Runtime.Remoting.Channels.Ipc.ConnectionCache.GetConnection(String portName, Boolean secure, TokenImpersonationLevel

level, Int32 timeout)
   at System.Runtime.Remoting.Channels.Ipc.IpcClientTransportSink.ProcessMessage(IMessage msg, ITransportHeaders requestHeaders, Stream

requestStream, ITransportHeaders&amp; responseHeaders, Stream&amp; responseStream)
   at System.Runtime.Remoting.Channels.BinaryClientFormatterSink.SyncProcessMessage(IMessage msg)

Exception rethrown at [0]:
   at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
   at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData&amp; msgData, Int32 type)
   at Symantec.Sharepoint.Shared.InterfaceLibrary.ILoadBalancingService.GetAvailableScanEngine(ScanEngine&amp; oScanEngine)
   at Symantec.Sharepoint.RTCommandClient.RTCommand.GetAvailableScanEngine(ScanEngine&amp; oScanEngine)
   at Symantec.Sharepoint.ScanLibrary.ScanObject. (IntPtr inILockBytes, Int32&amp; pdwStatus, String&amp; wzVirusInfo)

</Data></EventData></Event>

3. Error on App Server 2 – Event ID 8031

Log Name:      Application
Source:        Microsoft-SharePoint Products-SharePoint Foundation
Date:          11/6/2014 2:15:01 PM
Event ID:      8031
Task Category: Topology
Level:         Error
Keywords:    
User:          domainSPFarmAct
Computer:      SPAppServer2.domain.com
Description:
An exception occurred while updating addresses for connected app {6457c0c9-76ff-4fb6-a94e-4ca7928efa06_65529aa3-7640-40b8-b262-

e557c37f87e9}. The uri endpoint information may be stale. System.InvalidOperationException: The requested application could not be found.
   at Microsoft.SharePoint.SPTopologyWebServiceApplicationProxy.ProcessCommonExceptions(Uri endpointAddress, String operationName,

Exception ex, SPServiceLoadBalancerContext context)
   at Microsoft.SharePoint.SPTopologyWebServiceApplicationProxy.ExecuteOnChannel(String operationName, CodeBlock codeBlock)
   at Microsoft.SharePoint.SPTopologyWebServiceApplicationProxy.GetEndPoints(Guid serviceId)
   at Microsoft.SharePoint.SPConnectedServiceApplicationAddressesRefreshJob.Execute(Guid targetInstanceId)
Event Xml:
<Event xmlns=”http://schemas.microsoft.com/win/2004/08/events/event”>
  <System>
    <Provider Name=”Microsoft-SharePoint Products-SharePoint Foundation” Guid=”{6FB7E0CD-52E7-47DD-997A-241563931FC2}” />
    <EventID>8031</EventID>
    <Version>14</Version>
    <Level>2</Level>
    <Task>13</Task>
    <Opcode>0</Opcode>
    <Keywords>0x4000000000000000</Keywords>
    <TimeCreated SystemTime=”2014-11-06T20:15:01.064016600Z” />
    <EventRecordID>864875</EventRecordID>
    <Correlation ActivityID=”{7C78DFF9-926C-43B6-AEEF-60EDDB502CA2}” />
    <Execution ProcessID=”13816″ ThreadID=”18532″ />
    <Channel>Application</Channel>
    <Computer>SPAppServer2.domain.com</Computer>
    <Security UserID=”S-1-5-21-1627688274-1190192956-3999157559-1223″ />
  </System>
  <EventData>
    <Data Name=”string0″>6457c0c9-76ff-4fb6-a94e-4ca7928efa06_65529aa3-7640-40b8-b262-e557c37f87e9</Data>
    <Data Name=”string1″>System.InvalidOperationException: The requested application could not be found.
   at Microsoft.SharePoint.SPTopologyWebServiceApplicationProxy.ProcessCommonExceptions(Uri endpointAddress, String operationName,

Exception ex, SPServiceLoadBalancerContext context)
   at Microsoft.SharePoint.SPTopologyWebServiceApplicationProxy.ExecuteOnChannel(String operationName, CodeBlock codeBlock)
   at Microsoft.SharePoint.SPTopologyWebServiceApplicationProxy.GetEndPoints(Guid serviceId)
   at Microsoft.SharePoint.SPConnectedServiceApplicationAddressesRefreshJob.Execute(Guid targetInstanceId)</Data>
  </EventData>
</Event>

Remedy:

During the issue the users impacted can use the Save As option in Excel Web Viewer to save the file locally if they want to edit the file and upload it later after issue is fixed.
Excel Web Services - Save As option
Excel Web Services – Save As option
The user will loose the changes they will do during this issue if they will not save a copy and then edit the saved copy.
If you continue making changes in Excel file within Excel Web Viewer, you will get a prompt with following message. At this point you have lost the changes you would have made during this session.
Either due to communication issues or a server problem, you need to be rejoined to your editing session. You may have lost changes that you made recently.
Excel Web Viewer - Session Lost and Lost recent changes Message
Excel Web Viewer – Session Lost and Lost recent changes Message
At this point go to your document library location where your Excel file is located and review the Modified date. You will notice that the modified date will not reflect that it was modified. Same will be true for version number if versioning was enabled for that library.
Excel Web Services issue - File changes not Saved
Excel Web Services issue – File changes not Saved
At this point the user will feel angry, frustrated or a lost soul based no their work. Don’t loose hope. There is some green light. Its coming in next section.

Resolution:

The above issue with Excel Web View not able to save could happen due to number of reasons. In this case when I started reviewing the SharePoint Farm for the application servers running Excel Services, I found that whenever there was error in Excel web viewer for this issue, around the same time there was an error log for Symantec Protection for SharePoint Servers. Now you know why I listed the Symantec error in the Errors section. 
I went to SharePoint Central Admin website and looked at the Symantec settings. I noticed the service was showing as stopped on one application server.
SharePoint Central Admin - Symantec Protection Service Stopped on one App Server
SharePoint Central Admin – Symantec Protection Service Stopped on one App Server
Next I looked into the Windows Services console and found the service ‘Symantec Protection 6.0 for SharePoint Servers’ in STOPPING status. In that state you can not start, stop or restart that service.
Windows Services Console - Symantec Protection Service in STOPPING State
Windows Services Console – Symantec Protection Service in STOPPING State
I terminated the Symantec Protection SPSS process using Windows Task Manager on the server. Then I started that service successfully. Later I went to SharePoint Central Admin website > Symantec Protection section and looked at that server and it was showing as Started.
SharePoint Central Admin - Symantec Protection Service in Started state on an App Server
SharePoint Central Admin – Symantec Protection Service in Started state on an App Server
Next I opened the Excel file from SharePoint location in Excel Web services in edit mode. I was able to edit the file and Excel Web Services saved the changes back to the file. I then reviewed the Modified date and it was showing the file was modified at recent time with my name under Modified Bu field. 
Excel Web Services - File changes Saved
Excel Web Services – File changes Saved
Finally the Excel Services came back on track.
Keywords:
  • Excel can not save changes
  • Excel services can not save changes
  • Excel web services can not save changes
  • Excel services lost changes
  • SharePoint Excel services lost changes
  • SharePoint Excel services not saving changes
  • SharePoint Excel services not able to edit files
  • SharePoint Excel services not able to update file
  • SharePoint Excel services oddies
  • SharePoint Excel services issues
  • Can not modify Excel in SharePoint
  • Can not modify Excel in Excel Web Services

SharePoint Excel Data Refresh Common Issues

Monday, November 3rd, 2014

SharePoint Excel Data Refresh – Common Issues

Version: 1.0

SharePoint Excel Services requires different bits and pieces to work correctly in order for it to deliver your BI Reports correctly. There are times when you have to review and update the configurations in order to make it work.

I’ll cover some of my experiences related to issues and fixes in working with the SharePoint Excel Services in this publication.

Common SharePoint Excel Services Issues

Following are the few common issues that can happen with SharePoint Excel Services and the resolution of those issues.

a. Unable to refresh data for a data connection in the workbook. … The following connections failed to refresh

a. Unable to refresh data for a data connection in the workbook. … The following connections failed to refresh

I created a nice report in Microsoft Excel using the SSAS (SQL Server Analysis Services) as the data source. I was able to refresh the data connection within MS Excel file to pull the latest data or apply filters to get the data I wanted. No issue so far and every thing looks great.

MS Excel Report with SSAS Data Connection - Data Refresh Option
MS Excel Report with SSAS Data Connection – Data Refresh Working

I uploaded my report to a Business Intelligence (BI) Center site on SharePoint. I launched the report using Excel Web Services and I was able to see the data. I got the following error when I tried to do the Data connection refresh in an attempt to pull the latest data from SSAS data source.

Error:
Unable to refresh data for a data connection in the workbook. Try again or contact your system administrator. The following connections failed to refresh:
SSASserverName – InstanceName SSAS database ProjectDW TimeReport

SharePoint Excel Service Excel Report with SSAS Data Connection - Data Refresh Error
SharePoint Excel Service Excel Report with SSAS Data Connection – Data Refresh Error

More precisely, here is the what the screen looked like. Also I couldn’t apply the filters and my nice report turned out to be ‘acting not so nicely’.

SharePoint Excel Service Excel Report with SSAS Data Connection - Data Refresh Error Fullpage
SharePoint Excel Service Excel Report with SSAS Data Connection – Data Refresh Error Fullpage

Issue Cause

I started the SharePoint Log Viewer on the server and then launched my Excel report again in Excel Web services to reproduce the error. Paused the live monitoring of the ULS logs and started reviewing the log information. I found the line item in the log pertaining to data refresh issue.

Area: Excel Services Application
Category: External Data
Event ID: 2052
Level: Information

Highlighting the log entry revealed the message detail about it.

The workbook ‘http://sharepoint.domain.com/sites/dept1/team2/deptsolutions/BI/Excel Analysis Reports/SPExcelBIReport_ByFM.xlsx’ attempted to access external data using the unsupported provider ‘Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=SSAS Project Control;Data Source=SSASServerNameInstanceName;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error’. [Session: 1.V21.3V9WQUVIjSGKsFX87MBjr90.5.en-US5.en-US73.+0360#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-006036.289039c6-fe3c-4074-ac1f-48520e7f45a41.N User: 0#.w|domainfmasood]

SharePoint LogViewer - Excel Data Refresh error with SSAS Cube - Unsupported Data Provider Used
SharePoint LogViewer – Excel Data Refresh error with SSAS Cube – Unsupported Data Provider Used

Though, above information is sufficient clue to go about fixing the issue, I’m putting few more log entries that were logged for the same session.

Few ULS Log Entries:

1. 
ExternalSource.GetExternalKeyAndStateAndMarkUsed: Populating the session’s credentials for external data. Index=0, Credentials=[8126098, domainfmasood]

2. 
ConnectionRequest.ConnectionRequest: New connection request. SessionId=1.V21.3V9WQUVIjSGKsFX87MBjr90.5.en-US5.en-US73.+0360#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-006036.289039c6-fe3c-4074-ac1f-48520e7f45a41.N, WorkbookVersion=ConnectionInfo.WorkbookVersion: Uri=http://sharepoint.domain.com/sites/dept1/team2/deptsolutions/BI/Excel Analysis Reports/SPExcelBIReport_ByFM.xlsx, Version=Thursday, 23 October 2014 19:19:17

3.
The workbook ‘http://sharepoint.domain.com/sites/dept1/team2/deptsolutions/BI/Excel Analysis Reports/SPExcelBIReport_ByFM.xlsx’ attempted to access external data using the unsupported provider ‘Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=SSAS Project Control;Data Source=SSASServerNameInstanceName;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error’. [Session: 1.V21.3V9WQUVIjSGKsFX87MBjr90.5.en-US5.en-US73.+0360#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-006036.289039c6-fe3c-4074-ac1f-48520e7f45a41.N User: 0#.w|domainfmasood]

4. 
Refresh failed for ‘SSASServerName_InstanceName SSAS Project Control ProjectDW ProjectTimeReports’ in the workbook ‘http://sharepoint.domain.com/sites/dept1/team2/deptsolutions/BI/Excel Analysis Reports/SPExcelBIReport_ByFM.xlsx’. [Session: 1.V21.3V9WQUVIjSGKsFX87MBjr90.5.en-US5.en-US73.+0360#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-006036.289039c6-fe3c-4074-ac1f-48520e7f45a41.N User: 0#.w|domainfmasood]

5.
ExternalSource.ValidateConnection: Unable to get a connection: Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionException: Exception of type ‘Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionException’ was thrown.     at Microsoft.Office.Excel.Server.CalculationServer.ConnectionManager.ValidateConnectionInfo(ConnectionRequest connectionRequest, ConnectionInfo connectionInfo)     at Microsoft.Office.Excel.Server.CalculationServer.ConnectionManager.GetConnection(ConnectionRequest connectionRequest, ExtendedConnectionInfo extendedConnInfo, Credentials credentials, Int64 privateConnectionId, Boolean auditConnection)     at Microsoft.Office.Excel.Server.CalculationServer.ExternalSource.TryGetValidatedConnection(Request request, Credentials credentials, Extended…

6.
…ConnectionInfo extendedConnectionInfo, Boolean shouldReportFailure, Boolean auditConnection, Connection& connectionOut). sessionId=1.V21.3V9WQUVIjSGKsFX87MBjr90.5.en-US5.en-US73.+0360#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-006036.289039c6-fe3c-4074-ac1f-48520e7f45a41.N, externalSource=SSASServerName_InstanceName SSAS Project Control ProjectDW ProjectTimeReports

7.
ConnectionInfoManager.InitConnInfoFromOdcFile: ODC File Uri is local: C:UsersfmasoodDocumentsMy Data SourcesSSASServerName_InstanceName SSAS Project Control ProjectDW ProjectTimeReports.odc

8.
ExternalSource.ExecuteOperation: We exhausted all available connection information. Exception: Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionInfoException: Exception of type ‘Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionInfoException’ was thrown.     at Microsoft.Office.Excel.Server.CalculationServer.ConnectionInfoManager.GetConnectionInfo(Request request, String externalSourceName, Int32 externalSourceIndex, Boolean& shouldReportFailure)     at Microsoft.Office.Excel.Server.CalculationServer.ExternalSource.ExecuteOperation(Request request, ExternalSourceStateInfo externalSourceStateInfo, ExternalSourceStateInfo prevExternalSourceStateInfo, Int32 index, ConnectionInfoManager connectionInfoManager, ExternalDataScenario scenario, DataOperation dataOpe…

9.
…ration, Boolean verifyPreOperationConnection), Data Connection Name: SSASServerName_InstanceName SSAS Project Control ProjectDW ProjectTimeReports, SessionId: 1.V21.3V9WQUVIjSGKsFX87MBjr90.5.en-US5.en-US73.+0360#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-006036.289039c6-fe3c-4074-ac1f-48520e7f45a41.N, UserId: 0#.w|domainfmasood

10.
ExcelService.PostProcessRequest: finished request of type RefreshAll

Issue Resolution

I went to SharePoint Central Administration site and reviewed the Trusted Data Providers under Excel Services. The location of the configuration is Central Admin home > Application Management > Manage Service Applications > Click on your Excel Service Application > Trusted Data Providers.

In SharePoint 2010, MSOLAP.5 data provider is not present default. See the screenshot below.

SharePoint Excel Services - Trusted Data Provider - MSOLAP.5 not present by default
SharePoint Excel Services – Trusted Data Provider – MSOLAP.5 not present by default

Click on Add Trusted Data Provider link on top left and configure the MSOLAP.5 data provider.

SharePoint Excel Services - Trusted Data Provider - Configure MSOLAP.5 Data Provider
SharePoint Excel Services – Trusted Data Provider – Configure MSOLAP.5 Data Provider

Once added, you should see the MSOLAP.5 data provider in the list.

SharePoint Excel Services - Trusted Data Provider - MSOLAP.5 Data Provider Added
SharePoint Excel Services – Trusted Data Provider – MSOLAP.5 Data Provider Added

Once I had the MSOLAP.5 added as the Trusted Data Provider, in excel services, refresh all data connection feature started working. At that point I was also able to use filters in my report without any errors.

My SharePoint BI report lived happily ever after.

References:
1. Primer on SharePoint Excel Data Refresh Settings
2. SQL Server Version Numbers
3. Excel Services Data Refresh Flowchart – Review the screenshot titled E and section E in the table at bottom right for information related to this issue.
4. Add MSOLAP.5 as a Trusted Data Provider in Excel Services
5. How to: Install the Analysis Services OLE DB Provider on an Excel Services Computer

Keywords:

  • Fix Excel Data Refresh Issue
  • Fix Excel Data Refresh error
  • Fix Excel Data Refresh connection failure
  • Excel Data Refresh Issue
  • Excel Data Refresh connection failure
  • Data refresh issue due to MSOLAP.5
  • Missing Data Provider MSOLAP.5
  • Adding a new Trusted Data Provider
  • Trusted Data Provider in Excel Services
  • Trusted Data Provider in SharePoint Excel Services
  • Unable to Refresh SSAS Data from Excel
  • Event ID 2052
  • External Data Connection Failure
  • External Data Refresh Failure
  • SharePoint BI Report Refresh Issue
  • SharePoint BI Report data Refresh Issue
  • How to fix Excel Data Connection issue
  • How to fix Excel Data refresh issue
  • How to fix Excel Data Connection refresh issue
  • Get the real time data in Excel Services

Symantec Antivirus for SharePoint Blocked Linked Excel Files

Wednesday, October 8th, 2014

Symantec Protection for SharePoint Blocked Linked Excel Files

Issue Summary

Symantec Protection for SharePoint Servers (SPSS) 6.0 was deployed in the SharePoint farm. After deployment, certain files are getting blocked by it even though Symantec Antivirus on desktop had cleared those as clean files.

In one of my previous post Linked Excel Files and SharePoint, I covered the details on how linked files work in SharePoint.

Error:

2 – The file: FileNameByFM.xlsx -contains Unscannable Content. Reason: Container Size Violation | Container Size Violation -Status: Blocked

This file cannot be saved to the document library. If you want to save this file to the document library, clean the file using alternative virus scanning software and try saving it again.

Troubleshoot issues with Microsoft SharePoint Foundation.

File can not be uploaded to SharePoint dueto Symantec Antivirus for SharePoint SPSS
File can not be uploaded to SharePoint dueto Symantec Antivirus for SharePoint SPSS

If you are trying to upload a new file, the file is not uploaded. If you are trying to save after edit / checking out, file, it doesn’t work either.

Cause:

The issue is due to the linked MS Excel files that were on user desktop. Only container file was uploaded to the SharePoint. The file was 25 MB in size.

How to find linked files in MS Excel

Follow the steps listed below to find the linked files:
1. Open the Microsoft Excel file from SharePoint
2. Go to Data ribbon tab and then click Edit Links under Connections group
3. Review the linked file(s) for Location. Update / remove as it applies to your container file
4. Save and close the file

Microsoft Excel - Edit Linked Files
Microsoft Excel – Edit Linked Files

You might have to do these step by downloading a copy of that file on your desktop and then make above changes.

Microsoft Excel - Edit Linked Files - Review Location
Microsoft Excel – Edit Linked Files – Review Location

Resolution:

Even removing the linked file didn’t help in this case. The linked file was unnecessary in this scenario. Symantec Protection for SharePoint was still treating that file as a container file. Make sure you review the data in container Excel file that might be coming from the linked file.

I found that Symantec Protection Engine portal on the SharePoint server (http://localhost:8004) has a default filter policy for container handling. Symantec Protection Engine is a separate component of Symantec Protection for SharePoint Servers.

Container File Processing Limits:
Following filter policies were defined by default.

Stop processing a container file when any of the following limits is met or exceeded.
a. Time to extract file meets or exceeds: 180 seconds
b. Maximum extract size of the file meets or exceeds: 100 MB
c. Maximum extract depth of the file meets or exceeds: 10 levels

When processor limit is met or exceeded: Deny access to the file and generate a log entry.

Symantec Protection for SharePoint Servers - Filtering Policies on Container Handling
Symantec Protection for SharePoint Servers – Filtering Policies on Container Handling

I changed the container file  policy to: Allow access to the file and generate a log entry.

That seemed to resolve the issue.

Risk Assessment:

In this scenario, due to internal only SharePoint, the decision was made to allow access to files in case of container file processing limits are reached or exceeded. The decision was in favor of application availability rather strict security.

Keywords:

  • Symantec Antivirus for SharePoint
  • Symantec Protection for SharePoint
  • Symantec Protection for SharePoint blocks file
  • Symantec Protection for SharePoint does not allow file upload
  • Symantec Protection blocks file upload in SharePoint
  • File upload blocked by Symantec antivirus
  • File upload blocked in SharePoint
  • Unscannable Contents in SharePoint
  • Container Size violation in SharePoint
  • SharePoint File upload blocked by antivirus

Linked Excel Files and SharePoint

Wednesday, September 17th, 2014

Linked Excel Files and SharePoint

V: 1.0

I’ve been working lately with Microsoft Excel data report files that were linked and were uploaded to SharePoint. There are few scenarios where the linked files can cause issues if not used properly. I’m describing few scenarios below:

Scenario 1: User created MS Excel files on the desktop, linked those files (desktop or shared drive). then uploaded both files to SharePoint

Scenario 2: User created MS Excel files on the desktop, linked those files (desktop or shared drive). then uploaded only container file to SharePoint

Scenario 3: User created MS Excel files on the desktop, linked those files (desktop or shared drive). then uploaded both files to SharePoint and updated the linked files using their SharePoint links

Scenario 2 is common among Excel users. Users create linked excel files and upload only the main file aka the container file. In most cases the linked file was on the desktop of the user. Now when another user tries to open the Excel container file from SharePoint or from their desktop by copying it, they get errors / warning messages. That is due to container file not able to reach to author’s desktop file from SharePoint.

I copied the container excel file on my desktop and went through it.

MS Excel Container File that has linked files
MS Excel Container File that has linked files

Click on the File menu > Info. Under security warning section you will see the warning details.

MS Excel Container File that has linked files - File Info Security Warning section
MS Excel Container File that has linked files – File Info Security Warning section

Now you click on Enable Content from either the top middle  of file or within File > Info section in an attempt to get the complete data and you are presented with a warning as shown in following image.

Error:
We can’t update some of the links in your workbook right now.
You can continue without updating their values, or edit links you think are wrong.

MS Excel Container File Warning - Linked file not Reachable
MS Excel Container File Warning – Linked file not Reachable

You may get following warning message.

Error:
This workbook contains links to one or more external sources that could be unsafe.
If you trust the links, update them to get the latest data. Otherwise, you can keep working with the data you have.

MS Excel Container File Warning - Linked file could be unsafe warning
MS Excel Container File Warning – Linked file could be unsafe warning

How to view the Linked Files in Container Excel File:
To view the linked files from container Excel file, go to Data ribbon tab and under Connections group click Edit Links.

MS Excel Container File - Edit Links to Linked File
MS Excel Container File – Edit Links to Linked File

In Edit Links window, if you select a source, you will see its location. In situations, you will find it pointing to the local desktop profile of users. Nice work with the data report, but not nicely executed.

MS Excel Container File - Edit Links window
MS Excel Container File – Edit Links window

MS Excel Container File on SharePoint and Linked file not reachable:

Now if you try to open the file directly from SharePoint, you will get warning messages. See the URL address has been appended with the linked file address!

Error:
We can’t connect to http://sharepoint.domain.com/users/WindowsProfile/desktop/excelfile.xls

Open MS Excel File from SharePoint - Linked file missing - See URL
Open MS Excel File from SharePoint – Linked file missing – See URL

Click Ok and you will be greeted with another warning. See the image below.

Error:
Microsoft Excel cannot access the file http://sharepoint.domain.com/users/WindowsProfile/desktop/excelfile.xls. There are several possible reasons.

  • The file name or path does not exist
  • The file name is being used by another program
  • The workbook you are trying to save has the same name as a currently open workbook.
Open MS Excel File from SharePoint - Linked file missing - Possible Reasons listed
Open MS Excel File from SharePoint – Linked file missing – Possible Reasons listed

My Treat for Linked Excel Files and SharePoint:

I’ll cover the other details in of different testing scenarios may be little later as time permits. I created a container and linked files with some test data. Feel free to download and play around with it on your SharePoint test farm.

Download the Test Excel Container File and Linked File

Note it down that office Web apps doesn’t support linked files. You will get prompts when viewing the Excel container file using Office Web Apps in SharePoint.

MS Excel Container File Opened in Office Web Apps in SharePoint - Linked file not supported
MS Excel Container File Opened in Office Web Apps in SharePoint – Linked file not supported

Few Resources on the Excel Linked files:

Primer on Sharepoint Excel DataRefresh Settings

Sunday, June 1st, 2014

Primer on SharePoint Excel Data Refresh Settings

V 1.2

There are different places where you can configure the Excel data refresh settings for external data when using it with SharePoint.

a. Microsoft Excel file Data Connection Properties
b. Excel Web Viewer refresh option – viewing files in Excel Web Access
c. In Central Admin > Excel Services > Trusted File Locations
d. SharePoint Excel Web Part Properties

I created this Excel Data Refresh Options Chart that I’ve used on different projects. It shows external data refresh option in standalone MS Excel file, Excel file in a SharePoint library, in Excel Web Access and in Excel Web Access Web Part. It also shows different roles around Excel in an organization. Excel Workbook Author in some organization could be different than the SharePoint Site Admin that sets the web parts.

Softvative – MS Excel External Data Refresh Options Chart

a. MS Excel Data Connection Properties

1. Open your MS Excel file that has the data. Click on Data Ribbon tab and then click Connections.

MS Excel Data Connections Menu

2. Select one of the existing connections in your workbook and then click on Properties button.

MS Excel Data Connections Dialog Box

3. Under the Usage tab, under Refresh Control section there are refresh data settings:

  • Enable background refresh
  • Refresh every n minutes
  • Refresh data when opening the file
  • Remove data from the external data range before saving the workbook
MS Excel Data Connection – Refresh Control Settings

b. Excel Web Viewer refresh option – viewing files in Excel Web Access

You need to have SharePoint Enterprise edition for Excel Services. Hover mouse over an Excel file in a library, click on drop down arrow on right side to bring the menu and choose ‘View in Browser‘.

SharePoint Excel File in a Document Library – View in Browser

 When MS Excel file opens up in Excel Web Access, click on Data menu. SharePoint users will see these two options:

  • Refresh Selected Connection
  • Refresh All Connections
SharePoint – Excel Web Access – Data Refresh Options

c. In Central Admin > Excel Services > Trusted File Locations

Follow these steps to review / configure the Excel Services Refresh Settings. 

1. Go to SharePoint Central Admin Website 
2. Click on ‘Application Management‘ on left navigation
3. Click on ‘Manage Service Applications‘ link and then click on your Excel Service Application name
4. Click Trusted File Location

SharePoint – Excel Services Application

5. Hover mouse over one of your trusted file location and from drop down menu choose Edit.

SharePoint – Excel Web Services – Trusted File Locations

6. Scroll down and review / update values as appropriate under External Data section > External Data Cache Lifetime section for automatic and manual refresh. Default setting for both is 300 seconds (5 minutes)

  • Automatic refresh (periodic / on-open)
  • Manual Refresh

SharePoint – Excel Services – External Data Cache Lifetime

d. SharePoint Excel Web Part Properties

1. Create a new page or using existing page. Edit the page by clicking on Page ribbon tab > Edit Page. (or Site Settings > Edit Page)

SharePoint – Edit Page

2. Click on ‘Add a Web Part‘ link
3. Click on ‘Business Data‘ under Categories and then click on ‘Excel Web Access‘ under Web Parts section. Click Add button on the right side of the screen.

SharePoint – Add Web Part – Categories > Excel Web Access

4. Excel Web Access – Select a Workbook web part will come up on your page. Click on link that says ‘Click here to open the tool pane

SharePoint – Excel Web Access – Select a Workbook

5. Excel Web Access Web Part Properties section comes up on the right side of the page. Select an Excel file under Workbook field.

SharePoint – Excel Web Access Web Part Properties – top

SharePoint – Excel Web Access Web Part Properties – bottom

The default refresh connection settings on that web part are:

Section Option Default
Toolbar Menu Command Refresh Selected Connection, Refresh All Connections Checked
Interactivity Periodically Refresh if Enabled in Workbook Unchecked

You can leave the default setting for “Refresh Selected Connection, Refresh All Connections” to have that refresh option for your end users. Alternatively you can uncheck that option to remove refresh connection option for your users.

6. Optionally expand the Advanced property section of the web part and uncheck following options:
Allow Close
Allow Hide
Allow Editing in Personal View

SharePoint – Web Part Advanced Properties

7. Click Apply at the bottom right of the web part properties.
8. Review the Excel Web Access file and go to Data menu and see Refresh options.
9. Edit the web part properties again and uncheck the option “Refresh Selected Connection, Refresh All Connections“. Click Apply to save changes. Now review the Data menu and you will notice the refresh options are now removed.

SharePoint – Excel Web Access – Data menu

10. Excel Web Part property ‘Type of Toolbar‘ is by default set to full. That displays toolbar options based on settings. You can set that option to Summary, Navigation or none. These settings also hide or show refresh options in the Excel Web Access tool bar.

References:

External Data Cache Lifetime reference:
http://technet.microsoft.com/en-us/library/ff191194(v=office.14).aspx

Excel 2007 with Excel Services Reference
http://office.microsoft.com/en-us/sharepoint-server-help/refresh-external-data-in-excel-services-HA010105474.aspx

Excel Web Access Web Part custom properties (Sharepoint 2010)
http://office.microsoft.com/en-us/sharepoint-server-help/excel-web-access-web-part-custom-properties-HA010377893.aspx

Getting Started with Excel Services and Excel Web Access
http://office.microsoft.com/en-us/sharepoint-server-help/getting-started-with-excel-services-and-excel-web-access-HA010377881.aspx

Excel Services 2010 Data Refresh Flowchart: What is going on behind the scenes
http://blogs.technet.com/b/tothesharepoint/archive/2011/07/29/troubleshooting-excel-services-data-refresh.aspx

Excel Services data refresh flowchart
http://technet.microsoft.com/en-us/library/hh369968.aspx

Overview of connecting to (importing) data
http://office.microsoft.com/en-us/excel-help/overview-of-connecting-to-importing-data-HP010342748.aspx

Excel 2010 – Connection Properties
http://office.microsoft.com/client/helppreview14.aspx?AssetId=HA010342299&lcid=1033&NS=EXCEL&Version=14&tl=2&queryid=&respos=1&HelpID=22115

Excel 2010 – Refresh connected (imported) data
http://office.microsoft.com/client/helppreview14.aspx?AssetId=HP010342826&lcid=1033&NS=EXCEL&Version=14&tl=2&queryid=&respos=1&HelpID=199707

Configure Excel Services data refresh by using the unattended service account (SharePoint Server 2010)
http://technet.microsoft.com/en-us/library/hh525344(v=office.14).aspx

Configure Excel Services data refresh by using external data connections (SharePoint Server 2010)
http://technet.microsoft.com/en-us/library/hh525341(v=office.14).aspx

Keyword:

  • SharePoint  BI
  • Excel Web Access BI
  • Excel Web Services BI
  • SharePoint Business Intelligence
  • Softvative SharePoint BI Data Refresh Chart
  • Data Refresh Chart
  • Excel Data Refresh Options
  • Excel Data Refresh Settings
  • Excel Automatic Data Refresh Options
  • Excel Manual Data Refresh Options