Worksheet.Cells.FindString Cannot Find Date

Hi,

I'm searching a column for a date using a string value eg. "1/08/2011", however FindString() cannot find it. My code example is:

Dim dateCell As Cell
Dim dateArea As New CellArea()

dateArea.StartColumn = 0
dateArea.StartRow = 0
dateArea.EndColumn = 0
dateArea.EndRow = 365

dateCell = sheet.Cells.FindString("1/08/2011", Nothing, dateArea, False)

When looking at the spreadsheet the cell displays "1/08/2011". I've also tried searching for "01/08/2011" to no avail. Searching for 40756 (the excel serial date) as a number or string also does not work.

I can use the exact code and look for a string like "Hello" and it works fine.

Am I searching for dates using the correct method? If not how should I be doing it?

Many thanks,
Steve.

Hi,


Well, you are using FindString method, so it will search the string values only. If you have entered valid DateTime value into the cells and you want to search them, please use Find method instead. See the example code below. I have also attached a template file that contains DateTime in D5 and a string representation of Date in D11 cell. The FindString would search it only in D11, but the Find method would search your DateTime value too i.e. in D5 cell.

Sample code:
Dim workbook As New Workbook(“e:\test2\findstringbook.xlsx”)

Dim worksheet As Worksheet = workbook.Worksheets(0)

Dim findOptions As New FindOptions()
Dim ca As New CellArea()
ca.StartRow = 0
ca.StartColumn = 0
ca.EndRow = 100
ca.EndColumn = 24
findOptions.SetRange(ca)

'Aspose.Cells.Cell cellfound = worksheet.Cells.FindString(“1/8/2011”, null, findOptions); 'Only D11 cell would be found.
Dim cellfound As Aspose.Cells.Cell = worksheet.Cells.Find(“1/8/2011”, Nothing, findOptions) 'D5 cell is found.
MessageBox.Show(cellfound.StringValue)
MessageBox.Show(cellfound.Name)

Hope, you understand now.

Thank you.

Excellent, worked perfectly. Nice & quick as well!