Forum Discussion

Laxmi_1's avatar
Laxmi_1
Occasional Contributor
15 years ago

Read and Write Data into Excel

Hi,



In our application, we open some excel documents from data base and read\write data into that excel document and perform some calculations.

When i try to record excel document, i see that individual cells in the excel are not recorded and instead pixel positions are recorded.



Is there some way to read and write data into excel which is already opened.

I found some code for reading data from Excel, but it specified the path where excel is present. But it may not help us as we dont have excel's stored in the machine. We fetch them from DataBase.



Please help me find a way to do this.



Thanks in advance,

Laxmi.

6 Replies

Sort By
  • YMinaev's avatar
    YMinaev
    Staff
    15 years ago
    Hi Laxmi,



    You need to work with Excel via COM. To obtain its COM object (Excel.Application), you can use the Sys.OleObject property. Information on Excel's object model can be found in its built-in VBA reference.



  • Laxmi_1's avatar
    Laxmi_1
    Occasional Contributor
    15 years ago
    Hi Jared,



    Query1:



    I have found this code by searching online. But this code opens an excel from a specific path, here d:\B.xlsx



    Sub WriteDataIntoExcel

    set objexcel = createobject("excel.application")

    Set objWorkbook = objExcel.WorkBooks.Open("d:\B.xlsx")

    Set objDriverSheet = objWorkbook.Worksheets("Sheet2")

    Delay 3000 ' Wait until Excel starts

    objDriverSheet.cells(2,3) = 45

    End Sub



    But in our application we dont deal with already existing excels in the system in some path.

    Instead we create an excel by populating it with data from the database when user tries to open the a document by clicking in the application.

    So i am trying to find out if it is possible to attach an already opened excel to this code and try to read\write data from that excel using the code.

    Please let me know if this can be done.



    Query2:

    Also we work with named ranges in excel. But i am not able to modify
    this code to work with either a named range or with range of data like A1:A10. So
    please send me code which would be able to read and write data from
    named ranges and range of data.



    We are searching for an appropriate testing tool for our application.

    If Test Complete satisfies our requirements, we would buy some licenses.

    Please help me with my queries.



    There is one more which i raised - Title : cant find the item in Object Browser. Please respond to that as well.



    Thanks,

    Laxmi
  • syedsafvan's avatar
    syedsafvan
    Contributor
    15 years ago
    Laxmi,



    I believe that you would have to use SQL integrated with COM some way in your script to accomplish what you need. I haven't tried that before so I would not be able to give you the code example. See SQL section of TC for help.




    And about working with Ranges on excel is possible using COM. You need to define a range object.

    Set objMyRange = objMyExcel.Range("A1")





    Suggest you to go through an interesting knol - http://knol.google.com/k/excel-vbscript-automation#




    Thank you

    Syed














  • Laxmi_1's avatar
    Laxmi_1
    Occasional Contributor
    15 years ago
    Hi,



    The code,

    Set objRange2 = objDriverSheet.Range(”B1    ″)

    just fetches data from the cell B1, i.e. a single cell.

    However if we try to modify the same into something like



    dim arrayA(10)

    Set arrayA(10) = objDriverSheet.Range(”B1:B10") ' reading a range of data

                            or

    Set arrayA(10) = objDriverSheet.Range(”RangeName1″) ' reading a named range and which has 10 cells

    Log.Message arrayA(0)



    The script runs successfully but always returns 0.



    Please let me know the exact code to read these ranges.



    Also please let me know a way to read from excel documents in our application as i have specified in my earlier post.



    Thanks,

    Laxmi
  • YMinaev's avatar
    YMinaev
    Staff
    15 years ago
    Hi Laxmi,



    In your script, you assign the objDriverSheet.Range("B1:B10") object to the eleventh element of the arrayA array, while its first element remains uninitialized.



    You can obtain the range cell's values in the following way:

    ...

    Dim c

    For Each c in objDriverSheet.Range("B1:B10")

    Log.Message c.Value

    Next

    ...



    You can find additional information in Excel's built-in VBA reference.



    >

    So i am trying to find out if it is possible to attach an already opened excel to this code and try to read\write data from that excel using the code.

    <

    You can use the OleObject property to do this (see the "OleObject Property" help topic).