populate a word table programatically

[Eric]

Any links or tips to do this most appreciated. The data would currently come
from Excel, and get there by VBA. The word doc is protected with form
filling only allowed. v2003 on both.

I posted this on office development as well, as I hadn't seen this forum
first. If there is a more appropriate forum please let me know.

TIA,
Eric

 

[Jay Freedman]

Any links or tips to do this most appreciated. The data would
currently come from Excel, and get there by VBA. The word doc is
protected with form filling only allowed. v2003 on both.

I posted this on office development as well, as I hadn't seen this
forum first. If there is a more appropriate forum please let me know.

TIA,
Eric

The essential idea for retrieving recordsets (rows) from Excel is shown in
http://www.word.mvps.org/FAQs/InterDev/XLToWordWithDAO.htm.

In place of the MsgBox statement in that code, you would need something like
this (assuming the last name is in column A of the worksheet and the first
name is in column B):

With ActiveDocument.FormFields
.Item("LastName").Result = rs.Fields(0).Value
.Item("FirstName").Result = rs.Fields(1).Value
End With

Of course, iterating the rows with While Not rs.EOF doesn't make much sense
for a single Word form unless the worksheet has only one row.

If you get stuck, post back.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[Eric]

Hi Jay

The code will be in an Excel wbk (or addin) that the user will have open, so
I'm not worried about excel not being there. I don't have too much data to
transfer either, so I'm not concerned with speed right now. See below for
technique I would typically use to transfer data around.

The part I'm more concerned about is the mechanics of populating the Word
table, and am thinking it should go something like this
1) create a preformatted table in my protected Word do, with only a header
2) for each row of data
2.1) add a new row to the table (not sure how to do this)
2.2) paste the row into the table (not sure how to do this)
2.3) apply any needed formatting, i.e. currency $ and commas (not sure
how to do this)

Here's how I deal with moving Excel data around
1) create a User Defined Type to act as a data container for each row
2) create the table in code using three Named Ranges ("ptrTableStartCell",
"ptrTableEndCell", "ptrLastTableColumn")
3) for each rngRow in myTable.Rows
with udtMyData
.FirstName = rngRow.Cells(1, 1)
.FirstName = rngRow.Cells(1, 2)
end with

Any critique, pointers much appreciated. I like your DAO idea, but what I'd
really like to do eventually is use a 2008 VSTO based solution, either with
ADO, XML or LINQ. This would facilitate testing, reuse, and security for
both myself and the user. Unfortunately I haven't the slightest idea how to
do that now

Thanks,
Eirc

 

[Jay Freedman]

I can't tell you anything about your Excel code, as I've done very
little of that. But if it gets your worksheet built the way you want,
that's fine. I haven't dug into VSTO yet -- that's on my list of
things to do (somebody send me a 'tuit'!). And if you plan to use XML
and/or LINQ, be sure all your users will be on Office 2007; by
comparison, the Office 2003 implementation is clunky.

Here are some points about dealing with the Word document:

1. You can't add rows to the table while the document is protected.
The code must start by unprotecting the document. Then it can add
rows, add content such as form fields to the cells, and do any
formatting. At the end, it reprotects the document.

2. In the VB editor, click Tools > References and put a checkmark on
the Word Object Library. That will give you IntelliSense for objects
in the Word model and allow type checking.

3. It's sometimes necessary, and always a good idea, to qualify all
type names with the application that's intended. For example, both
Word and Excel have Range objects, so you must qualify anything you
declare as a Range. Even when there's no ambiguity, it helps you when
you go back to do maintenance to know which application a particular
variable refers to.

4. If you're driving Word from an Excel macro, then I think neither
ADO nor DAO is appropriate. That would be for pulling data from Excel
(the server app) with a Word-based macro (the client). However, I'm
far from an expert on that.

I whomped up a little demonstration for you. I created a worksheet
with data in three columns (LastName, FirstName, and Salary) and about
a dozen rows, and set a named range ("Presidents") to cover all but
the title row. I also created a Word template named "Names.dotx" (this
is in Word 2007) that contains a table with only a header row of three
columns, and protected it for forms although it doesn't contain any
form fields. This Excel macro creates a new document in Word from the
template and then builds the table from the Excel data.

Sub demo()
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim wdTbl As Word.Table
Dim wdRow As Word.Row
Dim xlRange As Excel.Range
Dim xlRow As Excel.Range

On Error Resume Next
' try to get Word if it's open already
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Err.Clear
On Error Resume Next
Set wdApp = CreateObject("Word.Application")
End If

If Not wdApp Is Nothing Then
wdApp.Visible = True
Set wdDoc = wdApp.Documents.Add( _
Template:="Names.dotx") ' <== change
Else
MsgBox "Could not create a Word document"
Exit Sub
End If

If wdDoc.ProtectionType <> wdNoProtection Then
wdDoc.Unprotect ' optional password
End If

' the template has a table (heading row only)
' so you know there is a .Tables(1)
Set wdTbl = wdDoc.Tables(1)
For Each xlRow In ActiveWorkbook.Names _
("Presidents").RefersToRange.Rows ' <== change
Set wdRow = wdTbl.Rows.Add

wdDoc.FormFields.Add Range:=wdRow.Cells(1).Range, _
Type:=wdFieldFormTextInput
wdRow.Cells(1).Range.FormFields(1).Result = _
xlRow.Cells(1, 1).Value

wdDoc.FormFields.Add Range:=wdRow.Cells(2).Range, _
Type:=wdFieldFormTextInput
wdRow.Cells(2).Range.FormFields(1).Result = _
xlRow.Cells(1, 2).Value

wdDoc.FormFields.Add Range:=wdRow.Cells(3).Range, _
Type:=wdFieldFormTextInput
wdRow.Cells(3).Range.FormFields(1).TextInput.EditType _
Type:=wdNumberText, Format:="$#,##0.00"
wdRow.Cells(3).Range.FormFields(1).Result = _
xlRow.Cells(1, 3).Value
Next

wdDoc.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
wdDoc.Save
End Sub

This demo is a bit clumsy; it could use some cleanup and more error
checking, but it should point you in the right direction for a
VBA-only solution.

 

[macropod]

Hi Eric,

Another thing you'll have to deal with on the Word side, is unprotecting the document before populating the table, then reprotecting
the document again afterwards. Either that or you'll have to put the table in an unprotected Section of the document. That's because
the forms protection won't allow you to edit the protected portion of the document. Unprotecting & reprotecting the document is
fairly straightforward.

Since you're working with a document protected for forms, and you apparently want to import some data from an Excel workbook, have
you thought of using a LINK field and just updating the field's source range to suit the user's input in a given formfield?

Cheers

 

[Eric]

You saved me a LOT of time and aggravation. I'm down to one question: I have
(1) form field that needs to get a text value that's 308 characters, and
that gives formfield.Result a 'string too long error'. Any ideas?

Thanks again!
Eric

 

[Eric]

How does a LINK field work? What's the disadvantage of using it?

Thanks,
Eric

Hi Eric,

Another thing you'll have to deal with on the Word side, is unprotecting
the document before populating the table, then reprotecting the document
again afterwards. Either that or you'll have to put the table in an
unprotected Section of the document. That's because the forms protection
won't allow you to edit the protected portion of the document.
Unprotecting & reprotecting the document is fairly straightforward.

Since you're working with a document protected for forms, and you
apparently want to import some data from an Excel workbook, have you
thought of using a LINK field and just updating the field's source range
to suit the user's input in a given formfield?

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Hi Jay

The code will be in an Excel wbk (or addin) that the user will have open,
so I'm not worried about excel not being there. I don't have too much
data to transfer either, so I'm not concerned with speed right now. See
below for technique I would typically use to transfer data around.

The part I'm more concerned about is the mechanics of populating the Word
table, and am thinking it should go something like this
1) create a preformatted table in my protected Word do, with only a
header
2) for each row of data
2.1) add a new row to the table (not sure how to do this)
2.2) paste the row into the table (not sure how to do this)
2.3) apply any needed formatting, i.e. currency $ and commas (not sure
how to do this)

Here's how I deal with moving Excel data around
1) create a User Defined Type to act as a data container for each row
2) create the table in code using three Named Ranges
("ptrTableStartCell", "ptrTableEndCell", "ptrLastTableColumn")
3) for each rngRow in myTable.Rows
with udtMyData
.FirstName = rngRow.Cells(1, 1)
.FirstName = rngRow.Cells(1, 2)
end with

Any critique, pointers much appreciated. I like your DAO idea, but what
I'd really like to do eventually is use a 2008 VSTO based solution,
either with ADO, XML or LINQ. This would facilitate testing, reuse, and
security for both myself and the user. Unfortunately I haven't the
slightest idea how to do that now

Thanks,
Eirc

 

[macropod]

Hi Eric,

For a general discussion on how link fields work, see Word's help file.

The main (dis)advantage is that the document will be linked to the source document. So, if the data in the source file changes, so
does the Word document. You can overcome that by locking or unlinking the field when the form is saved. Conversely, if you're using
named ranges in Excel, then pointing the link to the appropriate range name permits the embedded object in Word to expand/contract
accordingly, with no additional code.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

How does a LINK field work? What's the disadvantage of using it?

Thanks,
Eric

Hi Eric,

Another thing you'll have to deal with on the Word side, is unprotecting the document before populating the table, then
reprotecting the document again afterwards. Either that or you'll have to put the table in an unprotected Section of the
document. That's because the forms protection won't allow you to edit the protected portion of the document. Unprotecting &
reprotecting the document is fairly straightforward.

Since you're working with a document protected for forms, and you apparently want to import some data from an Excel workbook,
have you thought of using a LINK field and just updating the field's source range to suit the user's input in a given formfield?

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Hi Jay

The code will be in an Excel wbk (or addin) that the user will have open, so I'm not worried about excel not being there. I
don't have too much data to transfer either, so I'm not concerned with speed right now. See below for technique I would
typically use to transfer data around.

The part I'm more concerned about is the mechanics of populating the Word table, and am thinking it should go something like
this
1) create a preformatted table in my protected Word do, with only a header
2) for each row of data
2.1) add a new row to the table (not sure how to do this)
2.2) paste the row into the table (not sure how to do this)
2.3) apply any needed formatting, i.e. currency $ and commas (not sure how to do this)

Here's how I deal with moving Excel data around
1) create a User Defined Type to act as a data container for each row
2) create the table in code using three Named Ranges ("ptrTableStartCell", "ptrTableEndCell", "ptrLastTableColumn")
3) for each rngRow in myTable.Rows
with udtMyData
.FirstName = rngRow.Cells(1, 1)
.FirstName = rngRow.Cells(1, 2)
end with

Any critique, pointers much appreciated. I like your DAO idea, but what I'd really like to do eventually is use a 2008 VSTO
based solution, either with ADO, XML or LINQ. This would facilitate testing, reuse, and security for both myself and the user.
Unfortunately I haven't the slightest idea how to do that now

Thanks,
Eirc


Eric wrote:
Any links or tips to do this most appreciated. The data would
currently come from Excel, and get there by VBA. The word doc is
protected with form filling only allowed. v2003 on both.

I posted this on office development as well, as I hadn't seen this
forum first. If there is a more appropriate forum please let me know.

TIA,
Eric

The essential idea for retrieving recordsets (rows) from Excel is shown in
http://www.word.mvps.org/FAQs/InterDev/XLToWordWithDAO.htm.

In place of the MsgBox statement in that code, you would need something like this (assuming the last name is in column A of the
worksheet and the first name is in column B):

With ActiveDocument.FormFields
.Item("LastName").Result = rs.Fields(0).Value
.Item("FirstName").Result = rs.Fields(1).Value
End With

Of course, iterating the rows with While Not rs.EOF doesn't make much sense for a single Word form unless the worksheet has
only one row.

If you get stuck, post back.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit.

 

[macropod]

Hi Eric, try something based on:

ActiveDocument.Bookmarks("Name").Range.Fields(1).Result.Text = "My really long text string"

Cheers

 

[Jay Freedman]

Also, the form has to be unprotected when you do that, the same as for
adding table rows. Details in
http://www.word.mvps.org/FAQs/MacrosVBA/SetLongFmFldResult.htm.

Hi Eric, try something based on:

ActiveDocument.Bookmarks("Name").Range.Fields(1).Result.Text = "My really long text string"

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

You saved me a LOT of time and aggravation. I'm down to one question: I have
(1) form field that needs to get a text value that's 308 characters, and
that gives formfield.Result a 'string too long error'. Any ideas?

Thanks again!
Eric