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

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...

"Scott Bass" usenet739_yahoo_com_au wrote in message
...
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

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

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


Scott Bass wrote:
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...

"Scott Bass" usenet739_yahoo_com_au wrote in message
...
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

Thanks Graham, appreciate the reply.

My original field code was:

{ DATABASE \d "C:\\temp\\test.xls" \c
"Provider=Microsoft.Jet.OLEDB.4.0;Password=\"\";Us er 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=\"\";Us er 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=\"\";Us er 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


"Graham Mayor" wrote in message
...
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

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


Scott Bass wrote:
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...

"Scott Bass" usenet739_yahoo_com_au wrote in message
...
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