cancel
Showing results for 
Search instead for 
Did you mean: 

Read and Write Data into Excel

Occasional Contributor

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 6
Staff

RE: Read and Write Data into Excel

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.



------
Yuri
TestComplete Customer Care Engineer

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others.↓↓↓↓↓
Occasional Contributor

RE: Read and Write Data into Excel

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
Contributor

RE: Read and Write Data into Excel

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














Syed Ahmed Safvan

Occasional Contributor

RE: Read and Write Data into Excel

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

Occasional Contributor

RE: Read and Write Data into Excel

Jared,

can you please help me with this.
Staff

RE: Read and Write Data into Excel

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).



------
Yuri
TestComplete Customer Care Engineer

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others.↓↓↓↓↓
New Here?
Join us and watch the welcome video:
API Testing Mistake #2
APITestingMistake#2
Top Kudoed Authors