Word to Access Automation

[groupware]

Hi,

An age old question and I have been extensively searching the past
posts and have seen some reasonable suggestions.

I am after some thoughts on the best way to achieve this. I am
building an application suit from scratch that needs to be able to
import Word documents into an Access Database.

The word documents/templates will effectively contain repetitive
information structures that will be scattered throughout the document
in tables. They are issues listings eg issue, action, due date etc

The number of tables is not standard there could be 1 or 20 (same
structure though) in any document and each table could contain multiple
rows of information.

The word document/template itself will be automated through a toolbar
so I can control formats, styles and bookmarks etc.

Are form fields the best way to go ? How can I use form fields for
repetitive information and then map these back to access ?

I have used bookmarks and styles previously to automate word tables
(but this became a bit messy)

I am comfortable with the code to cycle through the word document and
write data back to access.

But what is the most workable approach that people have seen ?

Thanks

Jason.

 

[Gargoyle]

Hi,

An age old question and I have been extensively searching the past
posts and have seen some reasonable suggestions.

I am after some thoughts on the best way to achieve this. I am
building an application suit from scratch that needs to be able to
import Word documents into an Access Database.

The word documents/templates will effectively contain repetitive
information structures that will be scattered throughout the document
in tables. They are issues listings eg issue, action, due date etc

The number of tables is not standard there could be 1 or 20 (same
structure though) in any document and each table could contain multiple
rows of information.

The word document/template itself will be automated through a toolbar
so I can control formats, styles and bookmarks etc.

Are form fields the best way to go ? How can I use form fields for
repetitive information and then map these back to access ?

I have used bookmarks and styles previously to automate word tables
(but this became a bit messy)

I am comfortable with the code to cycle through the word document and
write data back to access.

But what is the most workable approach that people have seen ?

Thanks

Jason.

Have you considered just using Access reports instead, rather than mess about
with Word?

 

[groupware]

Hi Gargoyle,

I am trying to create an application that imports word documents, not
to necessarily create reports. It is an information capture system
where not everyone has Microsoft Access available and documents need to
be compiled and distributed in Word.

Jason.

 

[aaron.kempf]

Have you considered just using Access reports instead, rather than mess
about with Word?

 

[John Nurick]

Hi Jason,

If the data you need to extract from the Word document is in Word
tables, it may be simplest to use the document's Tables collection to
work through the tables. E.g. (air code)

Dim oDoc As Word.Document
Dim oTable As Word.Table
Dim oRow As Word.Row
Dim j As Long
Dim strValue As String

...
Set oDoc = GetObject("C:\temp\test.doc")
For Each oTable In oDoc.StoryRanges(wdMainTextStory).Tables
For Each oRow In oTable.Rows
For j = 1 To oRow.Cells.Count
strValue = oRow.Cells(j).Range.Text
'do stuff with the value
...
Next j
Next oRow
Next oTable
oDoc.Close False
...

Form fields are great if you just have one record's worth of data in the
document. With multiple records per document the problem is that you can
address them by index
oDoc.FormFields(j).Range.Text
or by the name or index of the bookmark each is associated with
oDoc.Bookmarks(strName).Range.Text
but this doesn't provide an easy way of telling which formfield is part
of which record. (Though I suppose you could get over this partially by
having a section break between each record, and using something like
oDoc.Sections(j).Range.FormFields(k).Range.Text

HTH

 

[groupware]

Thanks John.

I have actually used the method you talked about before to autmoate
some summary/reformatting of information within word only. (But of
course not every table row/cell contains a record or part thereof).
But cycling through the tables collection is more than likely the
approach I will take.

At this stage it looks like the best way to go as I can create a number
of macros for the user to add a new record and this will automatically
insert the relvant bookmark index. (I have also done this using styles
previously)

Any tips on how to "protect" bookmark indexes as they have a tendency
to get deleted ?

Thanks

Jason

 

[John Nurick]

Jason,

Any tips on how to "protect" bookmark indexes as they have a tendency
to get deleted ?

I don't think there is a way. You could use formfields (maybe in table
cells) and protect the document.

But would it be possible to get the users to enter data in Excel rather
than Word? That would give you a lot more control over what they could
do, and make it much easier to validate the data they enter.