Excel operation - to find usedrange of rows & col

Excel operation - to find usedrange of rows & col - Printable Version

+- Micro Focus QTP (UFT) Forums (https://www.learnqtp.com/forums)
+-- Forum: Micro Focus UFT (earlier known as QTP) (https://www.learnqtp.com/forums/Forum-Micro-Focus-UFT-earlier-known-as-QTP)
+--- Forum: UFT / QTP Beginners (https://www.learnqtp.com/forums/Forum-UFT-QTP-Beginners)
+--- Thread: Excel operation - to find usedrange of rows & col (/Thread-Excel-operation-to-find-usedrange-of-rows-col)

Excel operation - to find usedrange of rows & col - pooja - 02-17-2015

Hi,
I am very much new to Qtp,
I am trying to write some data to excel sheet.

I want to get the usedrange of rows in Excel and write the data in next available row, by passing the column name as parameter.
Kindly help me with this.

Ex: Excel sheet somthing like this
Name ID Ph
aa 12 52451
bb 5 5654

Code:
Function XXX(Byval path,Byval sheetname,Byval columnname)

    Dim objXL,wkb,ws

    Set objXL = CreateObject("Excel.Application")

    objXL.visible = True

    Set wkb = objXL.Workbooks.Open(path)

    Set ws = wkb.Sheets(sheetname)

    ws.Activate

    iRows = ws.UsedRange.Rows.count

    icol = ws.usedRange.columns.count

    Next_Row_Range = iRows +1

    Add_Column_Range = icol +1

             ws.cells(Next_Row_Range,columnname).value ="abc"

End Function

ws.cells(Next_Row_Range,columnname).value ="abc" -> i am going wrong here. I wanna pass the columnname & write data into next available row in particular column.

Thanks in advance.

Regards,
Pooja

RE: Excel operation - to find usedrange of rows & col - supputuri - 02-19-2015

You can get the total number of columns and then find the column number based on your columnname parameter. Then use the same in your code dynamically.

Please let me know if you need any more help on this.