Hi!
Is there any possibility to trigger Doors from Excel? I think the Doors-Client have to run? Is there any functionality to open a Doors Spec from Excel to read from Doors and write in Excel?
Greets Tobias tobi_mehrl - Fri Dec 05 05:12:31 EST 2014 |
Re: Trigger Doors from Excel? Yes there is. You can use VBA and COM to open a connection to a running DOORS client, or you can use the DOORS Batch Mode to launch a DXL file, which will write results to a normal file and then read that file from VBA... My first DXL project was an "interactive" excel sheet, that would read data from DOORS and fill a special template with the hierarchical data contained in the DOORS module. It was implemented in VBA using COM. So the basic idea here is to open a connection to DOORS, get some values and return them to the application to write them to a sheet. The following sub will for example get the currently selected Heading from DOORS and write the value to the current cell in Excel.
Sub xy()
Dim val As String
Set doorsApp = GetObject("", "DOORS.Application")
doorsApp.runStr "Module m; for m in database do break; if (null m) { oleSetResult ""Error please open a module""; halt; }; current = m; Object o = current; oleSetResult o.""Object Heading"" """"; "
val = doorsApp.result
ActiveCell.Value = val
End Sub
Of course you can optimize speed greatly, by returing several values at once to Excel and Separating them inside VBA. More speed of course to write to a CSV and import into Excel this way. Or to an XML sheet and use it as a data source in Excel. Etc. Hope this gives you the idea. Regards, Mathias |
Re: Trigger Doors from Excel? Hi Mathias,
thx for your answer. One question I have: What do you mean by saying "... and COM"...? What is COM relating to VBA?
In this 8 lines of code, you use the function "GetObject(...)". What does this function? What String I have to write in the first "" of the parameter of GetObject?
I have to read from a specific spec in the Doors-database. How can I define this Pathin VBA which points to this specific spec? |
Re: Trigger Doors from Excel? Mathias Mamsch - Fri Dec 05 06:28:23 EST 2014 Yes there is. You can use VBA and COM to open a connection to a running DOORS client, or you can use the DOORS Batch Mode to launch a DXL file, which will write results to a normal file and then read that file from VBA... My first DXL project was an "interactive" excel sheet, that would read data from DOORS and fill a special template with the hierarchical data contained in the DOORS module. It was implemented in VBA using COM. So the basic idea here is to open a connection to DOORS, get some values and return them to the application to write them to a sheet. The following sub will for example get the currently selected Heading from DOORS and write the value to the current cell in Excel.
Sub xy()
Dim val As String
Set doorsApp = GetObject("", "DOORS.Application")
doorsApp.runStr "Module m; for m in database do break; if (null m) { oleSetResult ""Error please open a module""; halt; }; current = m; Object o = current; oleSetResult o.""Object Heading"" """"; "
val = doorsApp.result
ActiveCell.Value = val
End Sub
Of course you can optimize speed greatly, by returing several values at once to Excel and Separating them inside VBA. More speed of course to write to a CSV and import into Excel this way. Or to an XML sheet and use it as a data source in Excel. Etc. Hope this gives you the idea. Regards, Mathias Hi Mathias Are you really shure that GetObject works. Ok, if you are working from the textbook, it is the correct function, by I only can get a running solution by using CreateObject(). I actually assume that even if doors is running, you have to create an additional com object to get connection to doors. The com object is not initialized in a standard start of doors. Best regards Wolfgang |
Re: Trigger Doors from Excel? tobi_mehrl - Fri Dec 05 06:53:07 EST 2014 Hi Mathias,
thx for your answer. One question I have: What do you mean by saying "... and COM"...? What is COM relating to VBA?
In this 8 lines of code, you use the function "GetObject(...)". What does this function? What String I have to write in the first "" of the parameter of GetObject?
I have to read from a specific spec in the Doors-database. How can I define this Pathin VBA which points to this specific spec? Hi Tobias,
COM: http://en.wikipedia.org/wiki/Component_Object_Model, There is also a DOORS DXL Reference manual's section "Controlling Rational DOORS from applications that support automation" There are various posts in this forum about COM discussion, and more information in the web, see e.g. http://www-01.ibm.com/support/docview.wss?uid=swg21450715. In general, all these posts say "try and error and continue googling" :-) Mike |
Re: Trigger Doors from Excel? Wolfgang Uhr - Fri Dec 05 08:33:39 EST 2014 Hi Mathias Are you really shure that GetObject works. Ok, if you are working from the textbook, it is the correct function, by I only can get a running solution by using CreateObject(). I actually assume that even if doors is running, you have to create an additional com object to get connection to doors. The com object is not initialized in a standard start of doors. Best regards Wolfgang The difference between getObject and createObject is as far as I understand if you want to get a handle to an existing OLE server (the first one in the Running Objects Table) or create a new Server instance and get a handle to it. So for Excel createObject will launch a new Excel application and getObject will get the first Excel Application in the ROT and return a handle. DOORS is different. Regarding COM DOORS has always acted like a singleton, i.e. getObject and createObject would try to start up a new DOORS instance, when no one is started and they would both return a handle to an existing DOORS instance, if one is started. I did not re-test one newer DOORS versions, but I would assume that createObject and getObject can be used both to interact with DOORS over COM. Regards, Mathias |
Re: Trigger Doors from Excel? tobi_mehrl - Fri Dec 05 06:53:07 EST 2014 Hi Mathias,
thx for your answer. One question I have: What do you mean by saying "... and COM"...? What is COM relating to VBA?
In this 8 lines of code, you use the function "GetObject(...)". What does this function? What String I have to write in the first "" of the parameter of GetObject?
I have to read from a specific spec in the Doors-database. How can I define this Pathin VBA which points to this specific spec? Hi Tobias, COM is the successor of OLE - in non technical words it is a Microsoft standard for Application interfaces. The most known Interface of COM is what you know as an OLE-Object - Something that can be embedded and edited inside another application. But generally you can COM as a platform for all kinds of functionality, you can expose a certain functionality over a COM server and other applications (COM client) can use it. The modern COM standard allows you to invoke COM Servers on a remote machine too. Microsoft provides on its own a very large collection of COM interfaces on Windows itself. E.g. the Windows Explorer, Data Services like XML and Database Connectivity, etc. - all these are accessible over COM. Why do you need that? Because by Design applications in Windows cannot communicate with each other, except for using one of the low level interfaces: network sockets, files, pipes, shared memory pages, etc. However these low level interfaces support only binary data, not the transfer of complex data structures. COM goes one step further - it allows not only to pass specific data structures from one application to the other (COM has certain predefined types, like arrays, and of course you can define your own). It does not only handle the 'marshalling', i.e. the conversion of the data structures into a byte stream, the transfer (e.g. over a network) to the other application and the 'unmarshalling', i.e. the conversion of the byte stream back to real data - it also handles reference counting on the data, i.e. it will also take care of disposing data, that is not used by anyone. That means that the Excel COM Server for example will shut down (at least it should), when it was opened by a COM client, and the client will delete its handle. so it is not in use anymore. Also a lot of applications expose their interface over COM - DOORS is one of them. Unfortunately the DOORS interface is very very poor: It only has two functions. "runStr" which allows you to run a DXL program stored in a string and "runFile" which allows you to run a DXL file. So what the above program does is it first gets a handle to the COM interface of DOORS using "getObject": Then it creates a small DXL program which gets the Heading from the current Object in the first open module, and read its Heading. Now DOORS also provides on its COM interface a function to send / retrieve a string to the running DXL program (oleSetResult, oleGetResult). We use this mechanism to get the Object Heading to Excel, where it can be read and processed by VBA. Hope this gives you some overview. If you got some specific questions, feel free to ask them in this thread. Regards, Mathias |
Re: Trigger Doors from Excel? What purpose has the first parameter (in our case it is empty) of the GetObject(...)-function?
doors.App.runFile ""? What I have to write in the ""? |
Re: Trigger Doors from Excel? bandchef - Wed Dec 10 08:53:22 EST 2014 What purpose has the first parameter (in our case it is empty) of the GetObject(...)-function?
doors.App.runFile ""? What I have to write in the ""? Hello bandchef, about GetObject, see my above post and the link to the MSDN Library (the content is available in English as well). about runFile: use the forum search. E.g. there was an example script on May 13, 2014: https://www.ibm.com/developerworks/community/forums/html/topic?id=7a01e109-c6a2-443b-a3df-15f781b70c08
BR, |
Re: Trigger Doors from Excel? Mike.Scharnow - Wed Dec 10 09:08:06 EST 2014 Hello bandchef, about GetObject, see my above post and the link to the MSDN Library (the content is available in English as well). about runFile: use the forum search. E.g. there was an example script on May 13, 2014: https://www.ibm.com/developerworks/community/forums/html/topic?id=7a01e109-c6a2-443b-a3df-15f781b70c08
BR, Thx for answer.
If I used a .dxl-Script in a extra file, a get a "OK" in the Excel cell. I can only return strings from dxl using this ole-objects. Which possibilities I have to copy over this limited interface? |
Re: Trigger Doors from Excel? tobi_mehrl - Thu Dec 11 04:52:31 EST 2014 Thx for answer.
If I used a .dxl-Script in a extra file, a get a "OK" in the Excel cell. I can only return strings from dxl using this ole-objects. Which possibilities I have to copy over this limited interface? The fastest way for exporting data over this interface is to create a 'tab' separated Table as a string in DXL. This one you can immediately set in Excel and you can fill a complete sheet that in one go. In this post, you should find some code for a reasonably fast way to build up the TSV / CSV String from the module values (the AddEscapedStringToBuffer function): If you try to run the example DXL code from that post (which copies a reasonable amount of data), you can check the speed. There is also some discussion on code-pages in this post - when transferring data from DOORS to excel you need to take care, that DOORS will encode data in UTF-8 but the data will be transferred in the default system code page (in case you got any non-ascii characters in your data) Another way to pass it from DOORS to Excel by the way, is to set the data to the clipboard and then just invoke a "PASTE" from VBA (instead of using doorsApp.result / oleSetResult). However there might be glitches, when the user saves something to clipboard right inbetween the copy and the paste. So consider this carefully. Hope this helps, regards, Mathias
|
Re: Trigger Doors from Excel? Mathias Mamsch - Thu Dec 11 07:01:14 EST 2014 The fastest way for exporting data over this interface is to create a 'tab' separated Table as a string in DXL. This one you can immediately set in Excel and you can fill a complete sheet that in one go. In this post, you should find some code for a reasonably fast way to build up the TSV / CSV String from the module values (the AddEscapedStringToBuffer function): If you try to run the example DXL code from that post (which copies a reasonable amount of data), you can check the speed. There is also some discussion on code-pages in this post - when transferring data from DOORS to excel you need to take care, that DOORS will encode data in UTF-8 but the data will be transferred in the default system code page (in case you got any non-ascii characters in your data) Another way to pass it from DOORS to Excel by the way, is to set the data to the clipboard and then just invoke a "PASTE" from VBA (instead of using doorsApp.result / oleSetResult). However there might be glitches, when the user saves something to clipboard right inbetween the copy and the paste. So consider this carefully. Hope this helps, regards, Mathias
Do you mean your answer with this code in this Thread? |
Re: Trigger Doors from Excel? tobi_mehrl - Thu Dec 11 07:08:16 EST 2014 Do you mean your answer with this code in this Thread? I mean the posted 'AddEscapedStringToBuffer' function which will allow you to build up a string containing tsv / csv data. This string you can transfer either over COM (doorsApp.result / oleSetResult) or over clipboard (as also shown on the post). The usage of the function is also demonstrated in the post. Regards, Mathias |