Excel columns missing for Mail Merge

[jcrawford]

In my Mail Merge document, I'm selecting a few columns from an Excel
spreadsheet, but when I go to More Items to select the columns I want, one of
the columns that I need is missing from the list. Any ideas on what I should
be doing to make this column available to me? THanks.

 

[Peter Jamieson]

Assuming your worksheet has a "heading row" containing "column names",
a. what is the name of the column you are looking for?
b. are the names of the columns immediately to the left and right of the
column you want shown?
c. which version of Word/Excel?

Peter Jamieson

 

[jcrawford]

Thanks for helping.
a. The name of the column is FMV (it is bold, underlined, left justified).
It's a currency field.
b. Column FMV is the last of 14 columns in the spreadsheet. The column
immediately to the left, ie, col. 13, is one of the columns that I am using,
and is text.
c. Excel 2003

 

[Peter Jamieson]

Some possibilities:
a. Did you add the FMV column after you connected your mail merge main
document to your Excel sheet?
b. what if you add one more column with some test data to the right of your
FMV column?
c. are the cells in your FMV column the result of cell formulas, or data
you typed directly into the column?

Anything else unusual you can think of about his column?

Peter Jamieson

 

[jcrawford]

Frustrating.
a. No, I didn't add the FVM field afterwards. The spreadsheet came with the
FMV column. And what I'm doing now while I'm trying to figure this out is
always starting with a new blank Word document and reconnecting to the
spreadsheet.

b. I tried adding both a text column (copy/paste an existing text column)
and another currency column (keying in about 6 of 110 rows) to the right of
the FMV col. Now none of them are available for me to select, not the FMV
col or the two I added.

c. The FMV col is a calculated field (col K - col L). But (see b.) I tried
adding a row of currency which I keyed in. It doesn't show up.

What else I can think of: 1) The spreadsheet was created via a Raiser's
Edge query and exported to Excel. 2) In order to get the currency formatted
correctly in the merge document, I did whatever I had to do in Tools. Now
when I connect to the spreadsheet, I have to Confirm Data Source, and I
select MS Exel Worksheets via DDE (*.xls).

 

[Peter Jamieson]

Frustrating.

Yes.

The FMV column is definitely column 14 (i.e. column N) ? i.e. there are no
hidden empty columns to the left of it?

Now
when I connect to the spreadsheet, I have to Confirm Data Source, and I
select MS Exel Worksheets via DDE (*.xls).

Are you seeing the FMV column when you connect using DDE?

When you connect using OLE DB (the default method) you would typically see a
list of "tables" to select from within the workbook, and would normally have
to pick a table name that corresponded to a worksheet name. Is that what is
happening in this case (if, for example, you have created a list or inserted
a database in Excel, you might see other range names that do not correspond
to complete sheets).

Peter Jamieson

 

[jcrawford]

Good morning!
a. Yes, the FMV is definitely column 14 and there are no hidden rows.

b. When I connect using DDE, I do not see the FMV column. That's the
problem, that I don't see the FMV column.

When I connect using OLE DB, I do see the list of "tables".

I did some more playing around this morning. The problem seems to occur if
I have turned on Tools/Options/Confirm Data Source. If I turn it off so that
I don't have to Confirm Data Source, the FMV column IS listed, but the
currency formatting is gone (no dollar signs or decimal points).

 

[Peter Jamieson]

Good afternoon to you too

OK, re. DDE, I wasn't sure whether you were seeing the FMV column with any
connection type.

When you turn on Tools/Options/Confirm Data Source and select the OLE DB
option, do you see the same dialog as you see when Tools/Options/Confirm
Data Source is turned off? My expectation is that you should, and that you
would see FMV either way, and that it would look the same. Can you confirm
or deny?

On the formatting front, there has always been a problem with OLE DB (and
ODBC) losing formatting that you have used in Excel. You either have to use
DDE (which doesn't work in this case) or use numeric formatting switches in
Word, e.g. change

{ MERGEFIELD FMV }

to

{ MERGEFIELD FMV \#"$,0.00" }

or something along thoselines. However, at this point I'm not sure how much
formatting you are losing. Is an amount that shows as $1,234.56 in Excel
coming across as 1234.56, 1234, or what?

Peter Jamieson

 

[Peter Jamieson]

BTW,if it does not contain confidential information, you are welcome to
despam my e-mail address (remove "KillmapS" and send me a copy of your XL
workbook, and I'll see if I can spot what is going on.

Peter Jamieson

 

[jcrawford]

Good afternoon to you too

OK, re. DDE, I wasn't sure whether you were seeing the FMV column with any
connection type.

When you turn on Tools/Options/Confirm Data Source and select the OLE DB
option, do you see the same dialog as you see when Tools/Options/Confirm
Data Source is turned off? My expectation is that you should, and that you
would see FMV either way, and that it would look the same. Can you confirm
or deny?

On the formatting front, there has always been a problem with OLE DB (and
ODBC) losing formatting that you have used in Excel. You either have to use
DDE (which doesn't work in this case) or use numeric formatting switches in
Word, e.g. change

{ MERGEFIELD FMV }

to

{ MERGEFIELD FMV \#"$,0.00" }

or something along thoselines. However, at this point I'm not sure how much
formatting you are losing. Is an amount that shows as $1,234.56 in Excel
coming across as 1234.56, 1234, or what?

Peter Jamieson

 

[jcrawford]

Well -
Tools/Options/Confirm Data Source = yes AND OLE DB --> FMV yes
Tools/Options/Confirm Data Source = no AND OLE DB --> FMV yes

I tried the numeric formatting switches, something I'd never used before,
and that is working, so that's what I'll do. It's a reasonable solution; I
just wish I'd thought about it sooner. Next time we'll know --> don't even
waste time trying to make the other work because it doesn't; confirm the data
source and use the switches.

With the switches, you have to do alt-F9 to get them and then you have to
stay in alt-F9 when you finish the merge? That's what I'm finding. I want
the merge commands/steps on the side where they were, but with alt-F9, they
go away. I can use the little icons in the toolbar though. Small price to
pay.

THANKS SO MUCH FOR ALL THE TIME YOU SPENT IN TRYING TO HELP ME FIGURE THIS
OUT!!!!!

 

[Peter Jamieson]

Next time we'll know --> don't even

waste time trying to make the other work because it doesn't; confirm the
data
source and use the switches.

It's unusual because switching to DDE is often the way to fix problems where
stuff disappears in Excel.

For future reference you may also find my page at
http://tips.pjmsn.me.uk/t0003.htm useful - I didn't refer to it before as
your problem seemed to be about another issue altogether.

With the switches, you have to do alt-F9 to get them and then you have to
stay in alt-F9 when you finish the merge? That's what I'm finding.

You can Alt-F9 then Alt-F9 again to toggle between "field codes" and "field
results/preview" mode, in any document at any time. However, precisely what
view you get when, for example, you close and re-open a document, has never
been completely clear to me. These things sometimes change in different
versions of Word. Yes, you may have to do something to get the task pane
back.

I want
the merge commands/steps on the side where they were, but with alt-F9,
they
go away.

Personally I prefer the merge toolbar but as usual it's not for everyone.
Although it doesn't provide the textual clues that the taskbar does, it does
pretty much everything else, and if you click the icons in a sequence
starting at the left hand end, you pretty much follow the sequence of the
task panes. One thing missing from the toolbar that a lot of people might
notice is that it has no way to connect to an Outlook Contacts folder -
however, gnerally speaking it's better to initiate the merge using Outlook's
facilities in that case anyway.

Glad we go there in the end!

Peter Jamieson

 

[jcrawford]

Did you get this post? The machine coughed and acted like it hadn't sent the
message.

 

[Peter Jamieson]

Yes, I think you must have posted once by accident before you typed what you
wanted.

Peter Jamieson