search for string inside of OLE table

doors36677 - Thu Sep 16 14:28:08 EDT 2010
In dxl you can't search for strings in OLE. In the native application of the OLE you can search and replace using the native application.

lsand - Thu Sep 16 14:48:23 EDT 2010
thanks.

Zetareticuli - Mon Dec 27 10:07:43 EST 2010
I'd just like to add to this. I had a similar interest in my own project. We had Word tables embedded as OLE objects. I wanted to be able to open different versions of the Word table and find discrepancies with a Word compare. I opened the table in Word using DOORS OLE commands and executed the Word compare via VBA. Word returned the result to the DXL and I was able to provide visual feedback to the end user as to whether or not the table matched.

The moral of the story is although you can't directly access OLE text to search it, you can still do it if you have the appropriate interface to the external program which opens the OLE object.

jbjacques - Tue Jun 12 08:53:30 EDT 2012
Hello,

I am facing the same issue like you.

Zetareticuli, did you manage to automatize your procedure ?

It would be really hepfull for us.

Best regards

David_G_Bond - Tue Jun 12 10:48:17 EDT 2012

Here is an example I created for searching for "TBD" within OLEs in a module. It may serve as a starting point for you.

// Process Ole
/*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
This is a sample program that checks for TBDs in an OLE in the Object
Text.  This program ignores objects with multiple OLEs. It assumes that the
OLE is first order (i.e., does not itself contain an embedded OLE).

Excel does not provide a method for searching a chart, so such OLEs are added
to an error string in this example

Static and unregistered OLEs cannot be activated and searched, so they are
added to an error string in this example

This example only searches in OLEs from Word or Excel.  It is possible to
expand its capabilities to other applications. It is written for Office 2003,
but should still work for Office 2007. It may be modified to search for other
words than "TBD"
------------------------------------------------------------------------------*/
 
const int xlWorksheet = -4167
const int xlFormulas = -4123
 
Module m
Object o
string sName
string sErr
bool bIsActivated
bool bFoundTBD
bool bIsStatic
bool bIsUnregistered
int iObjMax, iCount, iType
 
OleAutoObj objDoc
OleAutoObj objApp
OleAutoObj objSelection
OleAutoObj objFind
OleAutoObj objCells
OleAutoObj objCell
OleAutoObj objSheet
 
OleAutoArgs objArgs = create
 
string sError = ""
string sTBDObjects = "The following objects have OLEs with TBDs: "
string sNoTBDObjects = "The following objects have OLEs that do not have TBDs: "
int iNumberOfOles
string sObjId
 
m = current
if (null m)
{
   errorBox "This function must be run from within a module"
   halt
}
 
iObjMax = 0
iCount = 0
for o in m do iObjMax++
progressStart(dbExplorer, "Find TDBs in OLEs", "Something", iObjMax)
for o in m do
{
   iCount++
   progressStep iCount
   sObjId = identifier o ""
   progressMessage "Processing " iCount " of " iObjMax " (" sObjId ")"
   iNumberOfOles = oleCount(o."Object Text")
   if (iNumberOfOles > 1)
   {
      sError = sError "Object " sObjId " has more than one OLE\n"
      continue
   }
   if (iNumberOfOles == 0) continue
   
   // only one OLE, so let's process it.
   bIsStatic = containsStaticOle(o."Object Text")
   bIsUnregistered = containsUnregisteredOle(o."Object Text")
   if (bIsStatic)
   {
      bIsActivated = false
   }
   else if (bIsUnregistered)
   {
      bIsActivated = false
   }
   else bIsActivated = oleActivate(o)
   if (!bIsActivated)
   {
      sError = sError "Unable to activate OLE for object " sObjId ": "
      if (bIsStatic) sError = sError "It is Static\n"
      else if (bIsUnregistered) sError = sError "It is Unregistered\n"
      else sError = sError "Reason unknown\n"
      continue
   }
   objDoc = oleGetAutoObject(o)
   if (null objDoc)
   {
      sError = sError "Unable to get document handle for OLE object in " sObjId "\n"
      if bIsActivated then oleDeactivate(o)
      oleCloseAutoObject objDoc
      continue
   }
   sErr = oleGet(objDoc, "Application", objApp)
   if (!null sErr)
   {
      sError = sError "Cannot get application object for OLE in " sObjId "\n"
      oleDeactivate(o)
      continue
   }
   if (null objApp)
   {
      sError = sError "Unable to get application for activated document in " sObjId "\n"
      if bIsActivated then oleDeactivate(o)
      continue
   }
   oleGet(objApp, "Name", sName)
   if (matches("Word", sName))
   {
      oleGet(objApp, "Selection", objSelection)
      oleGet(objSelection, "Find", objFind)
      oleMethod(objFind, "ClearFormatting")
      olePut(objFind, "Text", "TBD")
      olePut(objFind, "MatchWholeWord", true)
      olePut(objFind, "Format", false)
      oleMethod(objFind, "Execute")
      oleGet(objFind, "Found", bFoundTBD)
      if (bFoundTBD) sTBDObjects = sTBDObjects sObjId "\n"
      else sNoTBDObjects = sNoTBDObjects sObjId "\n"
   }
   else if (matches("Excel", sName))
   {
      // get the cells on the active sheet
      oleGet(objApp, "ActiveSheet", objSheet)
      sErr = oleGet(objSheet, "Type", iType)
      if (!null sErr) sError = sError "Unable to get Excel type: " sErr "\n"
      else if (iType == xlWorksheet)
      {
         oleGet(objApp, "Cells", objCells)
         clear objArgs
         put(objArgs, "What", "TBD")
         put(objArgs, "LookIn", xlFormulas)
         put(objArgs, "MatchCase", true)
         put(objArgs, "SearchFormat", false)
         oleMethod(objCells, "Find", objArgs, objCell)
         if (!null objCell) sTBDObjects = sTBDObjects sObjId "\n"
         else sNoTBDObjects = sNoTBDObjects sObjId "\n"
      }
      else sError = sError "Cannot search Excel Chart in " sObjId "\n"
   }
   else
   {
      sError = sError "Application " sName " not currently supported\n"
   }
   if bIsActivated then oleDeactivate(o)
}
progressStop
 
if (sError != "") warningBox sError
infoBox sTBDObjects
infoBox sNoTBDObjects

• David Bond

David_G_Bond - Tue Jun 12 10:48:17 EDT 2012

Here is an example I created for searching for "TBD" within OLEs in a module. It may serve as a starting point for you.

// Process Ole
/*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
This is a sample program that checks for TBDs in an OLE in the Object
Text.  This program ignores objects with multiple OLEs. It assumes that the
OLE is first order (i.e., does not itself contain an embedded OLE).

Excel does not provide a method for searching a chart, so such OLEs are added
to an error string in this example

Static and unregistered OLEs cannot be activated and searched, so they are
added to an error string in this example

This example only searches in OLEs from Word or Excel.  It is possible to
expand its capabilities to other applications. It is written for Office 2003,
but should still work for Office 2007. It may be modified to search for other
words than "TBD"
------------------------------------------------------------------------------*/
 
const int xlWorksheet = -4167
const int xlFormulas = -4123
 
Module m
Object o
string sName
string sErr
bool bIsActivated
bool bFoundTBD
bool bIsStatic
bool bIsUnregistered
int iObjMax, iCount, iType
 
OleAutoObj objDoc
OleAutoObj objApp
OleAutoObj objSelection
OleAutoObj objFind
OleAutoObj objCells
OleAutoObj objCell
OleAutoObj objSheet
 
OleAutoArgs objArgs = create
 
string sError = ""
string sTBDObjects = "The following objects have OLEs with TBDs: "
string sNoTBDObjects = "The following objects have OLEs that do not have TBDs: "
int iNumberOfOles
string sObjId
 
m = current
if (null m)
{
   errorBox "This function must be run from within a module"
   halt
}
 
iObjMax = 0
iCount = 0
for o in m do iObjMax++
progressStart(dbExplorer, "Find TDBs in OLEs", "Something", iObjMax)
for o in m do
{
   iCount++
   progressStep iCount
   sObjId = identifier o ""
   progressMessage "Processing " iCount " of " iObjMax " (" sObjId ")"
   iNumberOfOles = oleCount(o."Object Text")
   if (iNumberOfOles > 1)
   {
      sError = sError "Object " sObjId " has more than one OLE\n"
      continue
   }
   if (iNumberOfOles == 0) continue
   
   // only one OLE, so let's process it.
   bIsStatic = containsStaticOle(o."Object Text")
   bIsUnregistered = containsUnregisteredOle(o."Object Text")
   if (bIsStatic)
   {
      bIsActivated = false
   }
   else if (bIsUnregistered)
   {
      bIsActivated = false
   }
   else bIsActivated = oleActivate(o)
   if (!bIsActivated)
   {
      sError = sError "Unable to activate OLE for object " sObjId ": "
      if (bIsStatic) sError = sError "It is Static\n"
      else if (bIsUnregistered) sError = sError "It is Unregistered\n"
      else sError = sError "Reason unknown\n"
      continue
   }
   objDoc = oleGetAutoObject(o)
   if (null objDoc)
   {
      sError = sError "Unable to get document handle for OLE object in " sObjId "\n"
      if bIsActivated then oleDeactivate(o)
      oleCloseAutoObject objDoc
      continue
   }
   sErr = oleGet(objDoc, "Application", objApp)
   if (!null sErr)
   {
      sError = sError "Cannot get application object for OLE in " sObjId "\n"
      oleDeactivate(o)
      continue
   }
   if (null objApp)
   {
      sError = sError "Unable to get application for activated document in " sObjId "\n"
      if bIsActivated then oleDeactivate(o)
      continue
   }
   oleGet(objApp, "Name", sName)
   if (matches("Word", sName))
   {
      oleGet(objApp, "Selection", objSelection)
      oleGet(objSelection, "Find", objFind)
      oleMethod(objFind, "ClearFormatting")
      olePut(objFind, "Text", "TBD")
      olePut(objFind, "MatchWholeWord", true)
      olePut(objFind, "Format", false)
      oleMethod(objFind, "Execute")
      oleGet(objFind, "Found", bFoundTBD)
      if (bFoundTBD) sTBDObjects = sTBDObjects sObjId "\n"
      else sNoTBDObjects = sNoTBDObjects sObjId "\n"
   }
   else if (matches("Excel", sName))
   {
      // get the cells on the active sheet
      oleGet(objApp, "ActiveSheet", objSheet)
      sErr = oleGet(objSheet, "Type", iType)
      if (!null sErr) sError = sError "Unable to get Excel type: " sErr "\n"
      else if (iType == xlWorksheet)
      {
         oleGet(objApp, "Cells", objCells)
         clear objArgs
         put(objArgs, "What", "TBD")
         put(objArgs, "LookIn", xlFormulas)
         put(objArgs, "MatchCase", true)
         put(objArgs, "SearchFormat", false)
         oleMethod(objCells, "Find", objArgs, objCell)
         if (!null objCell) sTBDObjects = sTBDObjects sObjId "\n"
         else sNoTBDObjects = sNoTBDObjects sObjId "\n"
      }
      else sError = sError "Cannot search Excel Chart in " sObjId "\n"
   }
   else
   {
      sError = sError "Application " sName " not currently supported\n"
   }
   if bIsActivated then oleDeactivate(o)
}
progressStop
 
if (sError != "") warningBox sError
infoBox sTBDObjects
infoBox sNoTBDObjects

• David Bond

mcnairk - Tue Jun 12 12:20:16 EDT 2012
In DOORS 8.2 get a syntax error on line 51:

progressStart(dbExplorer, "Find TDBs in OLEs", "Something", iObjMax) for o in m do {

Any ideas?

Thanks,
Ken.

SystemAdmin - Tue Jun 12 13:06:08 EDT 2012
It seems that you had a problem when copying the code to your editor.
The command "progressStart" with its parameters should stand alone, it is line 58 in my environment.
line 59 contains the command "for o in m do {"

mcnairk - Wed Jun 13 08:47:09 EDT 2012
Duuuh!
Thanks Mike.
Thanks also to David for providing the code; I've been looking for something like this for years!

Ken.

mcnairk - Wed Jun 13 09:29:50 EDT 2012
Seems to leave Word open afterwards. Anyone know how to close it?

THX Ken.

David_G_Bond - Wed Jun 13 14:24:30 EDT 2012
I also did not properly clean up the OleAutoObj objects. The memory management is nil. I just intended this as a quick example. What I did was study the object models and vba to get the right methods and properties. It is not hard to figure out how to close down the Word application. The Close method will close the Word document. The Quit method will exit Word.

• David Bond

mcnairk - Fri Jun 15 08:14:28 EDT 2012
I've figured out how to do the cleanup, but should I do this after examining each OLE object?
Also, should I close MSWord after each object (allowing the user to see the progress bar, but might slow down execution since it has to keep re-opening Word) or wait till all objects in the module have been processed, before moving onto the next module. My intended use is to create a simple interface for users so they can enter the search string and a list of modules to search (typically 50 active modules that currently takes about 12 minutes to trawl...)

I found the following paper a useful primer on this subject:
http://download-na.telelogic.com/download/ugcagenda/W_DennisLockshine-Final-IntegratingDOORSintoWindows-VisualBasicandCOMprogrammingusingDOORSDXL.pdf

Many thanks,
Ken.