Error with the Excel.Find function in VBScript

Question

I am trying to create a VB script that searches through a column of dates and returns the address of the cell with todays date. For some reason I keep getting an "Object required: 'FoundCell'" error. Could someone have a look at my code and correct me? I can read out the date using WScript.Echo, but once I use it in the find function it immediately gives me the error.

Set oExcel = CreateObject("Excel.Application")
Set wshShell = CreateObject("Wscript.Shell")

File_Path = "D:\Work\Personal Timemanagement\test.xlsx"


Set oData = oExcel.Workbooks.Open(File_Path)

WHAT_TO_FIND = Date()
WScript.Echo WHAT_TO_FIND

Set FoundCell = oData.Worksheets("tst2").Range("A1:A40").Find(WHAT_TO_FIND)
oExcel.Cells(4,4) = FoundCell.Address


oExcel.ActiveWorkbook.SaveAs "D:\Work\Personal Timemanagement\test2.xlsx"
oExcel.ActiveWorkbook.Close

oExcel.Application.Quit

WScript.Quit

Thanks for the help!

Answer 1

WHAT_TO_FIND1 returns value like #14/10/2014#.So replace the # with nothing using WHAT_TO_FIND1=Replace(WHAT_TO_FIND,"#","Nothing").

Once replaced the above code will work

Answer 2

I tried your script, works perfect for me. For testing purposes I suggest you add the following line to the script

oExcel.Visible = True 

Also make sure all the privies instances of Excel are closed so your script could get write access. (open task manager and end all Excel process - unless you have other excel files open)

Now make sure the Worksheet is spelled correct "tst2" also ensure that the range is correct "A1:A40"

Keep us posted.

Answer 3

Find() returns Nothing when the given value isn't found in the given range. Make sure that the Range A1:A40 on sheet tst2 in the workbook D:\Work\Personal Timemanagement\test.xlsx actually contains a cell with the current date, and that the cell is also formatted as a date. Find() won't return a match if for instance you're looking for a date 7/11/2013 and the range contains a cell (formatted as text) with the string 7/11/2013. Modify your statement like this for finding "text" cells:

Set FoundCell = oData.Worksheets("tst2").Range("A1:A40").Find(CStr(WHAT_TO_FIND))