none
Revisting VSTO Excel Custom Search Function RRS feed

  • Question

  • Hi, its been quite a while since i have been to VSTO. In the past i have done a good search function for excel 2007 and i managed to get the file up and running in my VS2010 with some difficulty over the version issues, but now i want to make this in to an add-in so that it reflects on all the excel workbooks when they are opened by the user in the add-in's ribbon tab

    pls view the file and feel free to edit it around here is the file in skydrive.

    So basically i have the necessary things now all i want is to have a .msi installer to install the search function that i created as an add in so that when that add-in is pressed the usercontrol will show up thnks


    D.Boy
    Saturday, July 30, 2011 4:58 AM

Answers

  • Hi D.Boy,

     

    <<but the previous cell is not being cleared up. i hope you understand where i am driving at which means it is going to leave all these nasty little hyperlinks all over my sheet, which is not logical, i scanned thru the code to prevent this but not sure where to edit this, so this i might require your invaluable help again>>

    Please let the “theColno”, “RowStart” and “RowEnd” became Ribbon1 Class’s variable. Then when you start a new search the old result on the worksheet will remove.

     

    <<but i noticed that i have to press enter after i type the string in the add-in text box>>

    I think I no idea about it. This action is a build-in action which we must do to fire the Onchange event. By the way, if you type the duplicate content in to the editbox the Onchange event will not fire.

     

    <<i want to share this add-in with the rest of my peers by making this in to installation>>

    I don’t think the reference you mention is good enough to distribute your add-in. Would you please right click mouse on the solution explorer and select “publish” to create a Click once deployment to distribute your add-in? It will be easy for you to share your add-in with your peers. And I would recommend you take a look at http://msdn.microsoft.com/en-us/library/8st7th1x(v=VS.100).aspx It will solve the specific require of the prerequisites of you add-in.

     

    If I have misunderstood anything, please feel free to let me know.

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, August 3, 2011 6:11 AM
    Moderator

All replies

  • Hi D.Boy,

     

    Thanks for posting in the MSDN Forum.

     

    According to your description I created a snippet to support your goal, please take a look to see whether it is you want.

     

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

    <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="Ribbon_Load">

      <ribbon>

        <tabs>

          <tab idMso="TabAddIns">

            <group id="MyGroup"

                   label="My Group">

              <editBox id="SearchCharacters" onChange="SearchAction"/>

            </group>

          </tab>

        </tabs>

      </ribbon>

    </customUI>

     

    public void SearchAction(Office.IRibbonControl Control,string text)

            {

                string sFindMe = text;

                int iCounter = 0;

                int colNo = 0;

                int RowNo = 0;

                Microsoft.Office.Interop.Excel.Range Mycell;

                string sFirstFoundAddress;

                List<string> arMatchesAdd = new List<string>();

                List<object> arMatchesValue = new List<object>();

                Microsoft.Office.Interop.Excel.Range rgFound;

     

                Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet;

                object missing = System.Reflection.Missing.Value;

     

                //Clear the array

                arMatchesAdd.Clear();

                arMatchesValue.Clear();

                //this loop clears the last result from the cells

                while (RowEnd != 0)

                {

                    ws.Cells[RowStart++, theColno] = "";

                    RowEnd--;

                }

     

                if (sFindMe.Length == 0)

                {

                    return;

                }

     

                rgFound = ws.Cells.Find(sFindMe, ws.Cells[1, 1], Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues, Microsoft.Office.Interop.Excel.XlLookAt.xlPart, missing, Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext, false, missing, missing);

     

                // If Find doesn't find anything, rgFound will be null

                if (rgFound != null)

                {

                    // Save the address of the first found item -

                    // it will be used in a loop terminating condition.

                    sFirstFoundAddress = rgFound.get_Address(true, true, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, missing, missing);

                    arMatchesAdd.Add(sFirstFoundAddress);

                    object sValue = rgFound.get_Item(missing, missing);

                    arMatchesValue.Add(sValue);

     

     

                    // Continue finding subsequent items using FindNext

                    rgFound = ws.Cells.FindNext(rgFound);

                    string sAddress = rgFound.get_Address(true, true, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, missing, missing);

                    sValue = rgFound.get_Item(missing, missing);

     

                    // Start a loop that calls FindNext until

                    // the first found cell is found again

                    while (!sAddress.Equals(sFirstFoundAddress))

                    {

                        arMatchesValue.Add(sValue);

                        arMatchesAdd.Add(sAddress);

                        rgFound = ws.Cells.FindNext(rgFound);

                        sAddress = rgFound.get_Address(true, true, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, missing, missing);

                        sValue = rgFound.get_Item(missing, missing);

                    }

                }

     

                //finds the active cell on the worksheet

                Mycell = Globals.ThisAddIn.Application.ActiveCell;

     

                colNo = Mycell.Column;

                RowNo = Mycell.Row;

                theColno = Mycell.Column;

                RowStart = Mycell.Row;

     

                //declaring parameters for Hyperlink function

                object oAnchor;

                string oAddress = "";

                object oSubadd;

                object oMissing = System.Reflection.Missing.Value;

     

                //write the values in array in the cells

                while (iCounter != arMatchesAdd.Count)

                {

                    ws.Cells[RowNo, colNo] = arMatchesValue[iCounter];

                    //assign the anchor cell

                    oAnchor = ws.Cells[RowNo, colNo];

     

                    //linking one cell to another cell

                    oSubadd = arMatchesAdd[iCounter];

     

                    //Hyperlink function defined here

                    ws.Cells.Hyperlinks.Add(oAnchor, oAddress, oSubadd, oMissing, oMissing);

     

                    //increase Row couner

                    RowNo += 1;

                    iCounter += 1;

                }

                RowEnd = RowNo;

            }

     

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, August 1, 2011 3:18 AM
    Moderator
  • Hi Tom tks for the code, but as you know it is not always easy to just copy and paste code in to my project file could you be kind enough to send me the .zip file of the soultion to ybpriya@gmail.com. I was really looking forward to getting this done over the weekend.  i figured the XML code but having trouble on where to put the C# code so a soultion file will be easy for me to compare mine and yours tks, even though i tried doing the pasting i am having a ton of errors regarding to 'thisaddin'...looking forward to it! :)
    D.Boy
    • Edited by jackandjill2012 Monday, August 1, 2011 5:54 AM Grammatical Corrections
    Monday, August 1, 2011 5:51 AM
  • Hi D.Boy,

     

    I have sent a mail to you. You can download this zip file from skydrive also.

     

    I will show you the steps to create an Excel Add-in to handle your issue.

     

    1.         Create an Excel Add-in solution.

    2.         Create a Ribbon via Ribbon Xml.

    3.         Copy the CreateRibbonExtensibilityObject method to ThisAddIn.cs

    4.         Insert the editBox element in the Ribbon.xml

    5.         Add the method name (on my side is “SearchAction”, you can name it as your wish) to editBox’s OnChange event.

    6.         Add the method in Ribbon1.cs.

     

    I hope it can help you.

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, August 2, 2011 3:09 AM
    Moderator
  • Hi Tom i did download the file and yes your methoid is more convenient, but i noticed that i have to press enter after i type the string in the add-in text box, which some people might consider not 'LATEST' but i will figure that out, my 1st question is that if i selected a blank cell and type the text in the add-in and press enter i get the results, so after the first search i go to another blank cell in the same sheet and again type same or different text and press enter i get the results as well, but the previous cell is not being cleared up. i hope you understand where i am driving at which means it is going to leave all these nasty little hyperlinks all over my sheet, which is not logical, i scanned thru the code to prevent this but not sure where to edit this, so this i might require your invaluable help again.

    By the way this Add-In is only for my computer, but i want to share this add-in with the rest of my peers by making this in to installation. The installation part i followed the document over here and got it but as you can see that it is referencing to .Net framework 3.0 and all the component ID even the VSTO ones are a bit outdated so on my development machine i have removed the necessary check for the .net framework as i already know that i have it and continues with the  installation. It installed just fine, it is working as the article said it would by displaying hello world msg box, but the question in point is as i explained want to deploy to other PC's running XP and Vista as well, here is the link to the article maybe you could ask the author to update or point me to the updated article tks

    Tks Tom


    D.Boy
    Tuesday, August 2, 2011 4:57 AM
  • Hi D.Boy,

     

    <<but the previous cell is not being cleared up. i hope you understand where i am driving at which means it is going to leave all these nasty little hyperlinks all over my sheet, which is not logical, i scanned thru the code to prevent this but not sure where to edit this, so this i might require your invaluable help again>>

    Please let the “theColno”, “RowStart” and “RowEnd” became Ribbon1 Class’s variable. Then when you start a new search the old result on the worksheet will remove.

     

    <<but i noticed that i have to press enter after i type the string in the add-in text box>>

    I think I no idea about it. This action is a build-in action which we must do to fire the Onchange event. By the way, if you type the duplicate content in to the editbox the Onchange event will not fire.

     

    <<i want to share this add-in with the rest of my peers by making this in to installation>>

    I don’t think the reference you mention is good enough to distribute your add-in. Would you please right click mouse on the solution explorer and select “publish” to create a Click once deployment to distribute your add-in? It will be easy for you to share your add-in with your peers. And I would recommend you take a look at http://msdn.microsoft.com/en-us/library/8st7th1x(v=VS.100).aspx It will solve the specific require of the prerequisites of you add-in.

     

    If I have misunderstood anything, please feel free to let me know.

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, August 3, 2011 6:11 AM
    Moderator