Category: Word

The following sample is over-simplified, but it shows how we can iterate through tables (and their columns and rows) to extract text, and in turn inserting them into an Oracle table.

Option Explicit

Public Sub InsertIntoOracle()
    Dim cn As ADODB.Connection
    Dim source, user, password, str As String
    Dim aTable As Table
    Dim tbl, row, col As Long

    source = "database"
    user = "scott"
    password = "tiger"
    tbl = 0

    Set cn = New ADODB.Connection
    cn.Open "Provider = OraOLEDB.Oracle; Data Source=" & source & "; User Id=" & user & "; Password=" & password & ""
    cn.BeginTrans
    For Each aTable In ActiveDocument.Tables
        tbl = tbl + 1
        For row = 1 To aTable.Rows.Count
            For col = 1 To aTable.Columns.Count
                str = Trim(aTable.Cell(row, col).Range.Text)
                If (Len(str) > 2) Then
                    cn.Execute "insert into document_content values('" & tbl & "-" & row & "-" & col & ": " & str & "')"
                End If
            Next
        Next
    Next

    If cn.Errors.Count = 0 Then
        cn.CommitTrans
    Else
        cn.RollbackTrans
    End If

    cn.Close
End Sub