Search the Site

Sponsors

bottom corner

Insert Microsoft Word content into Oracle database

Using MS Word's built-in macro capability, we can extract and manipulate Word content for insertion into a database.

This page is filed under keyword(s): word, oracle.

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

Did you find this page useful? Please consider browsing other articles or subscribing to the RSS feed to keep up with latest.

This page is filed under keyword(s): word, oracle.
Author: C. Peter Chen
Last updated: 8 Jul 2015

bottom corner