Excel merge to Word Footer displays merge field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Justine - 21 May 2004 18:38 GMT

Using VB we have an excel program which merges it's data
into a pre-formatted word template. This has worked great
for some time, but I have one user that has a problem
with the footer of his word document.

It displays his name, address, etc. perfectly where the
placeholders say it should, but the actual names of the
merge fields is displayed on his screen and prints out on
the report.

Again, I have over a thousand users who never had an
issue on all different platforms and office versions.

I have checked every Word option, like field codes, and
updated his office 2002 to the latest SP to no avail.

Can anyone assist?

Reply to this Message

Cindy M -WordMVP- - 21 May 2004 20:29 GMT

Hi Justine,

> Using VB we have an excel program which merges it's data
> into a pre-formatted word template. This has worked great
[quoted text clipped - 5 lines]
> merge fields is displayed on his screen and prints out on
> the report.

If your code doesn't specifically control this, the
application settings will have this effect. Display of field
codes is application- and not document-centric.

The user should go to Tools/options/view and turn off "field
codes". Then to Tools/Options/Print and do the same.

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

:-)

Reply to this Message

- 25 May 2004 19:15 GMT

Cindy,
That was our initial thought as well, but it has nothing
to do with the field codes -- those are separate items
all together.

I am referring to the merge fields that actual hold the
place for the data in the footer.

>-----Original Message-----
>Hi Justine,
[quoted text clipped - 27 lines]
>
>.

Reply to this Message

Cindy M -WordMVP- - 26 May 2004 16:26 GMT

> That was our initial thought as well, but it has nothing
> to do with the field codes -- those are separate items
[quoted text clipped - 3 lines]
> place for the data in the footer.
>

OK, I'm confused :-) merge fields ARE field codes...

Since this is an automated thing, it's a bit difficult to be
sure exactly what you've got, and how it's getting there.
Are you saying
- there are merge fields in the footer
- the merge is executing (to a new doc? if not, can you
get it to do that so that you can see the structures?)
- in the merge result, one sees the names of the fields
where the merge fields used to be, but no data?

The scenario this brings to mind is that the main merge
document type is catalog/directory instead of form letter,
envelope or label. Which version of Word is involved?

> >> Using VB we have an excel program which merges it's
> data
[quoted text clipped - 19 lines]
> off "field
> >codes". Then to Tools/Options/Print and do the same.

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

:-)

Reply to this Message

- 10 Jun 2004 19:58 GMT

>OK, I'm confused :-) merge fields ARE field codes...
>
[quoted text clipped - 10 lines]
>document type is catalog/directory instead of form letter,
>envelope or label. Which version of Word is involved?

Yes, there are merge fields in the footer but the field
codes option does not turn them on or off for some
reason.

Yes, the merge is executing a previously created MS Word
document - all versions of word work fine including the
version in this case - XP.

When we execute into Word we see all the data as it
should be but in the footer we see the field or merge
field codes next to the data it imported, like so:

There are four total lines
FREEDOM Investment Proposal provided by Mike Flanagan CPA
<FAFullName>
-
Raymond James Financial Services, Inc. Member NASD/SIPC
<BrokerDealer>
-
623 W. Main Street, Lenanon, TN 37087<Address>
-
615-547-5537<Phone>

What you see in those brackets are the placeholders and
the rep's name, address, phone number are what he typed
into our little excel program developed in VBA. We named
those placeholders and for whatever reason for this guy
the placeholders are visable. We don't use any of the MS
wizards or standard templates, we created our own .doc
which our Excel program dumps this information into.

Your help is greatly appreciated!

Reply to this Message

Doug Robbins - Word MVP - 11 Jun 2004 09:30 GMT

Are you actually using mailmerge for this or your own Excel program?

If the latter, what is the code?

Signature

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

>
> >OK, I'm confused :-) merge fields ARE field codes...
[quoted text clipped - 49 lines]
>
> Your help is greatly appreciated!

Reply to this Message

- 22 Jun 2004 16:25 GMT

Sub Print_Complete_Proposal()
'Andrew Keeler - Microsoft
Const wdPasteMetafilePicture As Integer = 3
Const wdInLine As Integer = 0
Const wdSeekCurrentPageFooter As Integer = 10
Const wdSeekMainDocument As Integer = 0

If Range("PrepFor") = "" Then
MsgBox ("You must open or create a client before
printing from the File menu.")
Exit Sub
End If
If Range("AEFName") = "" Then
MsgBox ("You must open or create a Finanical Advisor
before printing from the File Menu.")
Exit Sub
End If

Maximize
HideCats
HideFunds
SHADERISK
Dim WordApp As Object
Set WordApp = CreateObject("Word.Application")
Dim PreparedFor As Variant
Dim PreparedBy As Variant
Dim BrokerDealer As Variant
Dim Address As Variant
Dim Salutation As Variant
Path = ThisWorkbook.Path
'Get Data from Worksheet
Modifier = 1
PreparedFor = Range("PrepFor")
PreparedBy = Range("AEFName") & " " & Range("AELName")
BrokerDealer = Range("AffName")
Address = Range("FirmStAdd") & ", " & Range("FRCity")
& ", " _
& Range("FRState") & " " & Range("FRZip")
Phone = Range("FRPhone")
Salutation = Range("Salutation").Text
PropModel = Range("PropModel").Text
PortSize = Range("PortSize").Text
ReportDate = Range("ReportDate").Text
C1FullName = Range("CFname").Text & " " & Range
("CLname").Text
C2FullName = Range("SFname").Text & " " & Range
("SLname").Text
AsOfDate = Range("AsOfDate").Text
SaveAsName = Range("PrepFor").Value
& "_FREEDOM_Proposal"
If FileExists(SaveAsName,
Application.DefaultFilePath) = True Then
NewName = SaveAsName
Do Until FileExists(NewName,
Application.DefaultFilePath) = False
NewName = SaveAsName & "_" & Modifier
Modifier = Modifier + 1
Loop
SaveAsName = NewName
End If
Agreement = Range("Agreement").Text
Agreement2 = Range("Agreement2").Text
ModelVer1 = Range("ModelVer1").Text
ModelVer2 = Range("ModelVer2").Text
ModelDisclaimer = Range("ModelDisclaimer").Text
WordApp.Visible = True
'Print to Word
With WordApp
.Documents.Open Filename:=Path & "\FREEDOM.doc"
' .ScreenUpdating = False
.ActiveDocument.Variables("PreparedFor").Value =
PreparedFor
.ActiveDocument.Variables("Preparedby").Value =
PreparedBy
.ActiveDocument.Variables("BrokerDealer").Value =
BrokerDealer
.ActiveDocument.Variables("Address").Value =
Address
.ActiveDocument.Variables("Phone").Value = Phone
.ActiveDocument.Variables("Salutation").Value =
Salutation
.ActiveDocument.Variables("PropModel").Value =
PropModel
.ActiveDocument.Variables("PortSize").Value =
PortSize
.ActiveDocument.Variables("ReportDate").Value =
ReportDate
.ActiveDocument.Variables("C1FullName").Value =
C1FullName
.ActiveDocument.Variables("C2FullName").Value =
C2FullName
.ActiveDocument.Variables("AsOfDate").Value =
AsOfDate
.ActiveDocument.Variables("Agreement").Value =
Agreement
.ActiveDocument.Variables("Agreement2").Value =
Agreement2
.ActiveDocument.Variables("ModelVer1").Value =
ModelVer1
.ActiveDocument.Variables("ModelVer2").Value =
ModelVer2
.ActiveDocument.Variables
("ModelDisclaimer").Value = ModelDisclaimer

End With

WordApp.ActiveWindow.ActivePane.View.SeekView =
wdSeekCurrentPageFooter
With WordApp
.ActiveDocument.Bookmarks("FAFullName").Select
.Selection.TypeText Text:=PreparedBy
.ActiveDocument.Bookmarks("BrokerDealer").Select
.Selection.TypeText Text:=BrokerDealer
.ActiveDocument.Bookmarks("Address").Select
.Selection.TypeText Text:=Address
.ActiveDocument.Bookmarks("Phone").Select
.Selection.TypeText Text:=Phone
.ActiveDocument.Bookmarks("FAFullName2").Select
.Selection.TypeText Text:=PreparedBy
.ActiveDocument.Bookmarks("BrokerDealer2").Select
.Selection.TypeText Text:=BrokerDealer
.ActiveDocument.Bookmarks("Address2").Select
.Selection.TypeText Text:=Address
.ActiveDocument.Bookmarks("Phone2").Select
.Selection.TypeText Text:=Phone

'Changed below line becuase it failed on my
machine Andrew Keeler - Microsoft
'.ActiveWindow.ActivePane.Close
.ActiveWindow.ActivePane.View.SeekView =
wdSeekMainDocument
End With

>-----Original Message-----
>Are you actually using mailmerge for this or your own Excel program?
>
>If the latter, what is the code?

Reply to this Message

Doug Robbins - Word MVP - 23 Jun 2004 10:44 GMT

Why not use { DOCVARIABLE } fields in the footer in place of your markers.

Aside from that, you should use the .Range of a bookmark to insert text
rather than opening the footer pane and selecting the bookmarks.

Signature

> Sub Print_Complete_Proposal()
> 'Andrew Keeler - Microsoft
[quoted text clipped - 142 lines]
> >
> >Hope this helps

Reply to this Message