Invalid Merge Field error

[John]

Based on a lot of help I was able to create a directory linked to an Excel
table via DDE. Now, I am encountering an 'Invalid Merge Field' error when
trying to merge the document. Not all the columns in the Excel are available
even though they were when I made the template. Results based on searches in
this newsgroup for this issue basically indicate that DDE is "flaky at best"
which is what I am experiencing. (I had at one point successfully complete a
merge with this very template.)

Preferrably, a solution to the Invalid Merge Field error is available (see
note). Short of that, is there another approach for merging text or
formatted (%) numbers into the same field (one record has text, the next
record has % number etc.).

Regarding the Invalid Merge Field error:
I have data in every column and record of the Excel table populating the
merge. (this "fixed" the issue for me early on).

 

[Peter Jamieson]

Can you tell exactly which field and what content in the data source is
causing the problem? (e.g. previously you were looking for a way to
distinguish between null, numbers, Y/N etc: is it possible that the
solution you currently have is trying to process text in general rather
than numbers? Or is it a different problem?

An approach that can work if the problem is that you are not seeing the
data as it is in Excel, is to copy/paste your Excel data into Word, then
use that document as your data source. But if the problem is for example
that { = } fields cannot process the input data correctly, that is
unlikely to be a solution.

Another way to do this is to use VBA to get data from your Excel sheet
using automation, and stuff it into predefined locations in your
document, perhaps doing whatever data manipulation you need. e.g. as
long as the data values are not too long, you can assign data to Word
Variable objects and insert them using { DOCVARIABLE } fields.

Peter Jamieson

http://tips.pjmsn.me.uk

 

[John]

Peter
I believe the field that causes the issue is my 'Q3 Den' field ('Q3_Den' in
the Word merge field name) All the spaces in my Excel field (column) headers
were turned to underscores in the Word merge field name and it seems that
this in itself is not causing any issues.)

There are fields (columns) in the Excel file for Q1, Q2, Q3, Q4, and FYTD
Den. Q1 and FYTD both have real numeric or text data. Q2, Q3 and Q4 return
'N\A' based on a formula in Excel as there are no results for these periods
yet. The cell formats are custom.

Excel fields comming after (to the right of) 'Q3 Den' are no longer
available for the merge in Word. Whats more there are similar fields for the
numerator that are formatted and populated the same but not causing an issue.

I will try the pasting into Word solution but this ultimately adds a step to
the process that I hope can be avoided for the longer term.

 

[Peter Jamieson]

I suspect none of the following will help, but...

Excel fields comming after (to the right of) 'Q3 Den' are no longer
available for the merge in Word. Whats more there are similar fields for the
numerator that are formatted and populated the same but not causing

an issue.

Although hidden columns do not seem to cause a problem here (in Word
2007 at the moment - which versions of Word/Excel are you using and
which file formats (.doc/.docx/.xls/.xlsx? ) if you have any, that may
be a factor.

Word can certainly get at least temporarily confused if you start adding
columns while the merge document is open.

Also, how many columns before "Q3 Den" ?

Peter Jamieson
http://tips.pjmsn.me.uk

 

[John]

Sorry if this is posted in here twice - received a 'temporarily unavailable
error' on my last attempt.

Peter – thanks for your help
I am using Office 2007 (xlsx and docx) The Excel file was originally
created in 2003 then recently converted to 2007. The Word doc may have been
created in 2003 or 2007. I don’t remember as the rollout of 2007 is
concurrent with my building this tool.
I have no hidden rows or columns, nor am I moving, adding or deleting any.
‘Q3 Den’ was the 15th of 34 columns.
Now, for fun, I removed all spaces from column headers ‘Q3 Den’ is now
‘Q3Den’. This resulted in a successful merge once. Now the same issue is
occurring but starting at a later column ‘Intro2’ (26 of 34).

 

[Peter Jamieson]

I wish I could be precise about this, but there do seem to be more
problems with Word 2007 mailmerge when using the .docx/.xlsx formats.
I've had situations, for example, where I haven't been able to access
more than around 42 columns using DDE, but then in other situations it
works fine.

I wonder if, as an experiment, you could try saving your workbook as a
..xls and your document as a .doc and see if you encounter the same
problem (my suspicion is that if that's OK, it's more to do with the
..doc than the .xls)


Peter Jamieson
http://tips.pjmsn.me.uk

 

[John]

Peter - I did do this in the mean time and guess what - it worked. I have
some more iterations to do through this week and want to verify that it
continues to work. If so, I will post status here. For the record: I saved
the Excel file as the XLS and kept the Word file as DocX.