Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks collection is empty even though the some of the document is open
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks collection is empty even though the some of the document is open
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks collection is empty even though the some of the document is open
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks collection is empty even though the some of the docu
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks collection is empty even though the some of the document is open
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks collection is empty even though the some of the document is open
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" wrote in message oups.com... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks collection is empty even though the some of the document is open
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks collection is empty even though the some of the document is open
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" wrote in message oups.com... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks collection is empty even though the some of the document is open
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; } } |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks collection is empty even though the some of the document is open
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" wrote in message oups.com... 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; } } |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks collection is empty even though the some of the document is open
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks collection is empty even though the some of the document is open
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; } |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbooks collection is empty even though the some of the document is open
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" wrote in message oups.com... 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; } |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I forgot my password to open an excel document, how do I open it? | Excel Discussion (Misc queries) | |||
When I open Excel, workbooks open automatically. How can I stop t | Excel Discussion (Misc queries) | |||
my excel document won't open unless i go file, then open | Excel Discussion (Misc queries) | |||
Excel 2003 Workbooks.Open with CorruptLoad=xlRepairFile fails on Excel 5.0/95 file due to Chart, with Error 1004 Method 'Open' of object 'Workbooks' failed | Excel Programming | |||
Workbooks.Open / .Open Text - How do you stop the .xls addition? | Excel Programming |