How to read and write in excel through QTP script

[shwetha_m]

Hi I am new to this tool, Can any one pls send me step by step procedure on how to read and write the data in excel?

Regards,
Shwetha

[rajpes]

https://www.learnqtp.com/qtp-and-excel-part1/

[shwetha_m]

Hi,
when i run the below script.

Code:

rc = DataTable.Value ("A", dtGlobalSheet)
msgbox rc
Browser("Google").Page("Google").WebEdit("q").Set rc

Error is displayed as below :

The retrieve DataTable.Value operation failed. The <A> column does not exist.

Line (2):

Code:

"rc = DataTable.Value ("A", dtGlobalSheet)".

Can anyone please assist on this?

Regards,
Shwetha

[rajpes]

shwetha,
they are not user defined column names.Double click on that column heading (A) and give some name

[kishorambare]

[quote='shwetha_m' pid='19969' dateline='1328977787']
Hi I am new to this tool, Can any one pls send me step by step procedure on how to read and write the data in excel?

Regards,
Shwetha

Hi Shweta,

PFB:

Code:

dim ExcelObj ,NewSheet
Set ExcelObj = CreateObject("Excel.Application")
ExcelObj.Workbooks.Add
Set NewSheet = ExcelObj.Sheets.Item(1)
NewSheet.Name = "name of excel file"
NewSheet.Cells(2,2) = variable or string
ExcelObj.ActiveWorkbook.SaveAs "D:\name of excel file"
ExcelObj.Quit

[venkatesh9032]

Hello shwetha...data table is different and excel is different...

if you wan t to read the data you should use some functions...i will send the sample code..you willl understand and try to google further...

'Create a new Microsoft Excel object

Code:

Set myxl = createobject("excel.application")

'To make Excel visible

Code:

myxl.Application.Visible = true
  
myxl.Workbooks.Add
wait 2

'Save the Excel file as qtp.xls

Code:

myxl.ActiveWorkbook.SaveAs  "D:\qtp.xls"

'close Excel

Code:

myxl.Application.Quit

Set myxl=nothing





////To enter some data



myxl.Workbooks.Open "D:\qtp.xls"
myxl.Application.Visible = true

'this is the name of Sheet in Excel file "qtp.xls" where data needs to be entered

Code:

set mysheet = myxl.ActiveWorkbook.Worksheets("Sheet1")

'Enter values in Sheet1.
'The format of entering values in Excel is

Code:

excelSheet.Cells(row,column)=value
mysheet.cells(1,1).value ="Name"
mysheet.cells(1,2).value ="Age"
mysheet.cells(2,1).value ="Ram"
mysheet.cells(2,2).value ="20"
mysheet.cells(3,1).value ="Raghu"
mysheet.cells(3,2).value ="15"

///Try this.....u will get