Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Apr 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA to change link item (not file)

    Hello everyone,

    First every post as I am stuck and this board seems to have lots of helpful suggestions that nearly answer my problem. I am not proficient with VBA at all so apologies if I don't explain this well.

    I have an excel workbook with about 30 worksheets which are identically formatting (each one hold staff details and has various calculations about daily rates profitability etc.).

    I then have a word document into which the various tables link. I have one set up but I don't really want to set all the rest up by hand as there are tons of tables to link in for each one. I thought I could show field codes and do a replace but field codes just wont show (don't ask I have tried everything!).

    Anyways what I want to do is write a macro that takes all the links in my word document and changes each one to the new worksheet. The file name is exactly the same it is only the worksheet that needs to change (so for example linking to range "a5:B10" in sheet 2 instead of in sheet 1).

    I hope that makes sense. In all the explanations I have seen similar it shows changing the filepath not the worksheet. I know this is probably straightforward but with staff review day shortly I am up against it and I just cant seem to crack it!

    Any help gratefully appreciated,

    thanks in advance

    NRG

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi NRG,

    Probably the best way to do this is to:
    in Excel -
    • name each of the ranges in Excel.
    • copy & paste any portion of your Excel sheet into Word as a link.
    In Word -
    • make however many copies of the field you need.
    • press Alt-F9 to expose the field code.
    • change the range address in each of the LINK fields to one of your range names.
    • press Alt-F9 to restor the field display.
    • press Ctrl-A, then F9 to update the fields.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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