Results 1 to 9 of 9
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Font size in linked Excel range (2000)

    I know I've seen this mentioned in a FAQ or similar, but I don't know if there's a simple fix. When I link a range of Excel cells into a Word document, it appears fine until the document is reopened and the links are updated. The problem is that the default Excel font (Arial 10) is replaced during the update by a Word default size of 12 points. This, of course, throws out all the carefully formatted cells etc.

    Is there a "fix" for this problem please?

    thanks

    Alan

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Font size in linked Excel range (2000)

    Hi Alan:
    I can't duplicate your problem. When I Paste Special/Paste Link from an Excel spreadsheet, the formatting of the spreadsheet is maintained, albeit as direct formatting. Even if I change the formatting in Word, the next time I open & update the links, the Excel formatting takes over. Could you turn on the field codes in Word (Alt+F9) & post the code?

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Font size in linked Excel range (2000)

    Hi Phil

    Here's a typical link in the document:
    { LINK Excel.Sheet.8 "C:ABCAdminABC Maintenance & Accounts.xls" "BS AGM 02!R56C1:R77C9" a r * MERGEFORMAT }

    This appears as a 9 column table in Word 2000, with the font in Arial 12, rather than the 10 point size in the spreadsheet. What I seem to remember reading is that this "quirk" occurs because Word's Normal style is (by default) a 12 point font, even though it's Times New Roman.

    If I select the table (above), set the font size to 10pt, save & reopen allowing fields to update, it's back to 12 point Arial. Such is the problem.

    Alan

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Font size in linked Excel range (2000)

    Hi Alan:
    Take out the * mergeformat switch & update the fields. I'm not sure exactly why this is happening. If I apply formatting to the field result & keep the mergeformat switch, it supposed to, & does, keep my formatting. Without that switch, it should take it's formatting from the source, Excel. The fact that Word uses TNR, 12 pt, for it's normal style shouldn't change that.

    Check whether the normal style under Format/Style/normal/Modify has "automatically update" checked or whether automatically update document styles is checked under Tools/Templates & add-ins.
    Cheers,

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Font size in linked Excel range (2000)

    Thanks Phil.

    I removed the * mergeformat switch & updated the fields, but the font remains at Arial 12. The toolbar shows Normal/Arial/12 too.

    I'm not sure what these settings *should* be, but they are currently as follows:
    Check whether the normal style under Format/Style/normal/Modify has "automatically update" checked - NO
    whether automatically update document styles is checked under Tools/Templates & add-ins - YES.

    Alan

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Font size in linked Excel range (2000)

    Hi Alan:
    I should have thought about that first. UNCHECK Tools/Templates & add-ins/automatically update document styles. THAT'S why your styles change every time you open your document. It applies Word's styles from the attached template every time you open your document. It won't change direct formatting (& I suspect that the Arial is direct formatting & the size isn't). Bet this cures the problem.
    Cheers,

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Font size in linked Excel range (2000)

    OK, I'll certainly give that a shot. Meantime, something rang a bell at this end after looking at those field codes. When the Excel range is linked with a Paste Special... the default formatting suggested is HTML. This corresponds to the /h switch. I suspect that the document in question was done in an earlier Office version (95?), not offering this option, hence the /r or RTF format.

    I tried creating the link using both options, and lo & behold, the /h retained the Arial 10 font on update, but /r reverted to Arial 12! I'm sure now that this was the "bug" I'd read about, and if memory serves me correctly (always risky that) the "fix" was to select a formatting option other than RTF.

    Anyhow, independent of that, I'll try your suggestions and see how they pan out. Thanks Phil.

    Alan

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Font size in linked Excel range (2000)

    Hi Alan:
    You are right. The r switch is RTF & does result in the change. I haven't fully checked it out, but I noticed that, when using the r switch, although the Arial is brought over as direct formatting, the size isn't. That's why the style updates to size 12, but remains Arial. When you use the h switch, both the font & size are brought over as direct formatting.

    So the bug, if any, is in the switch. As far as Word's behavior with styles and direct formatting, it's acting the way it always does. I'm not very familiar with Excel, so I don't know if you can create a style name that Word doesn't have. If so, it could mean that if you want to avoid the problem, develop a style in Excel that isn't used in Word. Then Word will not update the style.

    Cheers,

  9. #9
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Font size in linked Excel range (2000)

    Hi Phil

    At the moment, the /h switch does the trick, but I think a style solution might be better longterm. I don't know much about the details of organising styles properly (in Word or Excel) but from what I've read, their correct usage is conducive to avoiding lots of problems with document interchange etc. It's something I should try to learn I think, whilst restructuring this whole series of interconnected Office documents. Thanks for your ideas.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •