Mail Merge using Excel for database

[Steve Gangey]

What would make Word not see data in a field. I have 308
Rows of data and 66 fields. It is a catalog merge and the
matrix establishes what data goes in which catalog.
The data is in the field along with 20 other rows of data
within the 308 products.

The first one is seen as blank by Word and there is
clearly data in the field.

I've copied the row that Word does see as having data and
inserted it below the one in question. Turning off the
filters and using the Preview button ican scroll down
through the records. When I get to the record in question
it shows blank. The next record shows data, this is the
one I copied into the row below the one in question.
Scroll back up one and the valid data appears in the field
in question. Go up one more and then back down and it
isn't there.

Do I need to tell you this has been driving me nuts!

 

[Steve Gangey]

I was wrong about the valid data. The data from the
record under the one in question is what appears as data.

Word 2000 (9.0.2720)

Similarly, with the working record placed above the one in
question the data is temporarily mapped in place of the
real stuff.

 

[Peter Jamieson]

Do you have any double-quote characters in your data, in or near the
offending field/row? (OOr any other "unusual" characters, i.e. that do not
appear elsewhere?

Are you connecting to Excel using the default mechanism (DDE, which starts
Excel if the spreadsheet is not already open) ? It may be worth trying other
methods (Converter, or probably a better bet, ODBC) - to do that, check Word
Tools|Options|General|"confirm conversion at open", go through the
connection process again, and select themethod when offered. But you may
find that other things do not come through (dates, amounts) and will need to
have format switches in the MERGEFIELD fields in Word.

 

[Guest]

For a deeper understanding I offer the macro code
developed to create such code:
Sub pc_hard()
'
' pc_hard Macro
' Macro updated 1/15/2004 by Stephen W. Gangey
'
ActiveDocument.MailMerge.MainDocumentType = wdCatalog
ActiveDocument.MailMerge.OpenDataSource Name:= _
"S:\GangeyAssoc\sitemap_files\AS.xls",
ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=True,
AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="",
WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False,
Format:=wdOpenFormatAuto, _
Connection:="catalog", SQLStatement:="",
SQLStatement1:=""
ActiveDocument.MailMerge.EditMainDocument
ActiveDocument.MailMerge.Fields.Add
Range:=Selection.Range, Name:= _
"merchant"
Selection.TypeParagraph
ActiveDocument.MailMerge.Fields.Add
Range:=Selection.Range, Name:= _
"pc_hardhtm"
Selection.TypeParagraph
Selection.TypeParagraph
ActiveDocument.MailMerge.DataSource.QueryString = _
"SELECT * FROM S:\GangeyAssoc\sitemap_files\AS.xls
WHERE ((pc_hardhtm IS NOT NULL ))" _
& ""
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With
ChangeFileOpenDirectory "S:\GangeyAssoc\lw \"
ActiveDocument.SaveAs FileName:="pc-hard.txt",
FileFormat:=wdFormatText, _
LockComments:=False, Password:="",
AddToRecentFiles:=True, WritePassword _
:="", ReadOnlyRecommended:=False,
EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=False,
SaveFormsData:=False, SaveAsAOCELetter:= _
False

and to look at the proposed result you will need to surf
to www.mall-at-gangey.com then select computers from the
category list and then hardware. Amazon.com is the one
that keeps getting skipped over!

 

[Peter Jamieson]

and to look at the proposed result you will need to surf

to www.mall-at-gangey.com then select computers from the
category list and then hardware. Amazon.com is the one
that keeps getting skipped over!

Unfortunately it was impossible to tell from this site exactly what data you
have in the cell that is causing problems. Is it the target URL, the entrie
string for the entry, or what? The only thing I did notice is that the URL
for the image in the Amazon entry is a relative pathname starting with ../
whereas all the other ones start with http: etc.

 

[Steve Gangey]

Sample of these files are on my website at:
www.gangey.co/WordProb. Use the saveas function to put
them in the same location and you will be able to
duplicate the scenario.

In this particular file these are the variables and
locations:
Row #21 Col C (merchant) Col BP (pc_hardhtm)
merchant = <!--Amazon.com-->
pc_hardhtm = <a
href="http://www.amazon.com/exec/obidos/redirect?
tag=stephenwgange-20&path=tg/browse/-/541966/"
target="_blank"><img
src="../amazon/images/pcpho120x60.gif" border="0"
width="120" height="60"></a>

Row #51 Col C (merchant) Col BP (pc_hardhtm)
merchant = <!--Buy.com-->
pc_hardhtm = <a href="http://click.linksynergy.com/fs-
bin/click?
id=*VbPTXVcYJ4&offerid=56785.10000394&type=4&subid=0"
target="_blank"><IMG width="234" height="60" alt="We have
it all right here!" border="0"
src="http://ak.buy.com/buy_assets/affiliate/01/0100_005B.gi
f"></a><IMG border="0" width="1" height="1"
src="http://ad.linksynergy.com/fs-bin/show?
id=*VbPTXVcYJ4&bids=56785.10000394&type=4&subid=0">


In books, music, movies, and a couple of other places I
have a virtual store on my site that they are directed to
and those begin with "../amazon...."

 

[Peter Jamieson]

The problem is a missing double quote in the fo_winehtm column on the
Amazon.com row (you have 'target=:_blank"', and 'target=:"_blank"' makes it
work).

Connection via DDE is particularly sensitive to double quotes because this
connection method tends to treat them as text delimiters (despite the fact
that the real delimiters should obviously be the cell boundaries). So, e.g.
columns containing old-style feet/inches data such as 6' 4" can cause
problems. Matching them up - which is probably the correct thing for your
data anyway - seems to solve the problem but personally I would regard that
as shaky enough to want to do things another way if possible.

I did try connecting via ODBC (check the select method box in the Open Data
Source dialog box and choose the ODBC option when presented) and that also
seemed to fix the problem (whether or not I fixed the double quote problem).
However, you would need to test that more thoroughly.

 

[Steve Gangey]

It seems to work.... Thanks! I wish I knew how! Well
maybe not. Ignorace can be bliss at times!

-----Original Message-----
The problem is a missing double quote in the fo_winehtm column on the
Amazon.com row (you have 'target=:_blank"',

and 'target=:"_blank"' makes it