Database field merge in word

[Bev]

Hi... I have a word document with a database field picking up an excel
spreadsheet. However, where there are blanks in a column of numbers
in the excel spreadsheet, the merge picks up a 0 (zero) instead of the
required blank. Any ideas on how I can stop this happening? Thanks,
Bev

 

[Graham Mayor]

From the Tools menu in Word, select Options and then go to the General tab
and check the box against the "Confirm conversions at open" item. (In Word
2007 it is Office Button > Word Options > Advanced > General > Confirm file
format conversion on open). Then when you select the data source, you will
be given the option of using the DDE method of connection which should read
the data as you have it formatted in the table.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Bev]

Thanks Graham - but I think to do this I would have to start from
scratch - it affects a number of docs each with a number of database
fields..... can't seem to apply it retrospectively .. and can't afford
to go back and redo them all. I think I must change the excel source
file or something.

Thanks again though

 

[Doug Robbins - Word MVP]

All you would have to do would be to reattach the data source to the
documents.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Peter Jamieson]

When you say "a database field", do you mean
a. a { MERGEFIELD } field that inserts one piece of data during a mailmerge
operation or
b. a { DATABASE } field that inserts an entire table in one go?

 

[Bev]

Hi there Peter, I mean b. a { DATABASE } field that inserts an
entire table in one go. Dougs method has worked on new doc but I seem
to mess up my IF statements when changing the datasource on existing
docs as Doug has suggested, but this is more down to my inability than
Dougs suggestion I'm sure! Bev

 

[Peter Jamieson]

For one reason and another, I have to assume you are using Word XP (2002) or
later. If you were using Word 2000 before, that would at least explain why
you are suddenly getting this problem.

In Word 2002 and later, you will not be able to get data from an Excel sheet
via DDE in a DATABASE field. That probably means that your database field
will be using OLE DB to get its data - if you insert a new database field,
then use Alt-F9 to show the contents of the database field, it will probably
be something like the following:

DATABASE \d "the full path name of your xls file with \\ separators" \c
"Provider=Microsoft.Jet.OLEDB.4.0;Password=\"\";User ID=Admin;Data
Source=the full path name of your xls file with \\
separators;Mode=Read;Extended Properties=\"HDR=YES;IMEX=1;\";Jet
OLEDB:System database=\"\";Jet OLEDB:Registry Path=\"\";Jet OLEDBatabase
Password=\"\";Jet OLEDB:Engine Type=35;Jet OLEDBatabase Locking Mode=0;Jet
OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet
OLEDB:New Database Password=\"\";Jet OLEDB:Create System Database=False;Jet
OLEDB:Encrypt Database=False;Jet OLEDBon't Copy Locale on
Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet
OLEDB:SFP=False" \s "SELECT * FROM `Sheet1$`" \h

There will be some differences if you are using Word 2007.

When you get data from Excel via OLE DB, you are at the mercy of the OLE DB
provider, which tries to decide what data type is in each column and convert
everything else to that type. Although the "rules" for this are not
straightforward, blanks are only likely to be returned as 0 if
a. Excel has decided that the column they are in is numeric (which probably
means that all the first 8 cells in that column are numeric)
b. the cell either has at least one blank in it (i.e. is not completely
empty) - or perhaps the cell is getting data from elsewhere.

Unfortunately, I think your only choices
a. are "fix the data" (i.e. if there are cells with " " instead of "",
change their contents to "" or "Clear" them)
b. modify the SQL in each { DATABASE } field to process any affected
columns specially. e.g. instead of

SELECT * FROM `Sheet1$`

if you have five field f1,f2,f3,f4,f5 and f4 has the problem, try e.g.

SELECT f1,f2,f3,iif(trim(cstr(f4)) = '','',f4) as [f4], f5 FROM [Sheet1$]

where those '' in the mddle are each two single quotes.

Neither of those things is likely to be very practical in the situation you
describe, but they are theonly things I can think of, except for the
possibility that you do your merges in two steps, i.e.
a. select, then copy/paste your Excel data into a Word document
b. use that as the data source

and even that is likely to cause problems, especially if your sheet has more
than 63 columns.

 

[Bev]

Hi Peter - thanks muchly for your advice. I will have to wait now
until tomorrow when hopefully my brain will be slightly refreshed to
try out your suggestion. Thanks for your time. I will feedback my
result ... thanks & goodnight! Bev