Forum Discussion

wynand_bwr's avatar
wynand_bwr
Occasional Contributor
13 years ago

Openig and Edit of Excel Spredsheet

Hi



Can anyone please assist me in finding a easy way to open a excel spreadsheet and editing the data inside the sheet with TC?



The sheet contains test data. I want to open the sheet at the end of my keyword test and delete the first row of data in the sheet.



I have tried recording the steps to just open the sheet inside a directory on my drive.

TC navigates to the directory, but fails to open the sheet?



My plan is to populate the sheet with a 1000 rows of data to use as a data loop. Then at the end of the test just delete the first row of data. Thus there will be new data to use each time I run the test. THE KEYWORD TEST WILL BE USED FOR REGRESSION TESTING.



The data in the sheet cannot be used again. Im in the banking industry and the data in the sheet is account numbers that can only be used once. Thats why I need to delete the first row of the sheet at the end of the test.



Thanks in advance.
  • googleid_118035's avatar
    googleid_118035
    Contributor
    13 years ago
    First I have create new unit under Advanced > Script

    Rename the unit as "VB_Func"



    Now added following two funtion in that sheet.



    Function Get_value_from_file(file,sheet,x,y)

    Dim b,c

    Set b = CreateObject("Excel.Application")

    b.DisplayAlerts = False

    Set c = b.Workbooks.Open(file)

    Get_val_from_File = c.Worksheets(sheet).Cells(y, x).Value

    c.Close True

    b.Quit

    End Function

    Function Insert_value_into_file(file, sheet, x, y, texeres)

    Set b = CreateObject("Excel.Application")

    b.DisplayAlerts = False

    Set c = b.Workbooks.Open(file)

    c.Worksheets(sheet).Cells(y, (x+18)).Value = texeres

    c.Save

    c.Close True

    b.Quit

    End Function



    create new unit under Advanced > Script

    Rename the unit as "Test"



    Now added following line into "Test" sheet.



    Sub Test()

    'USEUNIT VB_Func



    'declare veriables

    file,sheet,x,y,my_veriable,my_inserting_value



    'setup veriables

    file = "C:\Folder_Name\Test_Data.xls"

    sheet = "Sheet1"

    x = 3 ' column number

    y = 2 ' row numer



    'assign Excel file value into a veriables

    my_veriable = VB_Func.Get_value_from_file(file,sheet,x,y)



    'insert veriable value into an Excel file.

    Call VB_Func.Insert_value_into_file(file, sheet, x, y, my_inserting_value)



    End Sub
  • wynand_bwr's avatar
    wynand_bwr
    Occasional Contributor
    13 years ago
    Hi



    Thanks for the reply!!!



    I came up with a solution myself last week.



    What I did was create a VB script file with code. The created a .BAT that calls this file and executes it.



    VBS :

    Set aa = CreateObject("Excel.Application")

    Set bb = aa.Workbooks.Open("C:\TC_Regression_Test_Data\TC_Regression_Test_Data.xls")

    Set cc = aa.worksheets("Sheet1")

    cc.Rows(2).Delete

    bb.Save

    aa.Workbooks.Close()



    BAT :

    @echo off

    start c:\TC_Regression_Test_Data\TC3.vbs



    pause
  • wynand_bwr's avatar
    wynand_bwr
    Occasional Contributor
    13 years ago
    Forgot to mention that the dataloop wil not be using all the data in the sheet in a loop. I will be useing the test at random times just for one time or maybe 2 times. So that is why do select from beginning to end when selecting the file to be used in the data loop.