All about Excel in VBScript

‘Edit Excel based on certain values and preconditions..
‘If total is less than 35 then mark it in red else green
‘sheet name “Sheet 1

Dim EO, WS
Dim v1, v2, v3, v4, v5, v6
Dim Tot
Dim ro, co, CurR, CurC, LeftC, TopR
Dim RowRange, ColRange
Dim sPath
sPath=”C:\Users\V\Desktop\VB\edit_excel.xlsx”

Set EO=CreateObject(“Excel.Application”)
EO.Application.Visible=True
EO.WorkBooks.Open (sPath)
Set WS=EO.Sheets.Item(“sheet1”)

RowRange=WS.UsedRange.Rows.Count
Msgbox “Total Number of rows are: ” & RowRange
ColRange=WS.UsedRange.Columns.Count
LeftC=WS.UsedRange.Column
TopR=WS.UsedRange.Row
‘This is used to Bold the headers i.e first line in excel..

for i=LeftC to ColRange
    WS.Cells(1, i).Font.FontStyle=”BOLD”
    WS.Cells(1,i).Font.Size=”12″
    ‘WS.Range(“A”&i).Interior.ColorIndex=23
    ‘The following code is used to bold the first column data as header..
Next
for i=TopR to RowRange
    WS.Cells(i, 1).Font.FontStyle=”Bold”
    WS.Cells(i,1).Font.Size=”12″
Next

For ro=1 to RowRange-1
    CurR=ro+TopR
    ‘CurC=co+LeftC
    v1=WS.Cells(CurR, 3)
    v2=WS.Cells(CurR, 4)
    v3=WS.Cells(CurR, 5)
    tot=CDbl(v1)+CDbl(v2)+CDbl(v3)
    WS.Cells(CurR,6)=tot
    if CDbl(WS.Cells(CurR,6))<=30en
        WS.Cells(CurR, 6).Interior.ColorIndex=20
    Else
        WS.Cells(CurR, 6).Interior.ColorIndex=15
    End if
Next

EO.ActiveWorkBook.Save
EO.Quit
Set EO=Nothing

======================================================
Dim Dict
Dim EO, WS
Dim row,col
DIm CurC, CurR, LeftC, TopR
sPath="C:\Users\V\Desktop\VB\validation.xlsx"
DIm UsedRC, UsedCC

Set Dict =CreateObject("Scripting.Dictionary")
Set EO=CreateObject("Excel.Application")
EO.Application.Visible=True
EO.WOrkbooks.Open (sPath)
Set WS=EO.ActiveWorkBook.Sheets("sheet2")

UsedRC=WS.UsedRange.Rows.count
UsedCC=WS.UsedRange.Columns.Count
LeftC=WS.UsedRange.Column
TopR=WS.UsedRange.Row
For row=0 to UsedRC-1
    'For col=0 to UsedCC-1
    CurR= row+TopR
    ' CurC=col+LeftC
    Key1=WS.Cells(CurR,1).Value
    Value1=WS.Cells(CurR,2).Value
    if Dict.Exists(key1) then
        Dict(key1)=Value1 ' Assigning value to a key in a dict obj
        Msgbox "Value: " & Value1
    else
        Dict.Add Key1, Value1
        msgbox "M in Else Block" & Dict.Item(Key1) ' Getting the value corresponding to a key
    end if
    'Next
Next
Dict_Count=Dict.Count
Msgbox "Dict_Count: " & Dict_Count

Set Dict=Nothing
EO.Quit
Set EO=Nothing
===========================================================
Dim EO, WS
Dim sPath, v1,v2, v3, tot
sPath= "C:\Users\VenuGopi\Desktop\t.xls"
Set EO=CreateObject("Excel.Application")
EO.Application.Visible=True
EO.Workbooks.Open(sPath)

Set WS=EO.Worksheets.Item("Global
ro=2
Do until ws.cells(ro,1)=""
    v1=ws.cells(ro,3)
    v2=WS.cells(ro,4)
    v3=WS.Cells(ro,5)
    tot=v1+v2+v3
    WS.Cells(ro,6)=tot
    'msgbox v1
    ro=ro+1
loop
EO.ActiveWorkbook.Save
Msgbox "Saved Successfully!!"
EO.Quit
Set EO=Nothing

=================================================
Dim EO, WB
Dim sPath, v1, v2, v3, tot
sPath="C:\Users\V\Desktop\VB\validation.xlsx"
Set EO=CreateObject("Excel.Application")
EO.Application.Visible=True
Set WB= EO.WOrkbooks.Open (sPath)

t=2
Do Until EO.Cells(t,1).value=""
    val1=EO.Cells(t,3).value
    msgbox TypeName(val1)
    val2=EO.Cells(t,2).value
    msgbox val1 & vbTab & val2
    t=t+1
Loop
EO.Quit
Set EO=Nothing
===========================================================================
Dim EO, WS
Dim sPath, v1,v2, v3, tot
Dim CurR, CurC, r1, c1, LeftC, TopR, UsedR, UsedC

sPath= "C:\Users\V\Desktop\VB\validation.xlsx"
Set EO=CreateObject("Excel.Application")
EO.Application.Visible=True
EO.Workbooks.Open(sPath)

Set WS=EO.Worksheets.Item("sheet2
' Count total number of used rows and columns
UsedR=WS.UsedRange.Rows.Count
UsedC=WS.UsedRange.Columns.Count
'Get the Top row and left most column
TopR=WS.UsedRange.Row
msgbox "TopR : " & TopR ' this gives topr=1
'LeftC=WS.UsedRange.Column

On error Resume Next
    For r1=1 to UsedR-1 'start at row number 1
        'for c1=0 to UsedC-1
        CurR=r1+TopR
        ' CurC=c1+LeftC
        'msgbox WS.cells(CurR, 3)
        v1=WS.cells(CurR, 3)
        v2=WS.Cells(CurR, 4)
        v3=WS.Cells(CurR, 5)
        tot=v1+v2+v3
        WS.Cells(CurR,6)=tot
    Next

    if err.Number0 then
        Msgbox “Err. Description: “& err.Description
    else
        EO.ActiveWorkbook.Save
        Msgbox “Saved Successfully!!”
    end if

‘
EO.Quit
Set EO=Nothing

=========================================
‘ Based on WOrksheet now

Dim EO, WS
Dim row, col, Curcol, Currow, Counter
DIm TopR, LeftC
DIm UsedRC, UserCC
Dim V1, V2, V3, Tot

Set EO=CreateObject(“Excel.Application”)
EO.Application.Visible =True
EO.Workbooks.Open(“C:\Users\V\Desktop\VB\validation.xlsx”)

Set WS=EO.ActiveWorkBook.Sheets(“venu”)
On Error Resume Next
    UsedRC=WS.UsedRange.Rows.Count
    UsedCC=WS.UsedRange.Columns.count
    Msgbox “UsedRC: ” & UsedRC
    Msgbox “UsedcC: ” & UsedCC

    TopR=WS.UsedRange.row
    LeftC=WS.UsedRange.column
    Msgbox “TopR: ” & TopR
    Msgbox “LeftC: ” & LeftC
    if err.number0 then
        msgbox “Err.No: “& err.number & “err. Desc” & err.Description
    end if
EO.Quit
Set EO=Nothing
==============================================
‘Color Indexes printing along with the index numbers in corresponding cells..
‘ Starting from 1 to 100 in ten rows and ten columns..

Dim EO, WS
Dim row1, col1
Dim sPath
sPath=”C:\Users\Venugopi\Desktop\VB\edit_excel.xlsx”

Set EO=CreateObject(“Excel.Application”)
EO.Application.Visible=True
EO.Workbooks.Open(sPath)
Set WS=EO.Sheets.Item(“sheet2″)
k=1
On Error Resume Next
for row=1 to 10
for col=1 to 10
WS.Cells(row,col).Interior.ColorIndex=k
WS.Cells(row,col)=k
WS.Cells(row,Col).Font.FontStyle=”Bold”

k=k+1
Next
Next
EO.ActiveWorkBook.save
‘EO.Quit
On Error Goto 0
Set EO=Nothing