Merge Tables

[NickH]

Hi
One problem I have when merging the data into a word
document.

The table within ACCESS i am using to retrieve the data
for the merge into a word document. I need to split/sort
the table using the Table Field "Session ID" ,therefore
each session will have two or more records associated
with each session, and what I would like to do is to
place or merge one session per page i.e each session into
its own table on a new page possibly with a header for
each session if this is possible.

Thanks


Nick

 

[Cindy M -WordMVP-]

Hi NickH,

The table within ACCESS i am using to retrieve the data
for the merge into a word document. I need to split/sort
the table using the Table Field "Session ID" ,therefore
each session will have two or more records associated
with each session, and what I would like to do is to
place or merge one session per page i.e each session into
its own table on a new page possibly with a header for
each session if this is possible.

Look in the "Special Merges" section of my website's mail
merge FAQ. There's a short discussion on "one to many"
there, with links to sample files. I'd say the DATABASE
field approach should work for you...

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep
30 2003)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail

 

[Doug Robbins - Word MVP]

If you execute the mailmerge as a Directory or Catalog with the mergefields
in the cells of a single row table in the main document, with the SessionID
mergefield in the first cell, the following macro run on the merged document
will create a new with each SessionID in a row by itself, followed by rows
containing the items for that SessionID

' Macro to create multiple items per condition from a directory type
mailmerge

Dim source As Document, target As Document, scat As Range, tcat As Range
Dim data As Range, stab As Table, ttab As Table
Dim i As Long, j As Long, k As Long, n As Long
Set source = ActiveDocument
Set target = Documents.Add
Set stab = source.Tables(1)
k = stab.Columns.Count
Set ttab = target.Tables.Add(Range:=Selection.Range, numrows:=1,
numcolumns:=k - 1)
Set scat = stab.Cell(1, 1).Range
scat.End = scat.End - 1
ttab.Cell(1, 1).Range = scat
j = ttab.Rows.Count
For i = 1 To stab.Rows.Count
Set tcat = ttab.Cell(j, 1).Range
tcat.End = tcat.End - 1
Set scat = stab.Cell(i, 1).Range
scat.End = scat.End - 1
If scat <> tcat Then
ttab.Rows.Add
j = ttab.Rows.Count
ttab.Cell(j, 1).Range = scat
ttab.Rows.Add
For n = 2 To k
Set data = stab.Cell(i, n).Range
data.End = data.End - 1
ttab.Cell(ttab.Rows.Count, n - 1).Range = data
Next n
Else
ttab.Rows.Add
For n = 2 To k
Set data = stab.Cell(i, n).Range
data.End = data.End - 1
ttab.Cell(ttab.Rows.Count, n - 1).Range = data
Next n
End If
Next i


--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Guest]

Hi Doug

Thank you for your reply.
I will certainly give it a go

Cheers


Nick

 

[NickH]

Hi Cindy


Thank you for the reply I will have a look on your web
site.
Sorry about the mistake in my last repost.

Nick

 

[NickH]

Hello Doug

Thank you for the reply.
I will have ago with the example youhave given below.

I have had some moderate success this morning using
the "MERGE process" but it is or appears to be quite
limited in what you can do with Mail Merge.

Cheers


Nick

 

[NickH]

Hello Cindy

I have had a look at your WEB site and found some
interesting topics.
I do not know if this is the correct Discussions Group,
but I am looking for advise on Graphics.
I produced a Access database a while ago, and the general
plan or idea was to extract HPGL data from a Table using
a Third party application and with command buttons save
the graphics plot into a word document, and this works
fine although it is a bit cumbersome. In the VB program
shown later I wanted to clear the clipboard after
each "Copy Picture", so that I do not end up accidently
copy two plots the same to a word document, however it
does not appear to work now in Access 2002 or Word 2002.
That is one issue, the other issue and looking at your
WEB site, is it possible to retrieve the HPGL data within
the table and paste it into a word document as a Graphic
Plot, the third party application I am using is
Plottergeist which does the job well in producing the
graphic Plot. Here is the VB I use in Access 2002, the
program was written using Access 2000;
------------------------------------------------
Private Sub CopyPicture_Click()
'On Error GoTo Err_CopyPicture_Click
On Error Resume Next
Dim rSess As Recordset, iTemp As Integer
Dim WordObj As Word.Application
Set rSess = DBEngine(0).Databases(0).OpenRecordset
("Plot", dbOpenTable)
rSess.Index = "PrimaryKey"
rSess.Seek "=", Forms!Plot![Session ID]
If rSess.NoMatch Then
MsgBox "Invalid Session", vbOKOnly
End If
'DoCmd.Hourglass True

Set WordObj = GetObject(, "Word.Application") 'An
existing instance of Microsoft word

If Err.Number <> 0 Then
Set WordObj = CreateObject("Word.Application")
End If

WordObj.Visible = True
WordObj.Documents.Add Template:="c:\temp\plot.dot",
NewTemplate:=False 'Template in use
WordObj.Selection.GoTo What:=wdGoToBookmark,
Name:="EMI_EmissionPrecom_LogPlot_1" 'Bookmark Initial
WordObj.Selection.Paste

'Open, Empty and Close Clipboard
'No Clipboard API error Handling
Call OpenClipboard(0&)
Call EmptyClipboard
Call CloseClipboard
MsgBox "Clipboard Cleared!"

Exit_CopyPicture_Click:
Exit Sub

Err_CopyPicture_Click:
MsgBox Err.Description
Resume Exit_CopyPicture_Click

End Sub
---------------------------------------------------
Private Sub RunPlottergeist_Click()
On Error GoTo Err_RunPlottergeist_Click
Dim Freq As String
Dim Plottergeist As PltgeistX
Set Plottergeist = CreateObject("Pltgeist.PltgeistX")

'Sets Plottergeist to run in mode for resultant Plot for
this application
Plottergeist.Window_State "NORM"
HPGL_Data.SetFocus
Plottergeist.AddHPGL (HPGL_Data.Text) 'HPGL data
Originating from HP Analyzer
Plottergeist.CopyGraphicToClipBoard 'Note. Max of 12 Plots

AppActivate "Microsoft Access"
DoCmd.GoToControl "Signal Pass/Fail"
'Freq.SetFocus

MsgBox ("Click To Close Plottergeist")
Plottergeist.CloseApp

Exit_RunPlottergeist_Click:
Exit Sub

Err_RunPlottergeist_Click:
MsgBox Err.Description
'Resume Exit_RunPlottergeist_Click

End Sub

The Second Routine above runs the third application
retrieves data and paste's the graphic plot to the
clipboard, where it remains, the first routine shown
above opens a template for a new document and pastes the
plot from the Clipboard into a new titled document the
clipboard should then clear, although it appears no to
now.

Any help or advice on the above would be gratefully
appreciated.

Best Regards

Nick

 

[NickH]

Hello Doug

I have tried the example code below, but having
difficulties with one of the lines..


Set stab = source.Tables(1)

How is this set? the error I obtain is "5941"
The request member of the collection does not exsit.
I have tried inserting the name of the table but with no
success.

I would be grateful for any help,


Nick

 

[Cindy M -WordMVP-]

Hi NickH,

I do not know if this is the correct Discussions Group,
but I am looking for advise on Graphics.

I'm not sure this is the right group, either

. In the VB program
shown later I wanted to clear the clipboard after
each "Copy Picture", so that I do not end up accidently
copy two plots the same to a word document, however it
does not appear to work now in Access 2002 or Word 2002.

What doesn't work, more exactly?

is it possible to retrieve the HPGL data within
the table and paste it into a word document as a Graphic
Plot, the third party application I am using is
Plottergeist which does the job well in producing the
graphic Plot.

I'm not sure what you're asking, here. Certainly, if your
code can copy something somewhere else, you should be able
to paste it into Word...

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update
Sep 30 2003)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail

 

[Guest]

Hello Cindy

Perhaps I have confused the issue a bit.

Having looked at the code again, there appears to be a
problem with clearing the clipboard after the graphic has
been pasted to Word, the latter works find.
The main reason for wanting to clear the clipboard after
each copy and paste, was just to make life a little
easier when copying more graphic plots than the Clipboard
can handle "24".

I was interested in trying the "Merge in a picture from a
database", I tried this out but when producing a new
document although a box appears where the picture should
be, the graphics does not. I can only assume I am doing
something wrong.
I go though the "5" steps as described on your WEB page,
the database makes a reference in a table to graphic
plots (*.WMF)in a subdirectory, then using the MergeField
Description:

{ IncludePicture "C:\\Data\\Pics\\{ Mergefield Signature
\* upper }" }

as
{ IncludePicture "C:\\Temp\\{ Mergefield Description \*
upper }" }

I end up with the result described earlier, but no image.

If I then toggle on the field codes I obtain:
{INCLUDEPICTURE "C:\\TEMP\\c:\Temp\Plt1.wmf" }

I assume this is correct the "Mergefield" is finding the
Graphics image in the "Temp" directory, could it be
something to do with the type of file?

Any help would be most appreciated.

Regards


Nick

 

[Cindy M -WordMVP-]

Hi Nick

I'm having problems replying to your most recent message,
so I'm trying it as a "Reply to" to this, your original,
question...

I end up with the result described earlier, but no image.

If I then toggle on the field codes I obtain:
{INCLUDEPICTURE "C:\\TEMP\\c:\Temp\Plt1.wmf" }

I assume this is correct the "Mergefield" is finding the
Graphics image in the "Temp" directory, could it be
something to do with the type of file?

The problem here is that you're repeating the file path
(and with single backslashes) in the merge field. Best
would be to have only the file name in the mergefield. If
you need to store the entire path for some reason, then you
need to store it with double backslashes for the merge.

Why double backslashes? Because in Word fields the
backslash precedes "control characters" (switches). So you
need to double them up to tell Word to recognize the
backslash as a literal character.

In the merge result doc, the field code should be:

{INCLUDEPICTURE "C:\\TEMP\\Plt1.wmf" }

Try editing one so that it looks like this, press F9 while
the cursor is in the field, then Alt+F9 and view the
result.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update
Sep 30 2003)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail

 

[NickH]

Hi Cindy

Thankyou for the reply, yes it appears to work fine I can
include a picture in the new document.
The problem I have is if you have several Graphic images
to insert into the new document, I have them in a data
file as:

Table

Number Description
1 Plt1.wmf
2 Plt2.wmf
3 Plt3.wmf
etc etc

When I try to run a full merge in a new document, I
Obtain the correct number of Images , but they are all
the same image,i.e. "Plt1.wmf", so I know I am doing
something wrong, could you please advise me.

p.s. on a different note, we do have problems with the
email, its how they have the email server set up!, also I
have been on to your WEB site from Home,and found some
helpful things.This I cannot do from my place of work
since they have a security Filter stopping us from
entering some WEB sites."Very annoying at times"

Thanks

Nick

 

[Cindy M -WordMVP-]

Hi NickH,

When I try to run a full merge in a new document, I
Obtain the correct number of Images , but they are all
the same image,i.e. "Plt1.wmf"

Have you looked in the field codes? Is the same file
name in all the fields? Or did you not do Ctrl+A, F9 to
force the fields to update?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update
Sep 30 2003)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail

 

[NickH]

Hello Cindy

That appears to do the trick, thank you.

with the following string

{MERFEFIELD Number}
{IncludePicture "c:\\Temp\\{MERGEFIELD Description}" /d}

Why is it that when I make the new document, the numbers
field displays the numbers ascending 1 to 4, immediately
but to obtain the correct images 1 through to 4 I need to
use Ctrl A and F9 to update?
Not to worry though, you have resolved the problem I had

Thanks


Nick

 

[Cindy M -WordMVP-]

Hi NickH,

Why is it that when I make the new document, the numbers
field displays the numbers ascending 1 to 4, immediately
but to obtain the correct images 1 through to 4 I need to
use Ctrl A and F9 to update?

Mail merge updates the fields as it executes, but before the
IncludePicture field (and other nested fields, such as Link
or IncludeText) is populated If you were wanting to set
something up for non-savvy users, or to save time/steps then
I'd combine the execution and field update in VBA code.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep
30 2003)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail