Connecting to OI from Ms Office Applications using XREV.DLL

Another in the series of Works Code examples.

Previous OLE examples have shown how to use OpenInsight as the OLE "client", consuming services offered by other OLE "servers".

Here is an example which uses OpenInsight as the OLE server, accessed from a Visual basic client. The Example includes a sample class using Xrev.dll, an Excel example of pulling data from OI into Excel and pushing changes back into OI. This is sample code, without error handling, with hard coded parameters, not supported by RTI, etc. The "OpenInsight" class has worked for me in various Visual Basic or Visual Basic for Applications (VBA) hosts, such as Excel, Word, MsAccess and OutLook. The rest of the code below is Excel specific.

If you build a spreadsheet using the scripts below you ought to be able build spreadsheets which look up information from OpenInsight. For example if your spreadsheet has zipcodes in column A and your OI has a ZIPCODES table then in column B you could have a formula like

=Xlate("ZIPCODES",$A4,"CITY")

I hope this example proves useful to some of you.

- Bob

pre.code {

 background-color: #E5E5E5;

 border: 1px solid #000000;

 width: 780px;

 padding: 5px;

 font-family: courier, verdana, arial, serif;

 margin: 0px 10px auto;

}

1. Generic Visual basic class which can be embedded in Excel, Word, MsAcess, or SQlServer

Open an Excel Spreadsheet, Navigate to Tools, Macro,Visual Basic Editor. Insert a class Module, name the module OpenInsight, paste in the following:

I hard coded the OI location and Account information, you will need to adjust it.

Option Explicit
' OiClass
'
' Example of connecting to OpenInsight from VBA
' Using Xrev.dll
'
' Notes:
'
' You must register XREV.DLL
' e.g.  regsvr32 c:\revsoft\OpenInsight\Xrev.dll

' Oi location (mRevCapiDir) is hard coded in this example.
' see the Class Initialize function
' You can set it via public property

' Error Handling is not included.

' Write_Value, Read_Value depend on custom programs in OI


' 03-13-2006   rjc ([email protected])   Created
' ----------------------------------------------

' Valid Values for OpenFlag
Public Enum oiCreateFlag
    StaticEngine=0
    DynamicVisible=1
    DynamicHidden=65
End Enum

' Valid values for Close Flag
Public Enum oiCloseFlag
    StaticEngine=0
    DynamicEngine=1
End Enum

'-------------------------------------------------
' Class Variables Mostly External Engine Parameters
' See OpenInsight Documentation for Explanation
'------------------------------------------------
Private mRevelation As Revelation   ' Xrev object
Private mEngine As Object
Private mQueue  As Object
Private mQueName As String
Private mServerName As String
Private mRevCapiDir As String
Private mDatabase As String
Private mUserName As String
Private mPassword As String
Private mCreateFlag As oiCreateFlag
Private mCloseFlag As oiCloseFlag

Private mConnected As Boolean       ' True if connected

Private Sub Class_Initialize()

    Set mRevelation=CreateObject("Revsoft.Revelation")
    Set mEngine=Nothing
    Set mQueue=Nothing
    mConnected=False
    
    mQueName="
    mServerName="
    mRevCapiDir=c:\revsoft\OI41"
    mDatabase=SYSPROG"
    mUserName=SYSPROG"
    mPassword="
       
    mCreateFlag=DynamicHidden
    mCloseFlag=DynamicEngine

End Sub

Public Property Get ServerName() As String
    ServerName=mServerName
End Property
Public Property Let ServerName(value As String)
    mServerName=value
End Property

Public Property Get RevCapiDir() As String
    RevCapiDir=mRevCapiDir
End Property
Public Property Let RevCapiDir(value As String)
   mRevCapiDir=value
End Property
Public Property Get UserName() As String
    UserName=mUserName
End Property
Public Property Let UserName(value As String)
   mUserName=value
End Property
Public Property Get Database() As String
   Database=mDatabase
End Property
Public Property Let Database(value As String)
   mDatabase=value
End Property
Public Property Let CreateFlag(value As oiCreateFlag)
   mCreateFlag=value
End Property
Public Property Get CreateFlag() As oiCreateFlag
   CreateFlag=mCreateFlag
End Property
Public Property Get CloseFlag() As oiCloseFlag
    CloseFlag=mCloseFlag
End Property
Public Property Let CloseFlag(value As oiCloseFlag)
    mCloseFlag=value
End Property

Public Sub Write_Value(tableName As String, rowID As String, colID As String,  _
colValue As String)

   ' Wrapper for Write_Column function
   ' tableName=Name of OI Table.  Table must have a dictionary
   ' rowID=id of a single row
   ' colId=name / number of a column in the dictionary
   ' colValue=value to write into Oi,  in external (Oconv'd) format
         
         
   Dim ret As Integer
         
   If mConnected=False Then
      Connect
      If mConnected=False Then
         Exit Sub
      End If
   End If
   
   ret=mQueue.CallSubroutine("WRITE_VALUE", tableName, rowID, colID, colValue)

End Sub
Public Sub Write_Row(tableName As String, rowID As String, rowValue As String)

   ' Update OpenInsight with the value of a cell
   ' Target is the range to update
   Dim ret As Integer
   
   If mConnected=False Then
      Connect
      If mConnected=False Then
         Exit Sub
      End If
   End If
   
   ret=mQueue.CallSubroutine("WRITE_ROW", tableName, rowID, rowValue, 1)

End Sub
Function Read_Value(tableName As String, rowID As String, colID As String) As String

   ' Wrapper for Xlate
   ' tableName =Name of OpenInsight Table
   ' rowID   =id of Row, use vbTab delimiter to send in  multiple ids
   ' colID=Name / Nr of Column, rules same as Xlate.  Blank returns record
   
   ' Returns:
   ' Same as Xlate, with @fm -> vbLf, @vm ->vbTab
   
   '-------------------------------
     
   Dim StrResult As String
   Dim ret As Integer
     
   Read_Value="
   
   If tableName=" Then
      Exit Function
   End If
   
   If rowID=" Then
      Exit Function
   End If
   
   ' Connected?
   If mConnected=False Then
        Connect
        If mConnected=False Then
           Exit Function
        End If
   End If
   
   '
   ' Note: You need to create Read Value as a wrapper around Xlate, e.g.
   ' function read_value(tablename, rowid, colid)
   ' return Xlate( tablename, rowid, colid, "X")
   '
   ret=mQueue.CallFunction(StrResult, "READ_VALUE", tableName, rowID, colID)
   If ret=0 Then
       Read_Value=StrResult
   End If
    
   
End Function

Public Sub Connect()

    ' Connect to an Engine
    ' Expects servername, database, etc all set
    '-----------------------------------------
    If mConnected Then
       Disconnect
    End If
    
    ChDir (mRevCapiDir)
    
    Dim ret As Integer
    ret=mRevelation.CreateEngine(mEngine, mServerName, mDatabase, mCreateFlag, mCloseFlag)
    If ret=0 Then
           ret=mEngine.CreateQueue(mQueue, mQueName, mDatabase, mUserName, mPassword)
    End If
    
    mConnected=(ret=0)
    
End Sub
Public Sub Disconnect()
     Dim ret As Integer
     If mConnected Then
        ret=mEngine.CloseEngine()
        ret=mQueue.CloseQueue()
     End If
End Sub

Private Sub Class_Terminate()
     ' Kill Connection
     Dim ret As Integer
     If mConnected Then
        Disconnect
     End If
     Set mEngine=Nothing
     Set mQueue=Nothing
     Set mRevelation=Nothing
     mConnected=False
End Sub

2. Example module in Excel which uses the above class to connect to OpenInsight, Creates "Xlate" and "UpdateOI" as functions you can use in any formula in any cell

In the Excel Visual basic editor, Insert a new module named modGlobal, paste the following:

Dim gConnection As OpenInsight
Public Property Get oiConnection() As OpenInsight
    ' Prepare for Connection to Oi
    If TypeName(gConnection)=Nothing" Then
       Set gConnection=New OpenInsight
    End If
    Set oiConnection=gConnection
End Property
Function Xlate(table As String, row As String, column As String)
  Dim oConn As OpenInsight
  Set oConn=oiConnection
  Xlate=oConn.Read_Value(table, row, column)
End Function

Public Sub UpdateOi(Source As Range)

   ' Update OpenInsight with the value of a cell  
   ' In this example I jst write to Syslists
   
   Dim curRow As Integer
   Dim curCol As Integer
   
   Dim ret As Integer
   Dim value As Variant
   Dim message As String
   
   curCol=Source.column
   curRow=Source.row
   value=Source.Cells(1, 1).value

   message=Col " & curCol & " Row " & curRow & " changed to " & value
   
   oiConnection.Write_Row "SYSLISTS", "EXCEL", message
  

End Sub

3. WorkSheet Event code so that the spreadsheet will connect to OI when it opens, write any changes back to OI.

In this example I just write the changes to SYSLISTS.

Paste the Following code in the "This Worksheet" module in the Visual Basic Editor

Dim bRefreshing As Boolean


Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' Close Connection to OI
    Set gConnection=Nothing
End Sub

Private Sub Workbook_Open()
   bRefreshing=False
   ' Open Connection to Oi
   Set gConnection=New OpenInsight
   
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim oTarget As Range
    Set oTarget=Target
    If Not bRefreshing Then
        bRefreshing=True
        UpdateOi oTarget
        bRefreshing=False
    End If
End Sub

WOW! .. that was great.

Hi Bob,

Thank you for the code given this is exactkt what i want to do in one of my programs.But I have a small problem in the code for 'Openinsight'

module 'Private mRevelation As Revelation ' Xrev object,when try to compile with this statement error-'userdefined type not defined'compile error is giving what am i doing wrong??? Please help me.

Thanks

pavani(IngNz)

1. Make sure you registered Xrev.dll using regsvr32.

In the Visual Basic Editor, add a reference to the Revsoft 1.0 Type library. You add a reference by selecting "References" from the "Tools" menu. The references Panel should show you a list of the Activex objects listed on your system. If the Revsoft 1.0 Type library is not shown, then you probably need to register Xrev.dll, using a DOS command like

regsvr32 "c:\revsoft\openinsight\Xrev.dll"

If your OpenInsight is on a network then the regsvr command should reference the xrev at the network path. Note that Xrev will need to be registered on each machine that will run the Visual Basic Macro.

HTH

- Bob

Thanks for your help Bob.Now I can compile my project but not able to enter the formula in excel sheet second column I want to use Xlate('Job',$a1,'Priority') .but system not allowing and says runtime error occured error '424'object required.I think its not able to createobject Oiconnection. Help please

Hi Pavani,

It sounds like Xrev.dll is not registered, or the program is looking in the wrong spot.

Did you get a message like 'registration succeeded' when you ran regsvr32 "\myoipath\xrev.dll" from a DOS command line?

Did you edit the visual basic code to change the revacpidir from my openinsight path to your openInsight path?

- Bob

Bob.Yes I registered that dll and got the message also and I got the reference to Revsoft1.0 library.In the code for 'OpenInsight' module i changed revcapi dir to 'J:\devOi\CombOi' where I have my Oinght.exe,and my database name and username also i changed.The project compiled sucessfully.BUT STILL NOT ABLE TO ENTER THE FUNCTION ON SECOND COLUMN.I am using excel2003.In the project explorer you can see Project name-'OITest'under excel objects I have 'thisworkbook'and under class modules -'ModGlobal'and 'OpenInsight'

what am i doing wrong.Help Please!

Send me your email, I'll send a copy of my spreadsheet. It too is Excel2003.

Bob

[email protected]

Thanks Bob.My email is-

View this thread on the Works forum...