Tablets, PC's and Sales

February 13, 2013

I was reading an interesting article on Time’s magazine. I concur with the conclusion of the article.

It reads - “Where we go from here is the multi-screen world. In the age of the PC, it was all about one primary computing screen. Now it’s about many. Consumers will have two or three or four screens in their lives and they will want to use all of them as a part of their computing solution. The future is not about one screen; it is about many ….” 

Read more: 

In fact, we are getting into a multi – screen world. People are using multiple devices for various functions. Of course, tablets are eating into PC’s lunch. However, in the present market tablet is not considered a complete solution to PC.  You may prefer to do certain things on a PC, not on a tablet and vice-versa. I think it is important to understand what functions our sales people want to do on a tablet.

I have searched internet to understand how sales are using tablets. I found the following articles informative.

  • Seven tips for outfitting your sales team with tablets

  • Are Tablets Now the Preferred Tool for Salespeople?

Interestingly, I couldn't find any articles specific to sales in software industry. 

COM Add-in

VSTO : Creating and retrieving Custom XML part in Excel

February 12, 2013

Custom XML allows one to store XML in the desired format inside an Office document. For example, if we open Excel Workbook using 7-zip the custom XML folder is right at the root level. 
Default Folder Structure

string xmlString1 =
          "<?xml version=\"1.0\" encoding=\"utf-8\" ?>" +
          "<employees xmlns=\"\">" +
              "<employee>" +
                  "<name>Surender G</name>" +
                  "<hireDate>1999-04-01</hireDate>" +
                  "<title>Manager</title>" +
              "</employee>" +
Office.CustomXMLPart employeeXMLPart = Globals.ThisAddIn.Application.ActiveWorkbook.CustomXMLParts.Add(xmlString1, missing);

After executing the above code two files (item4.xml, itemProps4.xml) get added to the folder.

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<ds:datastoreItem ds:itemID="{8905F770-7F58-4172-B707-34A283DF8527}" xmlns:ds="><ds:schemaRefs>
<ds:schemaRef ds:uri=""/></ds:schemaRefs>

<?xml version="1.0" encoding="utf-8"?>
<employees xmlns="">
<name>Pradeep G</name>

Folder structure after adding Custom XML

Now, we use the highlighted itemID in itemProps4.xml to retrieve the stored XML (Don't miss parenthesis).  
string str = "{8905F770-7F58-4172-B707-34A283DF8527}";
                    Office.CustomXMLPart obj = Globals.ThisAddIn.Application.ActiveWorkbook.CustomXMLParts.SelectByID(str);
MessageBox.Show("XML ::::"+obj.XML);


We have successfully retrieved the XML.

Using Custom XML Part as a data store


Worksheet Activate event is not firing

February 12, 2013

On a fine day, I found that custom task pane is not appearing on activating a worksheet. I could find no error messages in our log4net log and windows logs. I tried to troubleshoot for a while but nothing worked. 

Finally, I went back my stable project and started comparing the code. It's found that the following line was commented out. 


As soon as I have removed the comments and task pane showed up again. 
I found that GetVstoObject() generates an extended object which gives access to application level events. Also, check out HasVstoObject().  

A short description about extended object

  • Add managed controls to any open document or worksheet
  • Convert an existing list object on an Excel worksheet to an extended ListObject that exposes events and can be bound to data by using the Windows Forms data binding model
  • Access application-level events that are exposed by Word and Excel for specific documents, workbooks, and worksheets

  1. Getting Extended Objects from Native Office Objects in Document-Level Customizations
  2. Extending Word Documents and Excel Workbooks in Application-Level Add-ins at Run Time
  3. Using Activate event on an Excel Worksheet

Deployment Issues migration tool - Deploying Weblink and migrating files with special characters

February 08, 2013

Recently, I noticed the followed issues in SFDC Ant deployment.

Issue 1: Typo in XML <name> metadata data for Web Link

Just to reiterate, all the buttons and links created under "Buttons and Links" section of the Object are treated as Web Links.

Screenshot from Migration Tool Guide

It should be WebLink, not Weblink. Otherwise, the following error occurs while extracting the package : package.xml - Entity type: 'Weblink' is unknown.

Correct XML is: 


Issue 2: Unable to deploy entities with special characters


<members>User w/Export </members>

I tried to deploy Profile entities with special characters like '-' and '/'. Tool is able to extract the Profile's fine. But, while deploying it has thrown the following error.

Error: package.xml(<profilename>):An object '<profilename>' of type Profile was named in package.xml, but was not found in zipped directory

The issue has occurred as the migration tool is URL-Encoding the names before saving them to the disk. For example profile User w/Export is retrieved as User%20w%2FExport. While deploying the package the tool is not able to find the match for User w/Export.

As a work around, I have changed the profile names in package.xml (In package folder) to match with the encoded name.

XML would look like:



Export Data To CSV

February 06, 2013

public class exportCSVController {

    public List> myList {get;set;}
    public exportCSVController() {
        myList = new List>();
        List temp = new List();
        for(Integer i = 0; i < 2500; i++){
            if(temp.size() < 1000){
                myClass m = new myClass();
                m.val1 = 'val1 ' + i;
                m.val2 = 'val2 ' + i;
                temp = new List();
                myClass m = new myClass();
                m.val1 = 'val1 ' + i;
                m.val2 = 'val2 ' + i;

    public class myClass{
        public string val1 {get;set;}
        public string val2 {get;set;}

<apex:page controller="exportCSVController" cache="true" contentType="text/csv#Export.csv" language="en-US">
    "Col A","Col B"
    <apex:repeat value="{!myList}" var="a">
        <apex:repeat value="{!a}" var="asub">



Steps to format an Excel table suitable to paste in Word

February 05, 2013

  • Create a table style

                    //Creating table Sytle. Unprotect all the sheets. If anyone sheet is protected it will return a COM Exception hresult 0x800a03ec
                    foreach (Excel.Worksheet ws in activeWorksheets)
                        if (ws.ProtectionMode || ws.ProtectContents)
                            ws.Visible = Excel.XlSheetVisibility.xlSheetVeryHidden;
                    catch (Exception excep)
                    //DISP_E_BADINDEX exception occurs if there is no TableStyle named “MyProposalStyleName”                       
if (!excep.Message.Contains("DISP_E_BADINDEX"))
                            throw excep;
                    //Protect sheets

                    foreach (Excel.Worksheet ws in protectedSheets)
                        ws.Visible = Excel.XlSheetVisibility.xlSheetVisible;


       //We had no luck using the Color property. Thus, using color index (

       void createTableStyle(string styleName)
            Excel.TableStyle ptStyle = Globals.ThisAddIn.Application.ActiveWorkbook.TableStyles[styleName];
            ptStyle.ShowAsAvailableTableStyle = true;

            // Table style Header Row               
            Excel.TableStyleElement HeaderRow = ptStyle.TableStyleElements[Excel.XlTableStyleElementType.xlHeaderRow];
            HeaderRow.Interior.ThemeColor = Excel.XlThemeColor.xlThemeColorDark1;
            HeaderRow.Interior.ColorIndex = 23;
            HeaderRow.Interior.TintAndShade = -0.249946592608417;
            HeaderRow.Font.ColorIndex = 2;
            HeaderRow.Font.Bold = true;
            HeaderRow.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = 23;
            HeaderRow.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
            HeaderRow.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
            HeaderRow.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = 23;
            HeaderRow.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
            HeaderRow.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
            HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeLeft].ColorIndex = 23;
            HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin;
            HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
            HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeRight].ColorIndex = 23;           
            HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin;
            HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
            HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeTop].ColorIndex = 23;
            HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;
            HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
            HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeBottom].ColorIndex = 23;
            HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;
            HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;

            // Table style Row Stripe 1
            Excel.TableStyleElement totalTable = ptStyle.TableStyleElements[Excel.XlTableStyleElementType.xlWholeTable];
            totalTable.Font.ColorIndex = 1;

            totalTable.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = 23;
            totalTable.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
            totalTable.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
            totalTable.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = 23;
            totalTable.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
            totalTable.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
            totalTable.Borders[Excel.XlBordersIndex.xlEdgeLeft].ColorIndex = 23;
            totalTable.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin;
            totalTable.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
            totalTable.Borders[Excel.XlBordersIndex.xlEdgeRight].ColorIndex = 23;
            totalTable.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin;
            totalTable.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
            totalTable.Borders[Excel.XlBordersIndex.xlEdgeTop].ColorIndex = 23;
            totalTable.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;
            totalTable.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
            totalTable.Borders[Excel.XlBordersIndex.xlEdgeBottom].ColorIndex = 23;
            totalTable.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;
            totalTable.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;


  •       Applying table style
        public void FormatAsTable(Excel.Range SourceRange, string TableName, string TableStyleName)
            SourceRange, System.Type.Missing, Excel.XlYesNoGuess.xlYes, System.Type.Missing).Name =

            SourceRange.Worksheet.ListObjects[TableName].TableStyle = TableStyleName;
            Excel.Range e = SourceRange.Cells[Type.Missing, 1];
            // applying conversion. 1 inch = 72 pt. Ex: 2.49 inches in Word = 2.49 * 72 pts in Excel
            e.ColumnWidth = 2.49 * 72;

            e = SourceRange.Cells[Type.Missing, 2];
            e.ColumnWidth = 1.19 * 72;

            e = SourceRange.Cells[Type.Missing, 3];
            e.ColumnWidth = 0.74 * 72;

            e = SourceRange.Cells[Type.Missing, 4];
            e.ColumnWidth = 1.02 * 72;

            e = SourceRange.Cells[Type.Missing, 5];
            e.ColumnWidth = 1.03 * 72;

Now, create a table out of the selected range and format

FormatAsTable(rangeToCopy, "MyTableName", "MyProposalStyleName");

  •        Copy the table to clipboard


Popular Posts