Excel found unreadable content in XLSM file. Do you want to recover the contents of this workbook?

Excel found unreadable content in XLSM file

Our XLSM template file was generating report successfully when we tried with Mozilla Firefox and Google Chrome. When same Excel template was tested with Microsoft Internet Explorer, it was generating the report but below error was occurring while saving and opening the report. 

“Excel found unreadable content in 'xlsm' file. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.”

On clicking Yes button, it popped up another message ‘Repairs to XLSM file’ with below text

Excel was able to open the file by repairing or removing the unreadable content.

Removed Feature: External formula reference from /xl/externalLinks/externalLink1.xml part (Cached values from external formula reference)

Click to view log file listing repairs C:\Users\<username>\AppData\Local\Temp\error051680_01.xml

The content of error051680_01.xml was as below

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>

- <recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">

<logFileName>error051680_01.xml</logFileName>

<summary>Errors were detected in file 'C:\Users\username\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.Outlook\D37RMGPB\FileName.xlsm'</summary>

- <removedFeatures summary="Following is a list of removed features:">

<removedFeature>Removed Feature: External formula reference from /xl/externalLinks/externalLink1.xml part (Cached values from external formula reference)</removedFeature>

</removedFeatures>

</recoveryLog>

Actually we were trying to generate Excel report based on XLSM template file. Internally the template data was populated by Aspose.Cells from Java side. We found that Internet Explorer behaves differently when compared with Firefox and Chrome in relation to runtime Excel 2007 report generation. 

The exact root cause for "Excel found unreadable content" was still unknown. It seems, when template was directly opened from website, template was opening by embedded within web browser and maintained references of dynamic Excel file name (created by Java) in temporary memory. On saving Excel report file to user machine, it used to save the file on user machine with some external reference (cached from temporary memory location of Internet Explorer).

Next we followed below process for "Excel found unreadable content" wherein the external references can be checked using below steps:

1. Change the saved file extension to .zip. For e.g. “FileName.zip”
2. Extract the zip file to a folder e.g. “FileName”
3. In this case, go to “\FileName\xl\externalLinks” folder. It shows XML files related to external references.
4. In this case, “\FileName\xl\externalLinks\_rels\externalLink1.xml.rels file show external reference to the Excel file at temp location
5. File “\FileName\xl\externalLinks\externalLink1.xml shows references to some external links

This all was very confusing and underlying root cause for "Excel found unreadable content" was still unknown.  Based on Microsoft forum input, we tried enabling VBA component in Microsoft Office and again checked the “unreadable content error” issue. But the issue didn’t fixed. Only good thing at that instance, we felt there was compilation error issue which sometimes unnecessarily occur.

Microsoft Support team instructions on Excel found unreadable content issue.

 

Finally, we had to contact Microsoft engineer to look into this. Initially he advised to run Microsoft Support Diagnostic Tool packages which is available for below operating systems

Windows XP (x86 and x64)

Windows Server 2003 (x86 and x64)

Windows Vista (x86 and x64)

Windows Server 2008 (x86 and x64)

Windows 7 (x86 and x64)

Windows Server 2008 R2 (x64)

Windows 8 (x86 and x64)

Windows Server 2012

For frequently asked questions about diagnostic data collection and uploading tools, Microsoft advised to click the below link.

http://support.microsoft.com/kb/2598970

However all these steps didn’t resolved the issue.

Finally Microsoft engineer advised to send sample copy of our Excel template to investigate further on this. Later on he mentioned that the problem seems to be with the sheet name with the Excel template. Actually one of the sheet name has more than 31 characters. We reduced the sheet name to less than 31 characters which luckily addressed the issue in our situation!

Hope this article detail might have given you some insights what scenarios we went through and reactive steps we followed. Thanks for reading our content on the issue of "Excel found unreadable content in XLSM file".

Excel VBA: Digital Signature applied in XLSM template file, but was still appearing

Digital Signature in XLSM template file

In our case, we ensured that Digital Signature was applied with latest certificate to XLSM templates of Excel 2007. However for some reason, it had no effect on macro security warning. When templates were deployed and checked with web application which invoke and generate the report based on that XLSM template, we found that security warning appearing at the top. We rechecked the Digital Signature on those reports and surprisingly it had latest certificate. We couldn’t understand why this problem is occurring though we put latest Digital Signature correctly on template file.

Someone suggested that they had similar issue and their issue was related to sorting of cells. We tried to remove such code where sorting of cells was used.

ActiveWorkbook.Worksheets("XYZ").Sort.SortFields.add Key:=Range("AJ2"), _

        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

If Sheet2.Range("Test").count > 0 Then

    With ActiveWorkbook.Worksheets("XYZ").Sort

        .SetRange Range("AJ2:AJ3")

        .Header = xlNo

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With

End If

However removing such code as mentioned above didn’t helped to fix the issue.

Finally we tried to explore referenced and unreferenced named ranges. We tried to remove unreferenced named range one by one and then reapply the digital signature on the template. Luckily we found that one of the defined named range which was not used anywhere was having some relation with this issue. Since that named range was not needed, we deleted it from named range list. As soon as it was removed and digital signature was reapplied, macro security warning was gone. Everything worked fine!!

ServiceNow: Stop or restrict the record from saving when Reference icon is clicked

In ServiceNow, when user clicks on Reference icon, for e.g. on Asset related interface, just to see the referred record, for e.g. Configuration Item, it redirects the user to the interface of referred record. Having said this, if user again navigates back to the Asset interface, auto save for Asset record is triggered even though no field modification is done on either interface. This type of behavior is often seen for Reference icon click in ServiceNow. This creates unintended confusion of record being modified by user who just tried to view records (for e.g. Asset to Configuration Item record navigation).

In this scenario, there is no way to stop the update action once it is initiated on Reference icon click. We can’t call autoSysFields(false) for current record in any business rule and expect to stop the update of audit fields like Updated and Updated By. Because autoSysFields(false) is called before update action is initiated.

If the root cause of update action is known, then as a workaround, if possible, that logic can be tried to shift from client script to business rule in ServiceNow. For e.g. if u_view field is set in any client script for OnLoad event, every time it set the value of u_view field which in turn mark the current record to modified status. This initiate the update action when Reference icon is clicked for any referred record. It tries to first update the current Asset record and then navigates to interface of referred record. In this case, it looks like user has not modified any field and auto save has been triggered on Reference icon. To avoid the update action in this case, client script logic can be moved to a business rule.

For example below script can identify the view name in business rule (before update), if view name is needed internally for some business logic.

var transaction = GlideTransaction.get();

var viewName = transaction.getRequestParameter("sysparm_view");

Once view name is identified, it can be used for desired purpose and client script just to set value of  u_view field can be deactivated and tested. If no auto save trigger on Reference icon click, then it means the issue has been addressed. If not, then it means some other field also changes internally which in turn trigger update action. Sometime it is not known which field value changes internally. In that situation below script can be used in business rule (before update).

var actionName = action.getActionName();

gs.addInfoMessage('Action performed: ' + actionName);

if (actionName == 'sysverb_check_save'){

    if (typeof GlideScriptRecordUtil != 'undefined')

       var gru = GlideScriptRecordUtil.get(current);

    else

       var gru = Packages.com.glide.script.GlideRecordUtil.get(current);

    var changedFields = gru.getChangedFields();

    gs.addInfoMessage('Fields changed: ' + changedFields.toString());

}

Above code patch will provide the list of fields modified for which update action initiate on Reference icon click. For e.g. Some financial field like ‘Cost’ on Asset side, which stores value in currency is initialized at runtime. By default its value is blank. When form is loaded, its value is changed to ‘$0.00’ which marks the current record has been modified. In such scenario, those can be updated with default value as ‘$0.00’ for blank in existing records and at table level such field can be set to default value as ‘$0.00’ so that it is not blank at the time of record creation. Once this activity is addressed, try to test and see if Reference icon click trigger update action.

If it is not possible to move any field logic from client script to business rule, then it is not possible to avoid the record save on Reference icon click. As an alternate option, users can be notified that some fields on the interface has been modified by validating g_form.modified in onSubmit event for actionName == 'sysverb_check_save' and provide an appropriate message by returning false. That will cancel the update action on Reference icon click. But it will also stop the user from navigating to the interface of referred record.

Otherwise glide.ui.reference.readonly.clickthrough property value can be changed in UI properties, if business users have no issue to have the impact on all interfaces wherein Reference icon no longer appear.

Another workaround is to change glide.ui.clickthrough.popup property to open a new tab every time user click on Reference icon (instead of opening the referred record interface in same window). However this change will apply to all interfaces and Reference icon will also appear in different way.

ServiceNow: Steps of Scheduled Email for an existing report not capturing in local updateset

In ServiceNow, when we create a Scheduled Email for an existing report, and look forward to capture related steps as part of an updateset, please be aware that Scheduled Email steps are not captured. This seems to be ServiceNow drawback or limitation. All steps related to the Scheduled Email of Report need to be separately captured in an independent file. Otherwise those steps need to be manually applied in destination ServiceNow environment. 

Scheduled Email record can be separately saved in XML format file and then same XML file can be imported back into the destination ServiceNow environment to implement the Scheduled Email for an existing report. 

So initially below steps should be followed to export and save Scheduled Email record as XML file

• Open Scheduled Email of Report
• Right click on list header Scheduled Email of Report and select Export à XML (This Record)
• Save the XML Document in local folder of your machine

Once XML file is created, below steps should be followed to import the XML file which will apply all steps to implement Scheduled Email for ServiceNow report

To import the XML file as Scheduled Email record in destination environment, elevated privileges access is needed. Hence select the elevated privileges (lock) icon beside user name. In the ‘Activate an Elevated Privilege’ dialog box, select the ‘security_admin’ check box and click OK. Now proceed further to import XML file using below steps

• Go to Reports à Scheduled Reports
• In the destination environment, right click on list header on Scheduled Email of Reports page and select “Import XML”.
• Browse and select the XML Document in local folder of your machine and click Upload.

Similar kind of situation arises in case of Scheduled Jobs in ServiceNow.

ServiceNow: Steps to create Scheduled Email of Report

Basically creating a Scheduled Email of Report in ServiceNow means scheduling a notification email for your desired business users who will receive the report (as an attachment) . Please note that the report need to be already created and should exist in ServiceNow reports.

Scheduled Email of Report in ServiceNow can be created using below steps:

1. Click on Scheduled Reports link besides Administration link on Reports à View / Run screen or click ‘Scheduled Reports’ link under Reports section
2. Click on New button to create the schedule for the report
3. Fill details on Scheduled Email of Report screen
4. Name: Fill name of Scheduled Email of Report
5. Report: Specify the report name to be scheduled
6. Email addresses: Specify the email addresses which acts recipients for the email
7. Set the schedule time for the report to run daily
8. Enter the Subject for the email notification
9. Enter body text for the scheduled email
10. Set Type for e.g. as ‘Excel’
11. Set Run as for e.g. ‘System Administrator’
12. Click on Save to save the report.

ServiceNow: Steps to create a Global Report of List type

ServiceNow Global Report

To create a Global Report of List type in ServiceNow, below steps need to be followed:

• Enter ‘Report’ in Type Filter Text box on left hand side on top left
• Click on Reports à View / Run. Clicking View/ Run will open Reports screen
• Click on New button to create a global report
• Populate values for following fields

Name: Name for the global report

Type: Select ‘List’

Table: Select Table Name. For e.g. Hardware (alm_hardware)

Group By: Leave it blank if group by is not needed

Export details: Keep it checked

Header Footer Template: Default

Visible To: Select ‘Me’ option if like to visible to yourself, else select ‘Groups and Users’ option if you like the report to be visible for an specific Groups or custom list of users. Else select ‘Everyone’ option, if you like to make the report visible to all

Filter and Order: This can be used to set filter for records in selected Table

Columns: Select desired column list from Available to Selected box. To selected field to more granular level for fields appearing in green with plus sign, select the field and click + sign icon. This will expand the field list. Normally this behaviour is seen for fields which act as Reference field. Now you can select the desired field name.

Once all values are selected, click Run Report which in turn will run the report and show the output records below in grid. You can click Save button to save the report.