Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

WORD formatting lost for linked file

2 views
Skip to first unread message

Mary Ferrari

unread,
Nov 5, 2002, 10:37:26 AM11/5/02
to
We are using WORD 97 and EXCEL 97 for Windows 95. We will
soon be getting Windows XP but we are still going to have
WORD 97 and EXCEL 97. Ugh!!

For several years we have been linking data from Excel
(COPY) to Word (EDIT, PASTE SPECIAL, PASTE LINK, FORMATTED
TEXT (RTF), then manually formatting the tables, often
using styles. When you close and reopen the file (and the
TOOLS, OPTIONS, GENERAL TAB, UPDATE AUTOMATIC LINKS AT
OPEN is turned on), Word retains the formatting we
applied. However, we can change the numbers in Excel and
they will change in Word. All is well.

So, this year I created a macro to do a lot of the
formatting and it works great--and fast. So, all looks
well, and if you have the TOOLS, OPTIONS, GENERAL TAB,
UPDATE AUTOMATIC LINKS AT OPEN turned off, you can close
and reopen the file and all is well. But, then when you
do an EDIT, LINKS, UPDATE NOW (which is the same thing as
if you would have had the TOOLS, OPTIONS, GENERAL TAB,
UPDATE AUTOMATIC LINKS AT OPEN turned on), you see the
file open nicely and then the formatting go away. I just
don't understand how the macro can affect this after it is
finished running but apparently it does. Also, I am new
at this using Visual Basic to write macros and just did it
by a lot of trial and error and so I don't know how good
the macro is, but I think a lot of people do it by trial
and error.

I understand that Office XP or Office 2000 for Windows XP
has is an extra option on the EDIT, LINKS screen where you
can check a box that says something like PRESERVE
FORMATTING. A guy in another department tried checking
this box and not checking this box. When he does not
check it, he gets our results. When he does check it, he
gets the results I want.

Is there some coding in VBA to copy what the EDIT, LINKS,
PRESERVE FORMATTING feature does?

Here is my macro:

Sub ColumnsFive()
'
' ColumnsFive Macro
' Macro recorded 07/31/02 by ESD Finance
'
Selection.Tables(1).Select
' Get rid of borders
With Selection.Tables(1)
.Borders(wdBorderLeft).LineStyle = wdLineStyleNone
.Borders(wdBorderRight).LineStyle = wdLineStyleNone
.Borders(wdBorderTop).LineStyle = wdLineStyleNone
.Borders(wdBorderBottom).LineStyle =
wdLineStyleNone
.Borders(wdBorderHorizontal).LineStyle =
wdLineStyleNone
.Borders(wdBorderVertical).LineStyle =
wdLineStyleNone
.Borders.Shadow = False
End With
With Options
.DefaultBorderLineStyle = wdLineStyleSingle
.DefaultBorderLineWidth = wdLineWidth025pt
.DefaultBorderColorIndex = wdBlack
End With
' Set font size
With Selection.Font
.Name = "Times New Roman"
.Size = 8
.Bold = False
.Italic = False
End With
' Set line spacing
With Selection.ParagraphFormat
.LineSpacingRule = wdLineSpaceExactly
.LineSpacing = 8
End With
' Set column widths for 17 columns
Selection.Rows.SpaceBetweenColumns = InchesToPoints(0)
Selection.Tables(1).Columns(1).Cells.SetWidth
ColumnWidth:=InchesToPoints(2.5), RulerStyle:= _
wdAdjustNone
Selection.Tables(1).Columns(2).Cells.SetWidth
ColumnWidth:=InchesToPoints(0.2), RulerStyle:= _
wdAdjustNone
Selection.Tables(1).Columns(3).Cells.SetWidth
ColumnWidth:=InchesToPoints(0.2), RulerStyle:= _
wdAdjustNone
Selection.Tables(1).Columns(4).Cells.SetWidth
ColumnWidth:=InchesToPoints(0.6), RulerStyle:= _
wdAdjustNone
Selection.Tables(1).Columns(5).Cells.SetWidth
ColumnWidth:=InchesToPoints(0.1), RulerStyle:= _
wdAdjustNone
Selection.Tables(1).Columns(6).Cells.SetWidth
ColumnWidth:=InchesToPoints(0.2), RulerStyle:= _
wdAdjustNone
Selection.Tables(1).Columns(7).Cells.SetWidth
ColumnWidth:=InchesToPoints(0.6), RulerStyle:= _
wdAdjustNone
Selection.Tables(1).Columns(8).Cells.SetWidth
ColumnWidth:=InchesToPoints(0.1), RulerStyle:= _
wdAdjustNone
Selection.Tables(1).Columns(9).Cells.SetWidth
ColumnWidth:=InchesToPoints(0.2), RulerStyle:= _
wdAdjustNone
Selection.Tables(1).Columns(10).Cells.SetWidth
ColumnWidth:=InchesToPoints(0.6), RulerStyle:= _
wdAdjustNone
Selection.Tables(1).Columns(11).Cells.SetWidth
ColumnWidth:=InchesToPoints(0.1), RulerStyle:= _
wdAdjustNone
Selection.Tables(1).Columns(12).Cells.SetWidth
ColumnWidth:=InchesToPoints(0.2), RulerStyle:= _
wdAdjustNone
Selection.Tables(1).Columns(13).Cells.SetWidth
ColumnWidth:=InchesToPoints(0.6), RulerStyle:= _
wdAdjustNone
Selection.Tables(1).Columns(14).Cells.SetWidth
ColumnWidth:=InchesToPoints(0.1), RulerStyle:= _
wdAdjustNone
Selection.Tables(1).Columns(15).Cells.SetWidth
ColumnWidth:=InchesToPoints(0.2), RulerStyle:= _
wdAdjustNone
Selection.Tables(1).Columns(16).Cells.SetWidth
ColumnWidth:=InchesToPoints(0.6), RulerStyle:= _
wdAdjustNone
Selection.Tables(1).Columns(17).Cells.SetWidth
ColumnWidth:=InchesToPoints(0.1), RulerStyle:= _
wdAdjustNone
' Set row heights
Selection.Tables(1).Rows.HeightRule = wdRowHeightAuto
With Selection.Rows
.Alignment = wdAlignRowCenter
.AllowBreakAcrossPages = True
.SetLeftIndent LeftIndent:=InchesToPoints(0),
RulerStyle:= _
wdAdjustNone
End With
' Center $ columns and set .54 decimal tabs for amount
columns
Selection.Rows.SpaceBetweenColumns = InchesToPoints(0)
Selection.Tables(1).Columns(3).Select
Selection.ParagraphFormat.Alignment =
wdAlignParagraphCenter
Selection.Tables(1).Columns(4).Select
Selection.ParagraphFormat.TabStops.Add
Position:=InchesToPoints(0.54), _
Alignment:=wdAlignTabDecimal,
Leader:=wdTabLeaderSpaces
Selection.Tables(1).Columns(6).Select
Selection.ParagraphFormat.Alignment =
wdAlignParagraphCenter
Selection.Tables(1).Columns(7).Select
Selection.ParagraphFormat.TabStops.Add
Position:=InchesToPoints(0.54), _
Alignment:=wdAlignTabDecimal,
Leader:=wdTabLeaderSpaces
Selection.Tables(1).Columns(9).Select
Selection.ParagraphFormat.Alignment =
wdAlignParagraphCenter
Selection.Tables(1).Columns(10).Select
Selection.ParagraphFormat.TabStops.Add
Position:=InchesToPoints(0.54), _
Alignment:=wdAlignTabDecimal,
Leader:=wdTabLeaderSpaces
Selection.Tables(1).Columns(12).Select
Selection.ParagraphFormat.Alignment =
wdAlignParagraphCenter
Selection.Tables(1).Columns(13).Select
Selection.ParagraphFormat.TabStops.Add
Position:=InchesToPoints(0.54), _
Alignment:=wdAlignTabDecimal,
Leader:=wdTabLeaderSpaces
Selection.Tables(1).Columns(15).Select
Selection.ParagraphFormat.Alignment =
wdAlignParagraphCenter
Selection.Tables(1).Columns(16).Select
Selection.ParagraphFormat.TabStops.Add
Position:=InchesToPoints(0.54), _
Alignment:=wdAlignTabDecimal,
Leader:=wdTabLeaderSpaces

End Sub

THANKS.
.


Peter Jamieson

unread,
Nov 11, 2002, 7:35:52 AM11/11/02
to
I can't quickly go back to Word/Excel 97 here, but this is what happens on
Word/Excel 2000

If you use Alt-F9 to view the underlying LINK field code you should see
something like:

{ LINK Excel.Sheet.8 "pathname" "sheetname!range" \a \r }

> I understand that Office XP or Office 2000 for Windows XP
> has is an extra option on the EDIT, LINKS screen where you
> can check a box that says something like PRESERVE
> FORMATTING.

This option is available in Word XP but not in Word 2000. However, all it
seems to do is insert a switch \*MergeFormat into the link, or remove it,
which you can do without going through the Edit|Links dialog. (e.g. you
would then have

{ LINK Excel.Sheet.8 "pathname" "sheetname!range" \a \r \*MergeFormat }

If you see a \*Mergeformat you typically retain the same formattting no
matter how the formatting was applied (although Word seems to lose certain
types of formatting, e.g. formatting a table's external boundary, and
sometimes gets it wrong, e.g. using Format|Borders and Shading to apply
blocks of colour, then updating the link, can have interesting results).

If you do not use \*Mergeformat then the formatting is typically discarded
however the field is updated.

However,
a. IMO it is quite a complicated task to preserve this formatting, I do not
know how Word actually does it, and it isn't obvious to me how it would deal
with, for example, row insertions on the Excel side. So I certainly wouldn't
be surprised if Word "decides" in certain circumstances that it doesn't
understand how to combine the data with the existing formatting, and just
gets a fresh copy of the data+formatting from Excel.
b. \*Mergeformat has some specific problems with certain field types in
specific versions of Word.

--
Peter Jamieson
MS Word MVP

"Mary Ferrari" <mary.f...@seattle.gov> wrote in message
news:772e01c284e1$3e606d10$3aef2ecf@TKMSFTNGXA09...

Mary Ferrari

unread,
Nov 12, 2002, 6:13:39 PM11/12/02
to
Peter Jamieson,

Thank you so much for your reply. I am familiar with the
\*MERGEFORMAT switch because we use that on something else
that we do (changing the source of our documents every
year). Although I don't know how to use your info to
write VBA code, each time after we run the macro, we can
do TOOLS, OPTIONS, VIEW tab and check FIELD CODES, then
type \*MERGEFORMAT as you show, then SAVE, then TOOLS,
OPTIONS, VIEW tab and uncheck FIELD CODES. It works.
Fantastic!! I was so desperate.

>.
>

0 new messages