none
Find & Replace Macro - Reading from an external database..

    Question

  • I have some code that will do a find and replace looking for a keycode in a database and replacing it with a corresponding value.

    Let's say I write a lesson plan (I'm a teacher) - and I want to write something in from the Natiomal Curriculum (a weighty tome) - it's too much to write in the full statement each time.

    I write ks21c

    the macro opens the db, looks for ks21c and replaces with Key Stage 2 1c) To be able to use a testtube etc.....

    Only problem is - the code is in Star Basic from the OpenOffice 2.0 suite - can anyone convert it for Word????

     

    The code follows........

     

     

    Sub ReplaceABC
    dim aFind as variant
    dim aReplace as variant
    if ColumnAsArray( "NCCodes", "SubjectCodes", "Code", aFind,,,false ) <> -1 then
      if ColumnAsArray( "NCCodes", "SubjectCodes", "Description", aReplace,,,false ) <> -1 then
        oDoc = thisComponent
        aRayCount = lBound(aFind)
        FandR = oDoc.createReplaceDescriptor
        FandR.SearchCaseSensitive = false
        FandR.SearchWords = true
        While aRayCount <= uBound(aFind)
           FandR.setSearchString(aFind(aRayCount))
           FandR.setReplaceString(aReplace(aRayCount))
           aRayCount = aRayCount + 1
           oDoc.ReplaceAll(FandR)
        Wend
      endif
    endif
    End Sub

    function ColumnAsArray( RegDSName as String,TableName as String,ColName as String,retArray as variant,optional UserName as String,optional Password as String,optional distinct as boolean,optional noNull as boolean,optional aConn as variant) as integer

    dim qtStr as string              ' Character used to quote identifiers
    dim conn as variant            ' database connection
    dim strUserName as string ' username passed to connection function
    dim strPassword as string ' password passed to connection function
    dim strDistinct as string '
    dim noBlanks as boolean '
    dim tmpBool as boolean   '
    dim stmt as variant        ' statement used for result set
    dim rs as variant           ' result set object from query
    dim RecCnt as integer   ' number of records
                                          ' that will be returned
                                          ' used to redimension
                                          ' result array
    dim strSQL as string                           

      ColumnAsArray = -1
      'on local error goto ColumnAsArrayError

      if ismissing( UserName ) then
        strUserName = ""
      else
        strUserName = UserName
      endif

      if ismissing( Password ) then
        strPassword = ""
      else
        strPassword = Password
      endif

      if ismissing( distinct ) then
        tmpBool = TRUE
      else
        tmpBool = DISTINCT
      endif

      if tmpBool then
        strDistinct = "DISTINCT"
      else
        strDistinct = ""
      endif


      if ismissing( noNull ) then
        noBlanks = True
      else
        noBlanks = noNull
      endif

      if not ismissing( aConn ) then
        conn = aConn
      else
        conn = CreateUnoService("com.sun.star.sdb.DatabaseContext").getByname( RegDSName ).getConnection( strUserName, StrPassword )
      endif

      qtStr = conn.getMetadata.getIdentifierQuoteString

       ' first thing run a query to see how many
       ' records we can expect
      stmt = conn.createStatement

      strSQL = " SELECT " & strDistinct & " COUNT( " & qtStr & ColName & qtStr & " ) from " & qtStr & TableName & qtStr
      rs = stmt.executeQuery( strSQL )
      rs.next
      RecCnt = rs.getInt( 1 )

      if RecCnt < 1 then
        goto ColumnAsArrayNoRecords
      endif
        ' we have records so dimension our array
        ' and fill it
      RetArray = DimArray( RecCnt )
        ' reset the recCnt variable to use as counter
      RecCnt = 0   
        ' now get the actual records
      strSQL = "SELECT " & strDistinct & " "& qtStr & ColName & qtStr & " from " & qtStr & TableName & qtStr
      rs = stmt.executeQuery( strSQL )   
      rs.next
      do
        if noBlanks then
          if rs.getString( 1 ) <> "" then
           RetArray( RecCnt ) = rs.getString( 1 )
         else
           ' if we did not write the value to
           ' the array then do not increment
           ' the counter
            goto ColumnAsArrayNoInc
          endif
        else
          RetArray( RecCnt ) = rs.getString( 1 )
        endif
       
        RecCnt = RecCnt + 1
        ColumnAsArrayNoInc:
        rs.next
      loop until rs.isAfterLast
     
      redim Preserve RetArray( RecCnt -1 )

    ColumnAsArrayNoRecords:
      if not ismissing( aConn ) then
        conn.dispose
      endif

      ColumnAsArray = RecCnt - 1
      exit function

    ColumnAsArrayError:
      print "Error in ColumnAsArray: " & error( err )
      conn.dispose
                       
    end function 

    Tuesday, January 31, 2006 10:10 PM

All replies

  • Per the support engineer:

    Please help to forward following questions to this partner:

    I’m wondering which database you are using in this scenario, since I saw there are some java method callings like com.sun.star.sdb.DatabaseContext, does it mean you are using some Java drivers to access the database? If so, would you please tell me the database environment like Access, SQL Server or Oracle? Although I can not help to translate all your code into Word VBA, I think we can provide a piece of sample to show you how to select data from database (if supported by MS platform) using ADO in Word VBA and replace certain strings with others.

    -brenda (ISV Buddy Team)

    Thursday, February 09, 2006 5:54 PM
  • It's a open office database -  which I could turn into a access database if needs be..
    Saturday, February 18, 2006 4:19 PM