Search code examples
javascriptexcelsharepointfindweb-parts

Javascript/Excel ActiveX object. Find exact match


So I am using a SharePoint web part (HTML Form Web Part) that I've built to query an excel file that is hosted on our Sharepoint site. I wrote javascript to create an Excel ActiveX object, and I can search the worksheet just fine.

However, I need to have it search for an exact match (the whole cell) not just part. I know the code that I need, I just can't make it work. I need to find out how to create the correct object type for the "xlWhole" argument of the "Find()" function.

The line of code I am having trouble with is commented out, because that didn't work. Any insight? I've seen the Range.Find method, but I just can't make it work.

var excel = new ActiveXObject("Excel.Application");

var wb = excel.Workbooks.Open("workbook path");
var ws = wb.Worksheets("worksheet name");
var ws = wb.ActiveSheet;
//var cell = ws.Cells.Find(str,excel.XlLookAt.xlWhole);
var cell = ws.Cells.Find(str);
foundRow = cell.Row;

Solution

  • This worked for me:

    function searchExcel()
    {
        var excel =new ActiveXObject("Excel.Application");
        excel.visible=true;
    
        var wb = excel.workbooks.open("D:\\Analysis\\tmp\\Book1.xlsx");
        var ws = wb.sheets(1);
    
        var str="Value1";
    
        // .Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, 
        //                                 MatchCase, MatchByte, SearchFormat)
        var cell = ws.Cells.Find(str,ws.Cells(1),-4163,1)
    
        alert(cell?cell.Row:(str+" not found"));
    
        excel.quit();
    }
    

    I guess you cannot skip over parameters when using COM from js (but you can leave them off the end).