Reading cells of Already opened excel file

Class library usage, coding and language questions.
Marturi
Posts: 4
Joined: Mon Mar 13, 2017 1:51 pm

Reading cells of Already opened excel file

Post by Marturi » Mon Mar 13, 2017 2:08 pm

Hi ,

My Application generates an excel and it will get open, after which I need to read the cells of it.

we would not be known the path of the excel file.So I have to get the instance of already opened excel.

I have the tried the below code in Visual studio and working fine. However if same code is used it is showing run time exception in Ranorex.pls let me know the issue is ?

Using Microsoft.Office.Interop;
Using Excel =Microsoft.Office.Interop.Excel;

method()
{
Excel.Application appApplication ;
appApplication =System.Runtime.Interopservices.Marshal.GetActiveObject("Excel.Application") as Excel.Application;
appApplication.DisplayAlerts =false;
appApplication.EnableEvents =false;
if(appApplication !=null)
{
Console.WriteLine(appApplication.ActiveWorkbook.Name);
}

Exception : Operation Unavailable(Exception from HRESULT :0x800401E3(MK_E_UNAVAILABLE)

Ranorex version : 6.2.1
Dll references : Microsoft.Office.Interop.Excel

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

Re: Reading cells of Already opened excel file

Post by odklizec » Tue Mar 14, 2017 8:31 am

Hi,

Are you running Ranorex "administrator" mode?
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

Marturi
Posts: 4
Joined: Mon Mar 13, 2017 1:51 pm

Re: Reading cells of Already opened excel file

Post by Marturi » Tue Mar 14, 2017 9:55 am

yes .Ranorex opened with Run as administrator.

McTurtle
Posts: 191
Joined: Thu Feb 23, 2017 10:37 am
Location: Benedikt, Slovenia

Re: Reading cells of Already opened excel file

Post by McTurtle » Fri Mar 17, 2017 3:26 pm

Hello Marturi,

Try the following code:
using System;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;
using System.Drawing;
using System.Threading;
using WinForms = System.Windows.Forms;

using Ranorex;
using Ranorex.Core;
using Ranorex.Core.Testing;
using Microsoft.Office.Interop.Excel;


namespace test3
{
    
    [TestModule("703ACBA5-006C-49A5-B57B-97EBA2866E68", ModuleType.UserCode, 1)]
    public class UserCodeModule1 : ITestModule
    {
        public UserCodeModule1(){}

        void ITestModule.Run()
        {        
            Application instance = null;

            try
            {
                instance = (Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
            }
            catch (System.Runtime.InteropServices.COMException ex)
            {
                Console.Write(String.Format(@"No Excel instance found: {0}", ex));
            }

            instance.Visible = true;
         
            var books = instance.Workbooks;

            foreach (Workbook book in books)
            {
                _Worksheet workSheet = book.ActiveSheet as _Worksheet;
                Console.Write(workSheet.Name);
            }
        }
    }
}
Don't forget to add the reference for Microsoft.Office.Interop.Excel.
Also make sure that Ranorex is not started as administrator.

Please let me know if this helped.

Sincerely,
McTurtle

Marturi
Posts: 4
Joined: Mon Mar 13, 2017 1:51 pm

Re: Reading cells of Already opened excel file

Post by Marturi » Tue Mar 21, 2017 1:00 pm

Hi ,

Still getting exception.

No Excel instance found : System.Runtime.InteropServices.COMException (0x800401F3): Invalid class string (Exception from HRESULT: 0x800401F3 (CO_E_CLASSSTRING))
at System.Runtime.InteropServices.Marshal.CLSIDFromProgID(String progId, Guid& clsid)
at System.Runtime.InteropServices.Marshal.GetActiveObject(String progID)
at ReadingExcel.UserCodeModule1.Ranorex.Core.Testing.ITestModule.Run() in e:\RanorexPrograms\ReadingExcel\ReadingExcel\UserCodeModule1.cs:line 52

krstcs
Ranorex Guru
Posts: 2683
Joined: Tue Feb 07, 2012 4:14 pm
Location: Austin, Texas, USA

Re: Reading cells of Already opened excel file

Post by krstcs » Tue Mar 21, 2017 3:33 pm

Do you have the FULL VERSION of Excel installed on the test system? It has to be a full Excel install with a valid license.

Also, you know you can use a CSV file instead and not have to worry about having Excel installed?
Shortcuts usually aren't...

Marturi
Posts: 4
Joined: Mon Mar 13, 2017 1:51 pm

Re: Reading cells of Already opened excel file

Post by Marturi » Tue Mar 21, 2017 3:45 pm

Hi,
I have ms office 2016 licensed in my pc

McTurtle
Posts: 191
Joined: Thu Feb 23, 2017 10:37 am
Location: Benedikt, Slovenia

Re: Reading cells of Already opened excel file

Post by McTurtle » Thu Mar 23, 2017 8:41 pm

Hello Marturi,

I have now changed the code a bit, excluding any Ranorex libraries and running the exe in from cmd. When I open Excel it works, when I close Excel I get an error which looks very much like your error.

Can you test the following code? Just create a new console application in Visual Studio:
using System;
using Microsoft.Office.Interop.Excel;

namespace Get_Process_ID
{
    class Program
    {
        static void Main(string[] args)
        {
            get_process_ID1.Program.test();
        }
    }
}    
namespace get_process_ID1
    { 
    class Program
    { 
        public static void test()
        {
                Application instance = null;

                try
                {
                    instance = (Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
                }
                catch (System.Runtime.InteropServices.COMException ex)
                {
                    Console.Write(String.Format(@"No Excel instance found: {0}", ex));
                }

                instance.Visible = true;

                var books = instance.Workbooks;

                foreach (Workbook book in books)
                {
                    _Worksheet workSheet = book.ActiveSheet as _Worksheet;
                    Console.Write(workSheet.Name);
                }
        }
    }
}
This way we can make sure that Ranorex is not the problem and we can easier identify the right issue.

Regards,
McTurtle

User avatar
Aracknid
Posts: 342
Joined: Tue Aug 10, 2010 3:23 pm
Location: Toronto, Ontario, Canada

Re: Reading cells of Already opened excel file

Post by Aracknid » Wed Apr 05, 2017 2:28 pm

Hi,

I've gotten this error with Excel, Outlook, and Project for my tests that interact with them. When I did some research, I found that it had something to do with a COM object needing to register with the Windows Registration Object Table (ROT) before it can be available as a COM object. Sometimes to speed up loading it doesn't register. I found that I could force it to register by losing focus on the App. This was why the issue never occurred for me while I was walking through my code in VisualStudio...

What I did to solve the problem was get the object in a try catch (as already suggested), and if it was caught, I'd force it to switch focus to another window (in my case the console window from running the script), wait 60 seconds, and then try to get the object again. This always resolved the3 issue for me.

Aracknid.

McTurtle
Posts: 191
Joined: Thu Feb 23, 2017 10:37 am
Location: Benedikt, Slovenia

Re: Reading cells of Already opened excel file

Post by McTurtle » Thu Apr 06, 2017 9:52 am

Hello Aracknid,

The fact that you have been able to figure this out is great!

And very nice from you to come back to us and let us know the solution so other people may find this here if they encounter a similar problem.

Kind regards,
McTurtle