import from word to access

[Vinnie]

It doesn't seem too hard to export data from Access to
Word, but it seems to be impossible to import data from
Word to Access. Wondering if anyone can tell me how to do
this. Thanks.

 

[John Nurick]

Hi Vinnie,

A: It depends on what the data is. Assuming it's a simple table,

1) Table|Select Table
2) Table|Convert table to text
(separate text with tabs)
3) Save as a text file (only the table, no captions or other text)
4) Import the textfile.

B: With a more complex table, with has multiple sections, merged cells,
totals and subtotals, and so on, importing it won't be straightforward.
Access tables have to be absolutely consistent: every record must have
the same number of columns (fields), each column must have compatible
data in every row (e.g. if you want a column in Word to become a numeric
field in Access, every cell in the column must contain either a number
or nothing, not even "-" or "n/a".)

The general idea is to rearrange the table until you get it into that
state. Any rows of totals and subtotals you simply omit, because they
can easily be recalculated in Access. Any merged cells have to be
unmerged, and appropriate values put in each of them. If the table is
divided into groups of rows, e.g.

East
New York 1 99 5
New Jersey 1 3 8
Maine 2 11 4

change it so each row is self-contained

East New York 1 99 5
East New Jersey 1 3 8
East Maine 2 11 4

and so on. Also, remove all the headings and notes and stuff, leaving a
single row of column headings. These will translate to Access
fieldnames, and therefore should not consist of reserved words such as
"Date" or "Time" and should preferably consist only of letters, digits
and underscores.

Often it helps to paste the table into an Excel worksheet to do all
this. At the end, get it into either an Excel sheet or a comma- or
tab-separated textfile, and from there import it to Access.

C: It's also possible to use Automation to import tables, bookmarked
text, field results, and other things.

It doesn't seem too hard to export data from Access to
Word, but it seems to be impossible to import data from
Word to Access. Wondering if anyone can tell me how to do
this. Thanks.

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

 

[fzraura]

So, does anyone have any code sample on how this would be done?

 

[John Nurick]

It depends entirely on what you're trying to import from Word.

 

[randyas]

Import from word

If table is made correctly copy to excel - and import excel file - advantage you can make fields as dates and Numbers etc.

Had a problem importing tab aligned word document - solution

replaced tabs with ~ character - saved it as a text file - imported it to access table - stripped "~" and saved the data in desired fields.

code eg - this is part of larger code of code for event on access form

' line being import field from word data "~~~~~~chest piece"
Private Sub Sno_GotFocus()
Dim chktld
Dim linex
brk = 0
If Me.Sno = "" Then
p1 = 1

Dim brkr
brkr = 0

linex = Me.line

Do While brkr = 0
chktld = Mid(linex, p1, 1)
If chktld = "~" Then
p1 = p1 + 1

If p1 = brkpt Then
brk = 1

End If
Else
brkr = 1
Me.Nom = Mid(linex, p1, Len(linex))
linex = Me.Nom
End If
Loop
End If 'temp remove
Forms!MEafY2!Nom.SetFocus
Me.Recordset.MovePrevious


If Len(Me.Nom) + Len(linex) < 254 Then
Me.Nom = Me.Nom + " " + linex
Me.Recordset.MoveNext

Else
Me.Recordset.MoveNext
Me.Nomx = "more than 254"

End If

End Sub

' I am sure there is an easier and a more elegant solution but no one's telling
' forgive me if I have left a variable undeclared most need to be in general decelerations before sub
' so that you can use them in other sub routines