Reading cells of Already opened excel file

[Marturi]

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

[odklizec]

Hi,

Are you running Ranorex "administrator" mode?

[Marturi]

yes .Ranorex opened with Run as administrator.

[McTurtle]

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]

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]

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?

[Marturi]

Hi,
I have ms office 2016 licensed in my pc

[McTurtle]

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

[Aracknid]

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]

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