Working With MS Excel

'Create a new excel, insert data into Cell, rename the sheet, Save and Close

Set objExcel = CreateObject("Excel.Application")

objExcel.Workbooks.Add

Set NewSheet = objExcel.Sheets.Item(1)

NewSheet.Name = "New"

NewSheet.Cells(2,2) = "USER"

objExcel.ActiveWorkbook.SaveAs "D:\ENCRYPTED\LearningQTP\CreateExcel.xlsx"

objExcel.Quit

Set objExcel = Nothing

Set objWorkBook = Nothing

Set objWorkSheet = Nothing

'Excel Search String Color Text and Bold and FontSize

Dim vrSearchString

Set objExcel = CreateObject("Excel.Application")

Set objWorkbook = objExcel.Workbooks.Open("D:\ENCRYPTED\Learning_QTP\Data_Files\Test.xls")

Set objWorkSheet = objExcel.Worksheets("Sheet3")

objExcel.Application.Visible = True

With objWorkSheet.UsedRange 'select the used range in particular sheet

Set vrSearchString = .Find (hardeep) ' data to find

For each vrSearchString in objWorkSheet.UsedRange' Loop through the used range

If vrSearchString="hardeep" then '’ compare with the expected data

   vrSearchString.Interior.ColorIndex = 40' make the yellow color if it finds the data

   vrSearchString.Font.FontStyle = "Bold"

   vrSearchString.Font.ColorIndex = 5

   vrSearchString.Font.Size = 14

CellAddress =vrSearchString.address

msgbox CellAddress

End If

Set vrSearchString = .FindNext(vrSearchString) ' next search

Next

End With

objExcel.ActiveWorkbook.Save

objExcel.Quit

Set objExcel = Nothing

Set objWorkBook = Nothing

Set objWorkSheet = Nothing

'Open an existing excel, Active sheet2, insert data into Cells, Save and Close

Set objExcel = CreateObject("Excel.Application")

Set objWorkBook = objExcel.Workbooks.Open("D:\ENCRYPTED\LearningQTP\Test.xlsx")

objExcel.Application.Visible = True

Set objWorkSheet = objWorkBook.Worksheets("Sheet2")

objExcel.Worksheets("Sheet2").Select

objWorkSheet.Cells(1,1) = "This"

objWorkSheet.Cells(2,1) = "is"

objWorkSheet.Cells(3,1) = "for"

objWorkSheet.Cells(4,1) = "QTP"

objWorkSheet.Cells(5,1) = "TEST"

objExcel.ActiveWorkbook.Save

objExcel.Quit

Set objExcel = Nothing

Set objWorkBook = Nothing

Set objWorkSheet = Nothing

'Open an existing excel, Insert a new sheet, insert data into Cells, Save and Close

Set objExcel = CreateObject("Excel.Application")

Set objWorkbook = objExcel.Workbooks.Open("D:\ENCRYPTED\LearningQTP\Test.xlsx")

Set objWorkSheet = objExcel.Worksheets("Sheet3")

objExcel.Application.Visible = True

objExcel.Worksheets.Add

Set NewSheet = objExcel.Sheets.Item(1)

NewSheet.Name = "New Worksheet"

NewSheet.Cells(2,2) = "USER"

objExcel.ActiveWorkbook.Save

objExcel.Quit

Set objExcel = Nothing

Set objWorkBook = Nothing

Set objWorkSheet = Nothing

'Open an existing excel, Rename a worksheet, Save and Close

Set objExcel = CreateObject("Excel.Application")

Set objWorkbook = objExcel.Workbooks.Open("D:\LearningQTP\Test.xlsx")

Set objWorkSheet = objExcel.Worksheets("HS")

objExcel.Application.Visible = True

objExcel.Sheets("Sheet5").Select

objExcel.Sheets("Sheet5").Name = "ABCDE"

objExcel.ActiveWorkbook.Save

objExcel.Quit

Set objExcel = Nothing

Set objWorkBook = Nothing

Set objWorkSheet = Nothing

'Open an existing excel, Search for a word, Highlight it, Save and Close

Set objExcel = CreateObject("Excel.Application")

Set objWorkbook = objExcel.Workbooks.Open("D:\ENCRYPTED\LearningQTP\Test.xlsx")

Set objWorkSheet = objExcel.Worksheets("Sheet3")

objExcel.Application.Visible = True

With objWorkSheet.UsedRange 'select the used range in particular sheet

Set c = .Find ("hardeep") ' data to find

For each c in objWorkSheet.UsedRange' Loop through the used range

If c="hardeep" then '’ compare with the expected data

c.Interior.ColorIndex = 40' make the yellow color if it finds the data

CellAddress =c.address

msgbox CellAddress

End If

Set c = .FindNext(c) ' next search

next

End With

objExcel.ActiveWorkbook.Save

objExcel.Quit

Set objExcel = Nothing

Set objWorkBook = Nothing

Set objWorkSheet = Nothing