Importing Value from Excel instead of a formula

[Francois Rousseau]

Hi folks,

 

The data I use for my scripts are stored in Excel workbook that we import in QTP. My problem is, I need to have references to other Excel file in my formulas to get the correct data for my scripts.

 

Is there a way to import the value of a cell instead of the formula behind it?

 

Would there be a way to do this an other way to get the values in my QTP datatable?

 

Thank you!

[Sedhupathi Gnanasundaram]

Hi Francois,

For your scenario no need import i guess, directly you can create the ref for Excel file then u can get the appropriate alue from excel sheet.

Ex: Using Set XL = CreateObject("Excel.Application")

Regards

Sedhu

--
--
You received this message because you are subscribed to the Google
"QTP - HP Quick Test Professional - Automated Software Testing"
group.
To post to this group, send email to Mercu...@googlegroups.com
To unsubscribe from this group, send email to
MercuryQTP+...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/MercuryQTP?hl=en
 
---
You received this message because you are subscribed to the Google Groups "QTP - HP Quick Test Professional - Automated Software Testing" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mercuryqtp+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Nishant Pawar]

Hi Francois,

Here is the code for your requirement.

Set oExcel= CreateObject("Excel.Application")

Set oWorkbook = oExcel.workbooks.open("Path of Excel File")

Set oWorksheet = oWorkbook.Worksheets("Sheet Name")

Variable name = oWorksheet.cells(row number,column number)

Let me know in case you want more clarification.

Thanks,

Nishant

[Nik]

Francois, I think I understand your issue. You need to import the valuees of excel instead of the formulas? Unfortunately, I do not think that there is an easy solution. I know i tried it and was not able to do so directly. But in the qtp world, there is always a solution. May I ask why you want to import this data? What domyou intend to use it for? I would recommend using an excel object to do a paste special of only values after you copy them. So copy all cells, paste special in a new sheet with only values, then import those values. Bit of a pain, but I havent seen better solutions out there.
www.qtptutorial.net

[Nishant Pawar]

Hi,

I can not understand what problem you guys are facing.

I am also trying to get the value of cell A(1,1) of excel and the value of cell A(1,1) is being calculated by formula "=(B2+C2)"

Per my understanding you want to access the value of cell and not the formula which is being applied in that cell..

When I access the cell A(1,1) using vbscript then it is returning me the value and not the formula which is absolutely fine I guess.

Please find below screenshot.

Please let me know if I am unable to get your question.

Thanks,

Nishant

On Fri, Jan 24, 2014 at 8:02 AM, Nik <nadv...@gmail.com> wrote:

Francois, I think I understand your issue. You need to import the valuees of excel instead of the formulas? Unfortunately, I do not think that there is an easy solution. I know i tried it and was not able to do so directly. But in the qtp world, there is always a solution. May I ask why you want to import this data? What domyou intend to use it for? I would recommend using an excel object to do a paste special of only values after you copy them. So copy all cells, paste special in a new sheet with only values, then import those values. Bit of a pain, but I havent seen better solutions out there.
www.qtptutorial.net

--
--
You received this message because you are subscribed to the Google
"QTP - HP Quick Test Professional - Automated Software Testing"
group.
To post to this group, send email to Mercu...@googlegroups.com
To unsubscribe from this group, send email to
MercuryQTP+...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/MercuryQTP?hl=en

---

You received this message because you are subscribed to a topic in the Google Groups "QTP - HP Quick Test Professional - Automated Software Testing" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/mercuryqtp/pL_6oyufO5E/unsubscribe.
To unsubscribe from this group and all its topics, send an email to mercuryqtp+...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

--
Regards,

Nishant Pawar.

[QTP Tutorialnet]

Nishant, try datatable.import for that spreadsheet. See what values you get in the data table...
Let me know if you are able to see values or formulas...

[Ravi kiran]

Please find the below to retrive data and formula using vba

Rem Create file system object
Set oExcel = CreateObject("Excel.application")
Rem add workbook
oExcel.Workbooks.add
Rem oExcel.Application.Visible = true
Set oSheet = oExcel.ActiveWorkbook.Worksheets("Sheet1")
Rem Add values and formula
oSheet.Cells(1,1).value="1"
oSheet.Cells(2,1).value="1"
oSheet.Cells(3,1).formula="=SUM(A1,A2)"
Rem save excel in sepcific folder path
oExcel.ActiveWorkbook.SaveAs "Folder path"
Rem retrive formula data
msgbox oSheet.Cells(3,1).formula
oExcel.quit

 

 

You received this message because you are subscribed to the Google Groups "QTP - HP Quick Test Professional - Automated Software Testing" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mercuryqtp+...@googlegroups.com.