Software Programs

‘how to create an excel using vb script
function create
set xl=createobject(“excel.application”)
xl.workbooks.add
xl.visible=true
set xl=nothing
end function

‘ read excel data using vb script
function read
set xl=createobject(“excel.application”)
set wb=xl.workbooks.open(“d:\sample.xls”)
set ws=wb.worksheets(1)
read=ws.cells(1,2).value
msgbox read
wb.close
set ws=nothing
set wb=nothing
set xl=nothing
end function

‘ write data into excel using vb script

function write
set xl=createobject(“excel.application”)
set wb=xl.workbooks.open(“d:\sample.xls”)
set ws=wb.worksheets(2)     ‘second sheet in the excel
ws.cells(6,8).value=”india”
wb.save
wb.close
set ws=nothing
set wb=nothing
set xl=nothing
end function

‘ format excel using vb script
function format
set xl=createobject(“excel.application”)
set wb=xl.workbooks.open(“d:\sample.xls”)
set ws=wb.worksheets(1)
ws.cells(4,5).value=”hai”
ws.cells(4,5).font.bold=true
ws.cells(4,5).font.colorindex=50
ws.cells(4,5).font.size=18
ws.cells(4,5).interior.colorindex=20
wb.save
wb.close
set . . . → Read More: play with excel in vb script

‘ main functions used in vb script

‘array –>used for array creation
a=array(12,13,14,15)
msgbox a(2)

‘ cint function–>converts into interger
‘ inputbox function–>takes user input
a=cint(inputbox(“enter number”))
msgbox a

‘ createobject —>used for creating objects
set obj=createobject(“scripting.filesystemobject”)
obj.createtextfile(“d:\test5.txt”)

‘ date and day
msgbox date
msgbox day(date)

‘ isarray –>returns true if if the argument is an array
b=array(12,13)
msgbox isarray(b)

‘ lbound & ubound
msgbox lbound(b)& ” ” &ubound(b)

‘ lcase and ucase

str=”aBcD”
msgbox lcase(str)&” “  . . . → Read More: how to use functions in vbscript

‘ All the functions under FileSystemObject
set files = createobject(“scripting.filesystemobject”)
‘set f1=files.createtextfile(“d:\test.txt”)

‘ File Append Mode
set f_appendmode=files.opentextfile(“d:\test1.txt”,8,true)
f_appendmode.writeline(“welcome to file append mode”)

‘ File ReWrite Mode
set f_rewritemode=files.opentextfile(“d:\test2.txt”,2,true)
f_rewritemode.writeline(“welcome to file re write mode”)

‘ File Read Mode
set f_readmode=files.opentextfile(“d:\test1.txt”,1,true)
str=f_readmode.readline
msgbox str

‘ File Exists or Not
path=”d:\test1.txt”
if files.fileexists(path) then
msgbox “the file “&path&” exists”
end if

‘ File Create,Copy,Delete Operations
files.createtextfile(“d:\test3.txt”)
files.copyfile “d:\test3.txt”, “c:\”
files.deletefile “d:\test3.txt”

set fso=files.getfile(path)
msgbox files.getparentfoldername(path)

‘ If u want to work . . . → Read More: FileSystemObject in vbscript

‘ open two excel sheets and compare them. give o/p    as :  match/mismatch
set xl=createobject(“excel.application”)
set wb1=xl.workbooks.open(“D:\first.xls”)
set sh1=wb1.worksheets(1)
r=sh1.usedrange.rows.count
c=sh1.usedrange.columns.count
‘set wb2=xl.workbooks.open(inputbox(“select execl sheet”))
set wb2=xl.workbooks.open(“D:\second.xls”)
set sh2=wb2.worksheets(1)
‘msgbox r
for i=1 to r
for j=1 to c
temp1=sh1.cells(i,j).value
temp2=sh2.cells(i,j).value
if temp1<>temp2 then

sh1.cells(i,j).font.bold=true
sh1.cells(i,j).font.colorindex=50
sh1.cells(i,j).interior.colorindex=20
sh2.cells(i,j).font.bold=true
sh2.cells(i,j).interior.colorindex=20

end if
next
if i=r and j=c then
msgbox “Both excels are equal”
exit for
end if
next

if i<>r or j<>c then
msgbox “Both Excels are not equal”
end if
wb1.save
wb1.close
wb2.save
wb2.close

set sh1=nothing
set wb1=nothing
set sh2=nothing
set . . . → Read More: Excel comparison in vb script