Update field not preserving formatting

[Scott Bass]

Windows XP Professional SP 2
MS Word 2003 SP1

Hi,

I'm trying to automate a word document by reading in data that gets updated
in Excel. I'm experiencing what I think might be a bug.

Try this:

* Create an Excel spreadsheet, say with a label row, 2 columns, and 3 rows
* Open MS Word
* Select Insert --> Field --> Database --> Insert Database button --> Get
Data button
* Navigate to your Excel spreadsheet, Open, select Sheet 1 (or the proper
sheet)
* Select Insert Data, check Insert Data as Field checkbox

* Now, drag the embedded table columns to widen the first column, moving the
second column to the right.
* Also, change the font of the text to some other value. Give it a
different style if you want. Change the color.

Now, RMB the field and choose Update Field. The columns will resize to
their original width, and the font reverts to the original font.

As one more test, RMB the field, chose Edit Field. Click the checkbox
Preserve formatting during updates. Repeat the above process. Same
problem. RMB again. The Preserve formatting checkbox is unchecked again.

Lastly, change the font in the Excel spreadsheet. Still doesn't work, it's
not picking up the font from the spreadsheet.

Surely this is a bug? Please please tell me this is fixed with some service
pack I can install? If M$ Word drops all formatting whenever I update the
field, my whole approach to a particular project is screwed. I need to
define Word templates which get their data from an Excel spreadsheet, and
Word needs to preserve the formatting of the text (font, column positioning,
everything but the text itself) when the Excel data changes.

Here's hoping...

Scott

 

[Scott Bass]

Also, is there a way to format the information read in from Excel? For
example, if the cell contains 0, I might want the output to be "0.00" in one
scenario, "( 0)" in a second scenario, and "00.0 (00.0%)" in a third
scenario.

I tried adding \# "00.0" at the end of the connection string to Excel, but
that broke the read of the data from Excel.

Thanks...

 

[Graham Mayor]

Re your first question, add a \*charformat switch to the field in Word which
will cause the field to retain Word's formatting at that point.
Re this question, see http://www.gmayor.com/formatting_word_fields.htm

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


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

 

[Scott Bass]

Thanks Graham, appreciate the reply.

My original field code was:

{ DATABASE \d "C:\\temp\\test.xls" \c
"Provider=Microsoft.Jet.OLEDB.4.0;Password=\"\";User ID=Admin;Data
Source=C:\\temp\\test.xls;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
`var1`, `var2` FROM `Sheet1$`" }

I changed it to:

{ DATABASE \d "C:\\temp\\test.xls" \c
"Provider=Microsoft.Jet.OLEDB.4.0;Password=\"\";User ID=Admin;Data
Source=C:\\temp\\test.xls;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
`var1`, `var2` FROM `Sheet1$`" \*charformat }

This preserves the font, as long as the entire table is in the same font.
However, if I highlight say the second column, and give that a different
font, when I update, that column reverts to the font of the first column.
It also loses any resizing of the columns in the table. Lastly, it loses
any color changes I make to the table.

I can live with everything except the column resizing. Is there any way to
get Word to remember the column resizing?

I also tried formatting the zeros as:

{ DATABASE \d "C:\\Documents and Settings\\Scott Bass\\My Documents\\My
Data Sources\\test.xls" \c
"Provider=Microsoft.Jet.OLEDB.4.0;Password=\"\";User ID=Admin;Data
Source=C:\\Documents and Settings\\Scott Bass\\My Documents\\My Data
Sources\\test.xls;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 `zero` FROM `Sheet1$`" \# "0.00" }

but this breaks the read of the Excel spreadsheet; instead of getting (in my
case) 5 rows of 0.00's, I just get a single 0.00. Lastly, is there a way to
format each column individually, so say I read in 2 columns from Excel, I
can format column1 as $,0.00, and column2 as 0.00% ?

Thanks,
Scott