SPONSORS:






User Tag List

Thanks Thanks:  0
Likes Likes:  0
Dislikes Dislikes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12
  1. #1
    Member
    Join Date
    May 2003
    Location
    Mumbai,Maharashtra,India
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    How to read Write data from Excel Sheet

    Hi,
    I'm new to Testcomplete.
    just started...
    I wud like to know how should I read or write values to an excel sheet.
    I tried it in .txt file it works..
    But for data driven we have created a test data in excel sheet (Tabular format)
    I happend to check the sample scripts DDt\delphi script but was not able to understand.
    If any body could help me out with its code??
    I'm trying out using VBscript
    Thanks & Regards,
    Alifiya

  2. #2
    Member
    Join Date
    Oct 2002
    Location
    Kansas City
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: How to read Write data from Excel Sheet

    Option Explicit
    Sub Main()

    Dim oExcel
    Dim oWorkBook
    Dim i
    Dim j
    Dim r
    Dim c

    Set oExcel = CreateObject("Excel.Application")
    With oExcel
    .Visible = True
    Set oWorkBook=.Workbooks.Open("C:\TEST_VBScript.xls")
    r = 1
    c = 1
    For i = 1 To 10
    For j = 1 To 10
    .Cells(r, c).Value = "TEST" & " Row: " & r & " Column: " & c
    r = r + 1
    Next
    r = 1
    c = c + 1
    Next
    .Cells.Select
    .Selection.Columns.AutoFit
    .Range("A1").Select
    oWorkBook.Save
    oWorkBook.Close
    Set oWorkBook = Nothing
    .Quit
    End With
    Set oExcel = Nothing

    End Sub

    This routine will write to Excel. To read from Excel you use the same techniques of the Excel Object Model. If you are not familiar with it, you can record VBA macros and inspect the results. Hope this helps. This is a useful link:

    http://msdn.microsoft.com/library/de...bjectModel.asp

    [ 05-20-2004, 08:24 AM: Message edited by: kadams ]

  3. #3
    Junior Member
    Join Date
    Sep 2001
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: How to read Write data from Excel Sheet

    Hi Alifiya,

    Here is another script sample as you requested. It gets access to Excel via OLE, reads the "Qnt" worksheet of the "Book1.xls" file and posts the data to the Test Log panel.

    Sub Test
    Set xcl = Sys.GetOleObject("Excel.Application")
    Sys.Delay(3000)
    xcl.Workbooks.Open("C:\Test\Book1.xls")
    Set WorkSheet = xcl.Workbooks.Item(1).Worksheets.Item("Qnt")
    xcl.Visible = True
    WorkSheet.Activate
    xcl.Cells(1,1).Activate()
    ColumnCount = xcl.ActiveCell.CurrentRegion.Columns.Count
    RowCount = xcl.ActiveCell.CurrentRegion.Rows.Count
    For i = 1 to ColumnCount
    Log.CreateNode("Column " + xcl.Cells(1, i).Value)
    For j = 2 To RowCount
    Log.Message(IntToStr(xcl.Cells(j, i).Value))
    Next
    Log.CloseNode
    Next
    xcl.Quit
    End Sub


    Atanas
    AutomatedQA

  4. #4
    Junior Member
    Join Date
    Mar 2005
    Location
    as
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: How to read Write data from Excel Sheet

    Hi
    could anyone help me with the same model that helped Alifiya but this time in delphi, because I see that the model presented dosen't work.

    thanks
    szmen

  5. #5
    Senior Member
    Join Date
    Jan 2002
    Location
    UK
    Posts
    108
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: How to read Write data from Excel Sheet

    </font><blockquote><font size="1" face="Verdana, Arial, Helvetica">code:</font><hr /><pre style="font-size:x-small; font-family: monospace;">procedure ExcelReader;
    //sample script to read specific cell values in an active spreadsheet
    var
    MsExcel, i;
    begin
    MsExcel := Sys.GetOleObject('Excel.Application').ActiveSheet;
    MsExcel.Visible := True;
    for i := 1 to 24 do
    //Cells[row 1..., column 1...]
    Log.Message(MsExcel.Cells[i,1].Value);
    MsExcel.Quit;
    end;</pre><hr /></blockquote><font size="2" face="Verdana, Arial, Helvetica">Assuming Excel is already open and showing the spreadsheet you want, this logs the contents of the first 24 rows in the first column.

  6. #6
    Member
    Join Date
    Feb 2003
    Location
    Frisco, TX, USA
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: How to read Write data from Excel Sheet

    Hi szmen,

    You mentioned that a presented model does not work. Please specify which script exactly does not work for you and which errors you get.

    To complement Nick's code, here is the Delphi script converted from the posted VBScript:

    </font><blockquote><font size="1" face="Verdana, Arial, Helvetica">code:</font><hr /><pre style="font-size:x-small; font-family: monospace;">procedure Test;
    var
    xcl, ColumnCount, RowCount;
    WorkSheet, i, j;
    begin
    xcl := Sys.GetOleObject('Excel.Application');
    Sys.Delay(3000);
    xcl.Workbooks.Open('C:\Test\Book1.xls');
    WorkSheet := xcl.Workbooks.Item(1).Worksheets.Item('Qnt');
    xcl.Visible := True;
    WorkSheet.Activate;
    xcl.Cells(1,1).Activate;
    ColumnCount := xcl.ActiveCell.CurrentRegion.Columns.Count;
    RowCount := xcl.ActiveCell.CurrentRegion.Rows.Count;
    for i := 1 to ColumnCount do
    begin
    Log.CreateNode('Column ' + xcl.Cells(1, i).Value);
    for j := 2 to RowCount do
    Log.Message(IntToStr(xcl.Cells(j, i).Value));
    Log.CloseNode;
    end;
    xcl.Quit;
    end;</pre><hr /></blockquote><font size="2" face="Verdana, Arial, Helvetica">
    Robert Leahey - TeamAQA
    Thoughtsmithy Software
    Note: I am not an employee of AutomatedQA - my support here is on a volunteer basis.

  7. #7
    Junior Member
    Join Date
    Mar 2005
    Location
    as
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: How to read Write data from Excel Sheet

    Hi leahey ,

    Thanks for the model.
    I was refering actually to the model presented in the samples in TC. I use delphi to create the applications and the samples presented for DDT doesn,t work. the procedure MainTest(Mode: Integer; WithBug: Boolean);
    var
    p, w, wOrder: OleVariant;
    begin
    // TestedApps.RunAll;

    p := Sys.WaitChild('OrdersProcess', 10000);
    w := p.WaitChild('OrdersMainForm', 10000);
    w.Activate;
    The error is in the last line of the code presented(the window couldn't be activate).I can't izolate the problem, I will be gratefull.
    Thanks,
    szmen

  8. #8
    Member
    Join Date
    Feb 2003
    Location
    Frisco, TX, USA
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: How to read Write data from Excel Sheet

    Hi szmen,

    Have you followed the instructions presented in the very beginning of the project's MainUnit unit? Have you compiled the corresponding application and checked whether the application is added to the list of Tested Applications (File | Tested Applications...)? Most likely, the cause of the problem is that TestComplete cannot find the OrdersDemo application and cannot launch it.
    Robert Leahey - TeamAQA
    Thoughtsmithy Software
    Note: I am not an employee of AutomatedQA - my support here is on a volunteer basis.

  9. #9
    Junior Member
    Join Date
    Mar 2005
    Location
    as
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: How to read Write data from Excel Sheet

    Yes I did that!
    But the Tc when it runnes ORDRES.exe it collaps in the moment when it is wainting for the ordersform to start.
    Do you have any ideea, I would be grateful if you cand help me to solve the problem.
    thanks
    szmen

  10. #10
    Member
    Join Date
    Feb 2003
    Location
    Frisco, TX, USA
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: How to read Write data from Excel Sheet

    Hi szmen,

    Could you please clarify what you mean by saying that TestComplete collapses? Do you get a dumper's window that suggests you to send a bug report to AutomatedQA? If yes, please contact our support team (support@automatedqa.com) regarding this issue and send them all the .dmp files located in the <TestComplete 3>\Logs folder.
    Thanks in advance.
    Robert Leahey - TeamAQA
    Thoughtsmithy Software
    Note: I am not an employee of AutomatedQA - my support here is on a volunteer basis.

 

 
Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Search Engine Optimisation provided by DragonByte SEO v2.0.36 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Resources saved on this page: MySQL 8.82%
vBulletin Optimisation provided by vB Optimise v2.6.4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.2.8 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
vBNominate (Lite) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Feedback Buttons provided by Advanced Post Thanks / Like (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Username Changing provided by Username Change (Free) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
BetaSoft Inc.
Digital Point modules: Sphinx-based search
All times are GMT -8. The time now is 02:47 AM.

Copyright BetaSoft Inc.