Writing to Word document from Access 2007 VBA. Trouble with merged cells.

I have a 9-column table. First, I am creating the headers of the table by merging cells together.
With doc
.Tables(1).Cell(1,1).Range.Text = "A"
.Tables(1).Cell(1,2).Range.Text = "B"
.Tables(1).Cell(1,3).Range.Text = "C"

.Tables(1).Cell(2,5).Merge MergeTo:= .Tables(2).Cell(2,6)
.Tables(1).Cell(2,5).Range.Text = "E"

.Tables(1).Cell(2,6).Merge MergeTo:=.Tables(1).Cell(2,7)
.Tables(1).Cell(2,6).Range.Text = "F"

.Tables(1).Cell(2,7).Merge MergeTo:=.Tables(1).Cell(2,8)  
.Tables(1).Cell(2,7).Range.Text = "G"
End with

Now, I need to create new rows in the table and store data within each of the 9 columns of the table. But when I try to access one of the previously merged cells (in the code below), I get an error:
The requested member of the collection does not exist.

Set rw = doc.Tables(1).Rows.Add
with rw
  .Cells(1).Range.Text = "A Value"
 .Cells(2).Range.Text = "B Value"
End with

How do you 'unmerge' the merged cells so that data can be written into each of the 9 columns?
newbie46Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Not sure I have an exact answer, but a couple of things that might help:

1. Make the word doc visible so you can see what's happening.

2. Put a break point on the line with the error and explore the collection from the debug window (ctrl/G).  ie.

? Cells(1).Range.Text

 Intellisense should be working, so you should be able to see count, etc or other properties to help you debug.

and finaly this:

.Tables(1).Cell(2,5).Merge MergeTo:= .Tables(2).Cell(2,6)

 looks like a bug; tables(2)?   Think it should be 1.

Jim.
0
newbie46Author Commented:
So are you saying that if the cells have been merged, once you create a new row within the same table, the merging no longer takes effect and it should be possible to write to each of the individual cells?
0
SheilsCommented:
I use the following codes to merge access data into word tables.

This code is used to insert data from an MS ACCESS DB into an MS WORD table

Before running the code you need a to create a word document template in the same folder as the MS ACCESS DB. The template must have a table with the number of columns that you intend to use. The table should have a header row and one “empty” row. In the “empty” row insert the bookmarks that you intend to use to merge data into the table

 
Dim cnnX As Object
Dim rst As Object

'Declare variables for ADODB connection and recordset 
Set cnnX = CreateObject("ADODB.Connection")    
Set cnnX = CurrentProject.Connection
Set rst = CreateObject("ADODB.Recordset")    
rst.ActiveConnection = cnnX
  

'Declare variables for ms Word application
Dim Wrd As New Word.Application
Dim wdDoc As Word.Document
Dim wdRange As Word.Range
 
 
'Create the sql 
strSQL="Insert SQL string"
 
 
'Open recordset for the sql
rst.Open strSql, , adOpenDynamic, adLockOptimistic
 
 
'Open the word template to merge data from the sql
Set Wrd = CreateObject("Word.Application")
MergeDoc = Application.CurrentProject.Path
MergeDoc = MergeDoc & "\MyTemplate.dot"
Set wdDoc = Wrd.Documents.Add(MergeDoc)
Wrd.Visible = True
 
On Error Resume Next
 
'------------------------------------------------------------------------------
'Now populate the ms word table with data from the recordset
 
rst.MoveLast
rst.MoveFirst
 
I = 1
 
Do Until rst.EOF
If I = 1 Then
 
'Add data to the first row to the table (the "empty" row)
Wrd.ActiveDocument.Bookmarks("fld1").Select
Wrd.Selection.Text = rst!fld1
Wrd.ActiveDocument.Bookmarks("fld2").Select
Wrd.Selection.Text = rst!fld2
'Repeat the previous two steps to merge extra columns
 
Else
'Create new row
Wrd.Selection.InsertRowsBelow (1)
Wrd.Selection.MoveUp
Wrd.Selection.MoveDown
 
'Insert Bookmarks in the new row
Wrd.ActiveDocument.Bookmarks.Add name:="fld1" & I
Wrd.Selection.MoveRight
Wrd.ActiveDocument.Bookmarks.Add name:="fld2" & I
'Repeat the previous two steps to add extra bookmarks
 
'Add data to the new row
Wrd.ActiveDocument.Bookmarks("fld1" & I).Select
Wrd.Selection.Text = rst!fld1
Wrd.ActiveDocument.Bookmarks("fld2" & I).Select
Wrd.Selection.Text = rst!fld2
'Repeat the previous two steps to merge extra columns
 
End If
I = I + 1
rst.MoveNext
Loop

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

newbie46Author Commented:
My is goal is to create the table shown in the attachment.
Doc1.docx
0
SheilsCommented:
Is the blue part the header or is that where you are going to merge the data.
What's the stuff underneath the blue part with all the tabs
0
newbie46Author Commented:
sb9,
You can ignore the blue part at the top. I want to populate the header which consists of A - L. Then, the data 1, aaa, bbbbb, etc. should appear under each column - 9 in total.
0
SheilsCommented:
OK then you can use my code to "merge" into the 9 columns. I am using the term merge quiet loosely because this code actually writes to the document and what you end up with is a static document with the data writen in. Since that you have a template created you can regenerate the table with different sets of data over and over again.

You will also need to set the reference in the vb editor becuase the word automation is early binding. I intend to convert it to late binding but have not got around to it. To change the reference do the following in you VB editor:

In tool>Reference make sure that Microsoft word object library is ticked

Then add the first row with the 9 columns. bookmark each column. Then save the document as a template. I have done that in the attached file.

Once this is done you will have to modify the code by adding in the lines as stipulated in line 62 and 69 of my code.

Change rst!fld1,rst!fld2,... to the actual names of the fields. Alternatively you can use rst.Fields(0),rst.Fields(1),rst.Fields(2),....


Doc1.dotx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SheilsCommented:
NB:- To "see" the bookmarks go to the insert tab and select bookmark. You will see the list of bookmarks in the dialog box. Select one and click goto. You will notive the cursor move to the location of the selected bookmark.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.