import data fr MS Word table

[sparky88]

A newbie trying to figure out how best to import data from MS Word into a SQL Server db. I'm also open to any other suggestions if using tables in Word isn't the greatest solution. Thanks in advance.

MS Word TableA Example:

Customer ProductNo
Johnson 5
Stevenson 10
Williams 16

MS Word TableB Example:

ProductNo ProductNotes
5 Silk scarves manufactured in China and
distributed by Xi-Ling Exports.
10 Ginzu kitchen knives. Set of 52 stainless steel
pieces.
16 Ping titanium golf clubs. 1 year warranty.

 

[HermanvLimbeek]

Importing from Word works fine. You can try something like this:

Public Sub OpenTable(sDoc As String)

Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim wdTable As Word.Table

Global sWdTxt() As String

Dim X As Integer, Y As Integer
Dim i As Integer, j As Integer, n As Integer

Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Open(sDoc)
Set wdTable = wdDoc.Tables(1)

'the first row is a heading (row 0) (in my case!!!)
X = wdTable.Rows.Count
Y = wdTable.Columns.Count

ReDim sWdTxt(X - 1, Y)

For i = 1 To X - 1

For j = 1 To Y
sWdTxt(i, j) = wdTable.Cell(i + 1, j).Range.FormattedText
If Trim(sWdTxt(i, j)) <> sEmpty Then
sWdTxt(i, j) = Left(sWdTxt(i, j), Len(sWdTxt(i, j)) - 2) 'strip last two characters

For n = 1 To Len(sWdTxt(i, j))
'add a linefeed to the carriage-return for a proper display
'in textboxes
If Asc(Mid(sWdTxt(i, j), n, 1)) = 13 Then
sWdTxt(i, j) = Left(sWdTxt(i, j), n) & Chr(10) & Mid(sWdTxt(i, j), n + 1)
End If
Next
End If
Next
DoEvents
Next
End Sub

I import the Word data to a global array. So after import I can close Word and handle the data in the array.

Add a reference to Microsoft Word 8.0 Object Library (or whatever version you may have on your system) to be able to access Word.