Excel Operation in VBScript

Excel Operation in VBScript

In this post we will see how to interact with excel application through vb script, QTP / UFT, Microsoft excel follows hierarchy mentioned as below

Excel -> Workbook -> Worksheet -> cells.

Using vb script we can perform below operation on excel object

1.       Create New Excel sheet

2.       Open Existing File

3.       Add new sheet under same workbook

4.       Copy Data from another sheet

5.       Format excel tables and cells

6.       Read data from excel

7.       Write data in excel

8.       Remove sheet from workbook

9.       Hide/Unhide sheet in workbook

10.   Protect/Unprotect sheet

Also you see above operations video tutorial

Create New Excel sheet

Dim xl                 '' Create new excel file                 Set xl = CreateObject("Excel.Application")                 xl.Visible = False                 xl.Workbooks.Add                 xl.ActiveWorkbook.SaveAs "C: \TestFile.xlsx"                                     xl.quit                 Set xl = Nothing

Open Existing File

Dim xl Dim xlWorkbook Dim xlWorksheet                 '' Create new excel file                 Set xl = CreateObject("Excel.Application")                 xl.Visible = True                 xl.Application.DisplayAlerts = False                 xl.Workbooks.Open("C:\TestFile.xlsx")                                 'xl.quit                 Set xl = Nothing

Add new sheet under same workbook

Set NewSheet = xlWorkbook.Worksheets.Add

Copy Data from another sheet

Dim xl Dim xlWorkbook Dim xlWorksheet                 '' Create new excel file                 Set xl = CreateObject("Excel.Application")                 xl.Visible = True                 xl.Application.DisplayAlerts = False                 Set xlWorkbook  = xl.Workbooks.Open("C:\TestFile.xlsx")                '' Copy cell data from sheet1                 xlWorkbook.Worksheets(“Sheet1”).Range(“A1:C5”).Copy                '' paste cell data in sheet2                 xlWorkbook.Worksheets(“Sheet2”).Range(“A1”).PasteSpecial                 Set xl = Nothing

Format excel tables and cells

'' Add border to table xlWorkbook.Worksheets(“Sheet1”).Range(xlWorkbook.Worksheets(“Sheet1”).Cells(1,5) , xlWorkbook.Worksheets(“Sheet1”).Cells(5,5)).Borders(12).LineStyle = 1

Read data from excel

Dim xl Dim xlWorkbook Dim xlWorksheet Dim iRowCount,iColCount,iRow,iCol                 '' Create new excel file                 Set xl = CreateObject("Excel.Application")                 xl.Visible = True                 xl.Application.DisplayAlerts = False                 Set xlWorkbook = xl.Workbooks.Open("C:\TestFile.xlsx")                 xl.Application.DisplayAlerts = False                 Set xlWorksheet = xlWorkbook.Worksheets(1)                                                 ''Read data from xl                 iRowCount = xlWorksheet.UsedRange.rows.count                 iColCount = xlWorksheet.UsedRange.Columns.count                                 ''print data from xl                 For iRow = 1 to iRowCount                  For iCol = 1 to iColCount                           Msgbox xlWorksheet.Cells(iRow,iCol).Value                  Next                 Next

Write data in excel

Dim xl Dim xlWorkbook Dim xlWorksheet                 '' Create new excel file                 Set xl = CreateObject("Excel.Application")                 xl.Visible = True                 xl.Application.DisplayAlerts = False                 Set xlWorkbook = xl.Workbooks.Open("C:\TestFile.xlsx")                                 Set xlWorksheet = xlWorkbook.Worksheets(1)                                 '' Write data in excel                 xlWorksheet.cells(1,1).value ="Tutorial Name"                 xlWorksheet.cells(1,2).value ="Tutorial Date"                 xlWorksheet.cells(1,3).value ="Source"                 xlWorksheet.cells(2,1).value ="Excel operations"                 xlWorksheet.cells(2,2).value = Date                 xlWorksheet.cells(2,3).value ="vb script"                 'xl.quit                 Set xl = Nothing

Remove sheet from workbook

xl.Application.DisplayAlerts = False                 '' Delete sheet from workbook                xlWorkbook.sheets(“sheet1”).Delete

Hide/Unhide sheet in workbook

'' hide sheet  from workbook                xlWorkbook.sheets(“sheet2”).Visible = False              ''Unhide sheet  from workbook                xlWorkbook.sheets(“sheet2”).Visible = true

Protect/Unprotect sheet

'' protect sheet  from workbook               If xlWorkbook.sheets(“sheet2”).ProtectContent= false then                xlWorkbook.sheets(“sheet2”).Protect Password:”TestPWD”, Userinterfaceonly=True              End if              '' Unprotect sheet  from workbook               If xlWorkbook.sheets(“sheet2”).ProtectContent=true then                xlWorkbook.sheets(“sheet2”).UnProtect Password:”TestPWD”              End if

happy learning 🙂