asked on

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?

SOLUTION

Jim Dettman (EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

newbie46

ASKER

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?

Sheils

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

newbie46

ASKER

My is goal is to create the table shown in the attachment.
Doc1.docx

Sheils

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

newbie46

ASKER

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.

ASKER CERTIFIED SOLUTION

Sheils

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sheils

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.