Clarity

  • 1.  MS Excel hyperlinks - Sharing experience

    Posted Mar 15, 2016 03:27 PM

    Recently I had a user complain that an Export to MS Excel from CA PPM was defective because partially through the export, hyperlinks became the actual HTML link. This caused a lot of confusion for the end user and they blamed CA PPM for screwing up their MS Excel report.

     

    The reality is that MS Excel has a limit of the number of hyperlinks it can display before it displays the HTML text for the link.

     

    This has been confirmed by Microsoft Support as a known XML Spreadsheet limitation with some versions of excel there is a maximum defined of 65530 hyperlinks.

     

    Additional Information:
    As per the change Impact and Upgrade guide, Excel has a 64-KB limit on the number of links that are exported to a worksheet. If you have configured links within your time-scaled value (TSV) cells, you can reach the limit. An Export to Excel operation does not complete successfully when the limit is reached.
    A new option named Enable TSV links for Export to Excel provides a way to control how TSV links are exported. The option is selected by default so that all TSV links are exported.
    If the option is not selected, TSV links are exported only up to the limit.
    When the limit is reached, the operation continues to export TSV values but without additional links.
    Regardless of the scenario, Excel has a 64-KB limit on the number of links that are exported to a worksheet.

     

    the following workarounds are available:
    1. Filter the data to limit the number of links exported.
    2. Export the data in segments limiting the number of links exported

     

    The above talks about TSV's. However, any export with a ton of links will cause this to happen. My solution to the user that was having this problem, was to go into the fields for a few of the attributes and remove the unnecessary links. This made them happy and also taught them that the issue is a MS Excel issue and not a CA PPM issue.

     

    Regards,

    Mike



  • 2.  Re: MS Excel hyperlinks - Sharing experience

     
    Posted Mar 21, 2016 11:24 AM

    Thanks for sharing this tip Michael!

    Michael Thibault wrote:

     

    Recently I had a user complain that an Export to MS Excel from CA PPM was defective because partially through the export, hyperlinks became the actual HTML link. This caused a lot of confusion for the end user and they blamed CA PPM for screwing up their MS Excel report.

     

    The reality is that MS Excel has a limit of the number of hyperlinks it can display before it displays the HTML text for the link.

     

    This has been confirmed by Microsoft Support as a known XML Spreadsheet limitation with some versions of excel there is a maximum defined of 65530 hyperlinks.

     

    Additional Information:
    As per the change Impact and Upgrade guide, Excel has a 64-KB limit on the number of links that are exported to a worksheet. If you have configured links within your time-scaled value (TSV) cells, you can reach the limit. An Export to Excel operation does not complete successfully when the limit is reached.
    A new option named Enable TSV links for Export to Excel provides a way to control how TSV links are exported. The option is selected by default so that all TSV links are exported.
    If the option is not selected, TSV links are exported only up to the limit.
    When the limit is reached, the operation continues to export TSV values but without additional links.
    Regardless of the scenario, Excel has a 64-KB limit on the number of links that are exported to a worksheet.

     

    the following workarounds are available:
    1. Filter the data to limit the number of links exported.
    2. Export the data in segments limiting the number of links exported

     

    The above talks about TSV's. However, any export with a ton of links will cause this to happen. My solution to the user that was having this problem, was to go into the fields for a few of the attributes and remove the unnecessary links. This made them happy and also taught them that the issue is a MS Excel issue and not a CA PPM issue.

     

    Regards,

    Mike