WshShell | Code to change System Date

You might want to change the windows system Date/Time during a script runtime, below is the code for the same.

Var_Date="01.09.2014"

Dim WS

Set WS= CreateObject("WScript.Shell")

WS.run "cmd"

window("object class:=ConsoleWindowClass").Type "date " & Var_Date

window("object class:=ConsoleWindowClass").Type chr(13)

window("object class:=ConsoleWindowClass").Type "exit"

window("object class:=ConsoleWindowClass").Type chr(13)

Set WS= nothing

Excel Macro | Function to find a value in specified column

One of those functions i used long back to find the row based on column name and value to be searched passed to the function as parameters,

Code:

Function FindRow(col_Name,ValueToFind)

Set exobj=createobject("excel.application")

exobj.visible = True

exobj.workbooks.open("C:\Documents and Settings\osnpt150\Desktop\Products.xls")

exobj.worksheets("Product_Testing_Grid").Activate

usedrowscount=exobj.worksheets("Product_Testing_Grid").UsedRange.Rows.Count

num=1

For n=num to usedrowscount

set  rng=exobj.worksheets("Product_Testing_Grid").Range(col_Name & n &":" & col_Name & usedrowscount)

On Error Resume Next

rng.Find(ValueToFind).Activate '', After:=ActiveCell, LookIn:=xlFormulas, LookAt :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= False, SearchFormat:=False).Activate

If err.number=0 Then

num=exobj.Activecell.Address

msgbox "Found at row" & num

num=mid(num,4,len(num)-3)

n=num

else

Exit for

End If

On Error goto 0

Next

exobj.quit  

set exobj=nothing

End Function