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;
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).