Pages

Wednesday, March 2

Working with Excel Sheets - create, open, modify, save, close!!

'Getting value from an existing excel sheet

Dim oExcel, oWB, oSheet, getVal
Set oExcel=CreateObject("Excel.Application")
Set oWB=oExcel.Workbooks.Open("C:\abhikansh.xls")
Set oSheet=oWB.WorkSheets("Sheet1")
oExcel.Visible=TRUE
getVal=oSheet.Cells(1,1).Value
print getVal
oWB.Close
Set oExcel=Nothing


'Inserting value to an existing excel sheet
Dim oExcel, oWB, oSheet, setVal
Set oExcel=CreateObject("Excel.Application")
Set oWB=oExcel.Workbooks.Open("C:\abhikansh.xls")
Set oSheet=oWB.WorkSheets("Sheet1")
oExcel.Visible=TRUE
setVal="ValueInserted"
oSheet.Cells(4,1).Value=setVal

oWB.Save
oWB.Close
Set oExcel=Nothing


'Create a new excel sheet, insert values and save it..
Dim oExcel, oWB, oSheet, setVal
Set oExcel=CreateObject("Excel.Application")
Set oWB=oExcel.Workbooks.Add
Set oSheet=oWB.WorkSheets("Sheet1")
oExcel.Visible=TRUE
setVal="ValueInserted"
oSheet.Cells(4,1).Value=setVal

oWB.SaveAs("C:\abhikansh1.xls")
oWB.Close
Set oExcel=Nothing

7 comments:

  1. Nice example. I am having trouble closing an Excel file which opens from web application. Could anyone please tell me how can I close that file. The recorded script doesn't work.

    Rosh.

    ReplyDelete
  2. Hi Abhikansh, nice code working, my scenario is to save web table contents suppose the web table has 100 rows and 10 columns, Can u please let me know the code How to save those values into an external excel sheet.

    ReplyDelete
  3. Hi Abhikansh, how do you actually work with an existen (already open) Excel sheet

    Thank you

    ReplyDelete
  4. Hi,

    I hope in the script 2 and script 3 print syntax not reuired it does not return any value.Bcoz getval no where delcared,,plz correct me. thanks in advance

    ReplyDelete
  5. Hi

    I have an one query. How to pass dynamically path for excel workbook open whether to pass the actual location of that file for particular drive. When i am running it on my local machine it works good but whenever it runs on any other machine i have to put whole project that particular drive. So i don't want it.
    As like this there is also one concern for upload file, here i have to set the path. I also want it dynamically. So give me useful reply please.

    For open excel workbook my coding is "Set myxl = createobject("excel.application")
    myxl.Workbooks.Open "D:\qtp.xlsx"

    myxl.Application.Visible = true
    set mysheet = myxl.ActiveWorkbook.Worksheets("upload_loyalty_multiple")
    Row=mysheet.UsedRange.Rows.Count
    Col=mysheet.UsedRange.columns.count
    scompanyname=mySheet.Rows(2).Columns(1).Value"

    I don't want this. I want dynamic.

    For upload file my coding is " Browser("Coupon Management System").Page("Coupon Management System_2").WebFile("loyaltyUIForm.UploadFile").Set("D:\rajesh's doc\CMS_Coupon_atTill\Testing\19th march\loyalty_multipromo_sample.csv") "

    I don't want this. I want dynamic.

    ReplyDelete
  6. Hello, i am trying to manipulate data from an existing excel using below code.But i am getting a UFT compiling error as "Expected ')" while saving the code,kindly help if possible

    Set objXL = GetObject(","Excel.Application")

    ReplyDelete