Assign excel workbook to local variable when the excel is already open

Question

What I am trying to do is create a Windows App that opens some excel workbooks, does some calculations and the saves and closes the excel files.

I cannot seem to be able to focus on one of them without having to open them, like:

oBook1 = oBooks.Open("C:\\Reports\Excel1.xlsx");
oBook2 = oBooks.Open("C:\\Reports\Excel2.xlsx");

What I need to be able to do is to assign Excel1.xlsx to oBook1 and Excel2.xlsx to oBook2 when the excel files are already open.

Is there a away to do this? I have tried several ways found online but nothing works.

Until now I managed to do the assignment only by opening them one by one like the example above.

Thank you. Danut

Edit 1:

I am trying to get a list of excel workbooks open and then to select the one i need from there. My code is:

        Microsoft.Office.Interop.Excel.Application oExcelApp;

             oExcelApp = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");

             foreach (Microsoft.Office.Interop.Excel.Workbook WB in oExcelApp.Workbooks)
             {
                 MessageBox.Show(WB.FullName);
             }

             oExcelApp = null;

When I try to assign oExcellApp I get the folowing error: "Cannot implicitly convert type 'object' to 'Microsoft.Office.Interop.Excel.Application'. An explicit conversion exists (are you missing a cast?)"

What am i doing wrong?

http://blogs.technet.com/b/the_microsoft_excel_support_team_blog/archive/2012/05/14/the-definitive-locked-file-post.aspx — tdbeckett, Jul 21 '14 at 14:44
I tried what is posted here http://stackoverflow.com/questions/6686886/how-to-access-an-already-opened-excel-file-in-c but it does not work either. — Danut, Aug 20 '14 at 06:27

score 1 · Answer 1 · answered Jul 21 '14 at 14:43

1

You can iterate the workbooks collection and test to see if the one you want is already there; if not, open it. Here's the VBA version of this:

Sub Test()
    Dim oWb As Workbook
    Set oWb = GetWorkbook("name_of_file.xlsx")
    If oWb Is Nothing Then
        MsgBox "File is not open; you'll have to open it yourself."
    Else
        MsgBox "File is already open and you have a reference to it in oWb."
    End If
End Sub

Function GetWorkbook(sName) As Workbook
    Dim oWb As Workbook
    For Each oWb In Application.Workbooks
        'Debug.Print oWb.Name
        'Debug.Print oWb.FullName ' You could also check the full path
        If sName = oWb.Name Then
            Set GetWorkbook = oWb
            Exit Function
        End If
    Next
End Function

answered Jul 21 '14 at 14:43

Steve Rindsberg

14,442
1
29
34

thank you for the answer but i am having a hard time moving this to C#. – Danut Jul 21 '14 at 15:13
Can't help you with that, I'm afraid. – Steve Rindsberg Jul 21 '14 at 19:41
No problem, thank you. I will wait for another answer. – Danut Jul 22 '14 at 11:44
Why is the answer in VB? this is a C# question. – tdbeckett Jul 28 '14 at 14:03
Automating Office apps is a two-part problem: you have to learn the app's object model and then figure out how to use whatever language you like to use the object model. I provided a simple object model example in the language I'm familiar with. If it helps you, you're free to adapt it to C#. If you're unable to do that, you're free to ignore my suggestion. – Steve Rindsberg Jul 28 '14 at 14:36

mattjr747 · Answer 2 · 2022-10-05T21:01:39.243

Short answer:
You are missing a cast as your error implies. The GetActiveObject function gets an "object" type, not an Excel.Application type. However it can be cast as an Excel.Application type. Try this:

oExcelApp = (Microsoft.Office.Interop.Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");

Long answer:
This is a very old question, but I have recently struggled with this problem and found a solution in C#, so here is my full solution.

First, I setup the using and global variable needed:

    using Microsoft.Office.Interop.Excel;
    using System.Runtime.InteropServices;

    Application excelApp;

Second, I have a function which Initializes the Excel application object as follows:

    public void InitExcelApp()
    {
       try
       {
          excelApp = (Application)Marshal.GetActiveObject("Excel.Application");
       }
       catch(COMException ex)
       {
          excelApp = new Application
          {
             Visible = true;
          };
       }
    }

Third, I created a function to get the open workbook object. This assumes you know the path of the open workbook. In my project, this is a safe assumption.

// This path MUST be windows style. I use Path.GetFullPath(mypath) before passing the path as a string.
private Workbook getOpenWorkbook(string path) 
{
   foreach (Workbook xlWorkbook in excelApp.Workbooks)
   {
      if (xlWorkbook.FullName == path)
      {
         return xlWorkbook;
      }
   }

   return null;
}

Lastly, I just call this function whenever I want to set a workbook object to an open workbook (with known path).

Workbook myWorkbookName = getOpenWorkbook(path);

Assign excel workbook to local variable when the excel is already open

2 Answers2