Workbooks collection is empty even though the some of the document is open

[[email protected]]

Hi,
I have a requirement to check if an excel document is open or closed
from another C# application. For this, I am using Automation to get the
instanc of the Excel application and iterating through the WorkBooks
collection.

The problem is sometimes the Workbooks collection returns empty
collection and so I could determine if the document is open or not.
Some time it works correctly with the same environment.

Here is my code snippet I am using.

if(excelApp == null)
{
try
{
excelApp =
(Excel.Application)Marshal.GetActiveObject("Excel.Application");
}
catch(System.Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex);
}
if (excelApp == null) return null;
}

foreach(Excel.Workbook book in excelApp.Workbooks)
{
try
{
if (book.FullName.ToLower() == m_strFileName.ToLower())
{
return book;
}
}
catch(System.Exception ex)
{
//You will get the exception here if the application is busy/
RMLogger.Error(ex);
return null;
}
}

Any help is highly appreciated.

Thanks
Thanga

[DM Unseen]

Thanga,

you wont be able to determine which Excel instance you are looking
at!(when there is more than 1 excel instance active).

Mysheet = GetObject(,"C:\myfile.xls") will connect to the first excel
instance that has this file open, or else it will open the file.

Dm Unseen

[Thanga]

Hi,
I just now saw that there are two instance of the excel is running when
this problem occurs.

Do you have any idea how to workaround this condition? May be by
getting all the instances?. I know getobject will return the first
instance it returns.

[Vic Eldridge]

> Do you have any idea how to workaround this condition? May be by
> getting all the instances?. I know getobject will return the first
> instance it returns.

I'm not sure if C#'s GetActiveObject and VB's GetObject are the same beast,
but it certainly sounds like they are (warts and all !). If so, the
following thread discusses a way to return a specific instance.

http://groups.google.com.au/group/mi...db4ab107e4a7e/


Regards,
Vic Eldridge

[DM Unseen]

There is in normal VB(A) to my knowledge no easy way of getting all
instances(And I looked really hard). It *is* possible using the ROT and
doing some hardcore API stuff, but this is really difficult.

What I did is create an out of process single instance ActiveX, which
is connected to by the XL file and my other VB app(in my case an Access
db). This way I can pass Object handles, so I always have the correct
Excel instance, the one that registered it's handle through my ActiveX.

Note that in .NET (i.e. c#) you might have some extra options, but that
solution would still need to look and the ROT(Running Object Table).

DM Unseen

[Peter T]

As you say not easy to grab all unknown Excel instance's but there are ways.
My method highly simplified:

Assumes that no unsaved BookX in any number of instances has the same suffix
X
Enumerate XLMAIN windows looking if any child windows in that instance has a
BookX, if so grab it with GetObject and ref it's xl parent

If it doesn't, bring the XLMAIN to the front (API) then with (createobject
invisible) Word use DDE to add a new workbook named BookX. Grab that, hide
the new BookX to save time if need to do again.

Normally possibly to create withevents xlApp class's of each instance from
which one can do whatever (read or manipulate).

Simple in theory but, as I mentioned in the link referred to by Vic
(adjacent post) convoluted and complicated!

Regards,
Peter T


"DM Unseen" <[email protected]> wrote in message
news:[email protected]...
> There is in normal VB(A) to my knowledge no easy way of getting all
> instances(And I looked really hard). It *is* possible using the ROT and
> doing some hardcore API stuff, but this is really difficult.
>
> What I did is create an out of process single instance ActiveX, which
> is connected to by the XL file and my other VB app(in my case an Access
> db). This way I can pass Object handles, so I always have the correct
> Excel instance, the one that registered it's handle through my ActiveX.
>
> Note that in .NET (i.e. c#) you might have some extra options, but that
> solution would still need to look and the ROT(Running Object Table).
>
> DM Unseen
>

[Thanga]

Hi All,
Thanks for your replies. I have solved this problem using ROT and
working pretty good without any issue. Access ROT is also not much
difficult if you know how to us P/Invoke.

Thanks
Thanga

[Peter T]

Hi Thanga,

I'm intrigued. Although it's a while since I looked into this it was my
understanding that only the first instance of an application gets registered
into the ROT, though all loaded files in a given instance do get registered
(eventually). This means (?) one still needs to know the name of a file
that's uniquely loaded in a given instance, then can use GetObject to attach
to that object (fullname) and it's parent application. Along the lines of
the second step in what I described previously.

Are you saying with your method you can attach to all instances without
knowing the name of at least one loaded object (file) in each. Or can you
return a list of files loaded in each instance then use GetObject. Would
you care to post your method with P/Invoke.

Regards,
Peter T


"Thanga" <[email protected]> wrote in message
news:[email protected]...
> Hi All,
> Thanks for your replies. I have solved this problem using ROT and
> working pretty good without any issue. Access ROT is also not much
> difficult if you know how to us P/Invoke.
>
> Thanks
> Thanga
>

[Thanga]

Hi,
My requirement was to just to know if the file is opened in the
application or not. I simply check the document entry in the ROT.

You can get hold of all instances of the Excel Application and iterate
through the documents opened in each of them. Here is the code I used
to get the instance of the document object. Similar way, you can get
the instance of the application you need.
protected object GetObjectFromROT( string strDocumentName )
{
Hashtable runningIDEInstances = new Hashtable();
Hashtable runningObjects = GetRunningObjectTable();

IDictionaryEnumerator rotEnumerator =
runningObjects.GetEnumerator();
while ( rotEnumerator.MoveNext() )
{
string candidateName = (string) rotEnumerator.Key;

if (candidateName.ToLower() == strDocumentName.ToLower())
{
return rotEnumerator.Value as object;
}
}

return null;
}

[STAThread]
protected Hashtable GetRunningObjectTable()
{
Hashtable result = new Hashtable();

int numFetched;
UCOMIRunningObjectTable runningObjectTable;
UCOMIEnumMoniker monikerEnumerator;
UCOMIMoniker[] monikers = new UCOMIMoniker[1];

GetRunningObjectTable(0, out runningObjectTable);
runningObjectTable.EnumRunning(out monikerEnumerator);
monikerEnumerator.Reset();

while (monikerEnumerator.Next(1, monikers, out numFetched) == 0)
{
UCOMIBindCtx ctx;
CreateBindCtx(0, out ctx);

string runningObjectName;
monikers[0].GetDisplayName(ctx, null, out runningObjectName);

object runningObjectVal;
runningObjectTable.GetObject( monikers[0], out runningObjectVal);

result[ runningObjectName ] = runningObjectVal;
}

return result;
}
}

[Peter T]

Thanks Thanga for sharing this.

My knowledge of C* is minimal, but if you already know "strDocumentName"
(fullname presumably) is there not a simpler GetObject equivalent,
CoGetObject in C++, not sure about C# .

I've tried to decipher your code, unsuccessfully, to the extent I don't see
how it could be adapted to attach to multiple instances without knowing the
fullname of a file uniquely loaded in each. Though I do appreciate for your
purposes you are only looking if a particular file.

Regards,
Peter T


"Thanga" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> My requirement was to just to know if the file is opened in the
> application or not. I simply check the document entry in the ROT.
>
> You can get hold of all instances of the Excel Application and iterate
> through the documents opened in each of them. Here is the code I used
> to get the instance of the document object. Similar way, you can get
> the instance of the application you need.
> protected object GetObjectFromROT( string strDocumentName )
> {
> Hashtable runningIDEInstances = new Hashtable();
> Hashtable runningObjects = GetRunningObjectTable();
>
> IDictionaryEnumerator rotEnumerator =
> runningObjects.GetEnumerator();
> while ( rotEnumerator.MoveNext() )
> {
> string candidateName = (string) rotEnumerator.Key;
>
> if (candidateName.ToLower() == strDocumentName.ToLower())
> {
> return rotEnumerator.Value as object;
> }
> }
>
> return null;
> }
>
> [STAThread]
> protected Hashtable GetRunningObjectTable()
> {
> Hashtable result = new Hashtable();
>
> int numFetched;
> UCOMIRunningObjectTable runningObjectTable;
> UCOMIEnumMoniker monikerEnumerator;
> UCOMIMoniker[] monikers = new UCOMIMoniker[1];
>
> GetRunningObjectTable(0, out runningObjectTable);
> runningObjectTable.EnumRunning(out monikerEnumerator);
> monikerEnumerator.Reset();
>
> while (monikerEnumerator.Next(1, monikers, out numFetched) == 0)
> {
> UCOMIBindCtx ctx;
> CreateBindCtx(0, out ctx);
>
> string runningObjectName;
> monikers[0].GetDisplayName(ctx, null, out runningObjectName);
>
> object runningObjectVal;
> runningObjectTable.GetObject( monikers[0], out runningObjectVal);
>
> result[ runningObjectName ] = runningObjectVal;
> }
>
> return result;
> }
> }
>

[Thanga]

Hi Peter,
Sorry that you couldn't make it work still. Basically I am a C++ COM
Programmer and recently I started working in C#. Why don't you post
your code snippet and see if I can help you with it.

-Thanga

[Thanga]

Peter,
I have modified my code snippet to iterate through all the instance and
get the work books loaded in each instance. You can view the Excel
registration in the ROT using "IROTVIEWER" which comes with the Visual
studio.

protected void GetObjectFromROT()
{
Hashtable runningIDEInstances = new Hashtable();
Hashtable runningObjects = GetRunningObjectTable();

IDictionaryEnumerator rotEnumerator =
runningObjects.GetEnumerator();
while ( rotEnumerator.MoveNext() )
{
string candidateName = (string) rotEnumerator.Key;
if
(candidateName.ToLower().startswith("!{0024500-0000-0000-C000-000000000046}"
)
{
//Get the Excel Application Handle.
//Iterate through the list of Worksbooks.
//Print the file name.
}
}


return null;
}

[Peter T]

Hi Thanga,

Thanks for coming back on this, I'm even more intrigued! One side of my
brain is telling me it can't work, ie end up with a ref to each multiple
instance, yet I'm beginning to see how it might. I have Visual Studio but
not yet been able to compile into something useable. My lack of C# is
letting me down, need to brush up which I fear will take a while.

The subject of referencing multiple unknown instances often crops up (not
directly your question) so it'd be handy if a DLL can be created that can be
used in VB/A to return, say, an array of object ref's to all Excel
instances.

My VB method works reasonably well but not quite well enough to be
distributed at large. As I mentioned before -

- Builds an array of Excel Win handles (XLMAIN) together with all unsaved
files named Book# in each instance (EXCEL7).
- If an instance does not have a Book#, add a new Book# to that instance
with DDE (hide this for future use).
- Use GetObject("Book#").Parent to reference each instance.

If you or anyone is interested to see my VB/VBA I'll forward a demo workbook
(contact below). But your C# method looks potentially much better!

Regards,
Peter T
pmbthornton gmail com

PS, in your adjacent you said

> Why don't you post
> your code snippet and see if I can help you with it.

I don't have a snippet, only what you posted previously, but thanks


"Thanga" <[email protected]> wrote in message
news:[email protected]...
> Peter,
> I have modified my code snippet to iterate through all the instance and
> get the work books loaded in each instance. You can view the Excel
> registration in the ROT using "IROTVIEWER" which comes with the Visual
> studio.
>
> protected void GetObjectFromROT()
> {
> Hashtable runningIDEInstances = new Hashtable();
> Hashtable runningObjects = GetRunningObjectTable();
>
> IDictionaryEnumerator rotEnumerator =
> runningObjects.GetEnumerator();
> while ( rotEnumerator.MoveNext() )
> {
> string candidateName = (string) rotEnumerator.Key;
> if
>
(candidateName.ToLower().startswith("!{0024500-0000-0000-C000-000000000046}"
> )
> {
> //Get the Excel Application Handle.
> //Iterate through the list of Worksbooks.
> //Print the file name.
> }
> }
>
>
> return null;
> }
>