Excel codes are not working

Best practices, code snippets for common functionality, examples, and guidelines.
theraviz
Posts: 111
Joined: Sun Apr 14, 2019 9:46 am

Excel codes are not working

Post by theraviz » Tue Jun 02, 2020 8:46 am

Hello,

I am trying to write some output values in an excel and for this I am using Excel Interop. But it is always throwing error in Value2.

The possible work around I did is as below but of no luck. Please support.

Tried replacing Value2 with Value - No luck
.Net framework used is 4.6.2 - No Luck


Error message is as below
'object' does not contain a definition for 'Value2' and no extension method 'Value2' accepting a first argument of type 'object' could be found (are you missing a using directive or an assembly reference?) (CS1061) - C:\Users\sd242\Documents\Ranorex\RanorexStudio Projects\Delet\Delet\Recording1.UserCode.cs:155,44

Assembly references being used.

using Microsoft.CSharp;
using xlObject = Microsoft.Office.Interop.Excel;

Code.


for (int i = 2; i < row; i++)
{

if (xlRange.Cells[i, 4].Value == ReferenceNumber)
{
row = i;
_TestFlag = true;
break;
}
}

if (_TestFlag != true)
{

xlRange.Cells[row, 2].Value2 = SerialNumber;
}


Please note the above code is working perfectly in Visual Studio

User avatar
odklizec
Ranorex Guru
Ranorex Guru
Posts: 7470
Joined: Mon Aug 13, 2012 9:54 am
Location: Zilina, Slovakia

Re: Excel codes are not working

Post by odklizec » Tue Jun 02, 2020 9:47 am

Hi,

Could you please post entire code? Also, try this...

Code: Select all

if ((xlRange.Cells[i, 4] as Excel.Range).Value  == ReferenceNumber)
Eventually, this:

Code: Select all

Excel.Range xlsRange = (Excel.Range)xlRange.Cells[[i, 4]];
if (xlsRange.Value  == ReferenceNumber)
Pavel Kudrys
Ranorex explorer at Descartes Systems

Please add these details to your questions:
  • Ranorex Snapshot. Learn how to create one >here<
  • Ranorex xPath of problematic element(s)
  • Ranorex version
  • OS version
  • HW configuration

theraviz
Posts: 111
Joined: Sun Apr 14, 2019 9:46 am

Re: Excel codes are not working

Post by theraviz » Tue Jun 02, 2020 7:32 pm

Hi,

Since my previous full code is huge and having some confidential data, I am sharing another Method which is using the same concept as before. Error is at the highlighted code (Value) - same as before.


using Excel = Microsoft.Office.Interop.Excel;

/*[UserCodeMethod]

public static void WriteToExcel(string Account, string EValue1)

{

Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open("c:\\myexcel.xlsx");
Excel.Worksheet xlWorksheet = (Excel.Worksheet)xlWorkbook.Sheets.get_Item(1);
Excel.Range xlRange = xlWorksheet.UsedRange;

int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
int row = xlWorksheet.UsedRange.Rows.Count;

for (int i = 1; i <= rowCount; i++)
{
if(xlRange.Cells[i, 2].Value.ToString() == Account)
{
xlRange.Cells[i, colCount + 1].Value = EValue1;
}
}
xlWorkbook.Save();
xlWorkbook.Close();
xlApp.Quit(); }

User avatar
odklizec
Ranorex Guru
Ranorex Guru
Posts: 7470
Joined: Mon Aug 13, 2012 9:54 am
Location: Zilina, Slovakia

Re: Excel codes are not working

Post by odklizec » Tue Jun 02, 2020 7:45 pm

Hi,

Well, I think it would be best if you simply create a “dummy” Ranorex project (with included excel file), which simulates your problem. I’m sorry, but it’s hard to tell what’s wrong by looking at the method you posted. I don’t see anything particularly wrong, but it may be because of my inexperience with interop methods ;) Also, have you tried to replace your definition of Excel.Range with the code(s) I suggested? Btw, there are several posts regarding storing data to excel. Check for example this one and eventually adapt your code according this sample...
https://www.ranorex.com/forum/write-dat ... tml#p44386
Pavel Kudrys
Ranorex explorer at Descartes Systems

Please add these details to your questions:
  • Ranorex Snapshot. Learn how to create one >here<
  • Ranorex xPath of problematic element(s)
  • Ranorex version
  • OS version
  • HW configuration

theraviz
Posts: 111
Joined: Sun Apr 14, 2019 9:46 am

Re: Excel codes are not working

Post by theraviz » Wed Jun 03, 2020 7:28 am

odklizec wrote:
Tue Jun 02, 2020 7:45 pm
Hi,

Well, I think it would be best if you simply create a “dummy” Ranorex project (with included excel file), which simulates your problem. I’m sorry, but it’s hard to tell what’s wrong by looking at the method you posted. I don’t see anything particularly wrong, but it may be because of my inexperience with interop methods ;) Also, have you tried to replace your definition of Excel.Range with the code(s) I suggested? Btw, there are several posts regarding storing data to excel. Check for example this one and eventually adapt your code according this sample...
https://www.ranorex.com/forum/write-dat ... tml#p44386
Hello,

Im extremely sorry! Your code is working fine and the only simple thing which was missing was the casting. After including type casting, it is working fine. Thanks a lot :)

User avatar
odklizec
Ranorex Guru
Ranorex Guru
Posts: 7470
Joined: Mon Aug 13, 2012 9:54 am
Location: Zilina, Slovakia

Re: Excel codes are not working

Post by odklizec » Wed Jun 03, 2020 7:36 am

Hi,

Nice to hear it works now ;)
Pavel Kudrys
Ranorex explorer at Descartes Systems

Please add these details to your questions:
  • Ranorex Snapshot. Learn how to create one >here<
  • Ranorex xPath of problematic element(s)
  • Ranorex version
  • OS version
  • HW configuration