Sergei_SG

Hi all,

Have been trauling the net for the answer but it seems it works for everyone but me...

Scenario:

1) open a few Excel workbooks

2) start my C# app that opens a form "MainForm" with a list box "ListOfWB"

3) what I needed is to populate the list box with the names of all open Excel workbooks

I've done it tone of times in VBA, but the first time in C# (2005). Can anyone help to point out the snag My current code below:

Code Snippet

private void MainForm_Load(object sender, EventArgs e)

{

Microsoft.Office.Interop.Excel.ApplicationClass XL = new Microsoft.Office.Interop.Excel.ApplicationClass();

foreach (Workbook _wb in XL.Workbooks)

{

ListOfWB.Items.Add(_wb.Name.ToString());

}

}

It looks like the "XL" application object does not contain any references to the workbooks that were open prior to the Form_Load event. However, if I keep the form running, open another couple of workbooks and re-populate the listBox, then it would reflect those new ones. I'm lost

Sergei



Re: Visual Studio Tools for Office Iterating through Excel Workbooks collection does not pick-up all open workbooks

JocularJoe

I suspect you're creating a new instance of Excel which doesn't have any workbooks open.

You could try using Marshal.GetActiveObject("Excel.Application") to connect to a running instance of Excel.





Re: Visual Studio Tools for Office Iterating through Excel Workbooks collection does not pick-up all open workbooks

Ji Zhou ĘC MSFT

Hi Sergei,

As Joe mentioned, you were just getting a new instance of Excel.Application. So opened workbooks would not apper in the property Workbooks.

Try these codes which work fine on my system:

Code Snippet

Microsoft.Office.Interop.Excel.Application XL = null;

private void Form1_Load(object sender, EventArgs e)
{
XL = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
foreach (Excel.Workbook _wb in XL.Workbooks)
{
this.listBox1.Items.Add(_wb.Name.ToString());
}
}

And thanks Joe to give clue :-)

Thanks

Ji






Re: Visual Studio Tools for Office Iterating through Excel Workbooks collection does not pick-up all open workbooks

Sergei_SG

Joe, Ji,

Yep, this worked. It makes sense now that I remember the good, old "GetObject" from VB6.

Cheers,

Sergei_SG