+ Reply to Thread
Results 1 to 8 of 8

Linking Excel Workbook to Fill Out Report in Word

  1. #1
    Registered User
    Join Date
    08-17-2013
    Location
    South Carolina
    MS-Off Ver
    Excel 2016
    Posts
    17

    Linking Excel Workbook to Fill Out Report in Word

    Is there a way to Link an Excel Workbook to a Word document and make it easily updatable if the referenced link is moved or renamed?

    Basically, I have a single Excel workbook with various tabs that calculate and create tables. A lot of the numbers that are entered and calculated appear in a variety of locations throughout the report. It is a tedious task to find and change all of the values through the report and not make any mistakes. Its a report that I will use over and over again for various projects.

    I found that I can paste everything that I want as links, but if the Excel file gets moved or renamed the links will die. So on the next project I need to start all over. Also, the file is really slow I guess because its updating the 30 links. The excel document is the same for each project just with different values.

    Is there a way to link the entire excel file and using a name manager like in an excel to excel link?

    Any help would be appreciated!

  2. #2
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,667

    Re: Linking Excel Workbook to Fill Out Report in Word

    You could add the 'Edit Links' button to Word's QAT, which will facilitate updating the links to point to the new workbook/location. Even without that the links can be updated by choosing File>Edit Links (look in the lower-right corner of the screen). If you're moving both the document and the workbook, but the path between them remains the same, see the macro solution I've posted at: http://windowssecrets.com/forums/sho...External-Files
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    Registered User
    Join Date
    08-17-2013
    Location
    South Carolina
    MS-Off Ver
    Excel 2016
    Posts
    17

    Re: Linking Excel Workbook to Fill Out Report in Word

    I have the Edit Links button on the words QAT but updating like 40 Links each time I do a report is highly tedious and defeats the purpose. It would be easier just to hand modify the data in the actual report.

    I tried using your VB code but say if I cut the Word and Excel together and paste them into a new folder each time it tries to update the links they are broken. Do I need to run the code or something? This is all new to me. I am Using Word 2013 if that makes any difference. Is it ok to rename the Module to Link_Fix?

    Also the updating process is very slow when opening the file. Would you suggest only have one link from the excel to the word and then making an OLE Link if the data repeats in the document? Would that make it faster?

  4. #4
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,667

    Re: Linking Excel Workbook to Fill Out Report in Word

    Quote Originally Posted by JNEWBURY2280 View Post
    I tried using your VB code but say if I cut the Word and Excel together and paste them into a new folder each time it tries to update the links they are broken.
    A simple way of updating the links manually is to open the document, press Alt-F9 to expose the field code, then use Find/Replace to change whatever part needs changing. Indeed, if you go changing the Excel filename, you'll need to do that (or use Edit Links) anyway.
    Do I need to run the code or something? This is all new to me. I am Using Word 2013 if that makes any difference. Is it ok to rename the Module to Link_Fix?
    As stated in the link:
    "simply extract the file from the zip archive, open up the Word VBE (Alt-F11) and import the file into the document you want to use it with, then save the document."
    There is no 'running' to be done - the macro runs automatically every time the document containing it is opened. As for renaming the Module, sure, but I can't see the point unless you already have a module of the same name in the document.
    Also the updating process is very slow when opening the file. Would you suggest only have one link from the excel to the word and then making an OLE Link if the data repeats in the document? Would that make it faster?
    If you reference the same Excel cell(s) in multiple places, it's far more efficient to insert one link to it/them, bookmark it then insert cross-references to that bookmark.

  5. #5
    Registered User
    Join Date
    08-17-2013
    Location
    South Carolina
    MS-Off Ver
    Excel 2016
    Posts
    17

    Re: Linking Excel Workbook to Fill Out Report in Word

    So if I move the file or rename it I will have to update with links regardless of have th VBA code in the document?

    I though that is what the VBA code was created to fix.

  6. #6
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,667

    Re: Linking Excel Workbook to Fill Out Report in Word

    Quote Originally Posted by JNEWBURY2280 View Post
    So if I move the file or rename it I will have to update with links regardless of have th VBA code in the document?

    I though that is what the VBA code was created to fix.
    You would really do yourself a favour if you paid attention. The VBA code fixes the links for a changed path, but not for a changed name. The whole premises upon which it works is for simulating relative paths - that's even what the thread title in the link says.

  7. #7
    Registered User
    Join Date
    08-17-2013
    Location
    South Carolina
    MS-Off Ver
    Excel 2016
    Posts
    17

    Re: Linking Excel Workbook to Fill Out Report in Word

    Ok. So basically there is no easy way to link everything together and only update one link which in turn will update the other 40.

    If the links are broken it all goes to plain text and I would not only have to update all of the links but reformat everything as well. Way easier just to do it by hand.

    It seems like Microsoft would include a Name Manger within word as it does in Excel that would allow you to create names for certain ranges that are linked to a single file. Therefore all you would need to do is update one file and all the links would be updated automatically.

  8. #8
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,667

    Re: Linking Excel Workbook to Fill Out Report in Word

    Quote Originally Posted by JNEWBURY2280 View Post
    If the links are broken it all goes to plain text
    Wrong.
    Way easier just to do it by hand.
    You're still not paying attention. I have already told you a very easy way of doing it via Find/Replace.
    It seems like Microsoft would include a Name Manger within word as it does in Excel that would allow you to create names for certain ranges that are linked to a single file.
    But Word isn't Excel and doesn't even use defined names...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Creating report from Excel to Word
    By RinorM in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 06-02-2014, 05:10 PM
  2. Header when creating report from Excel to Word
    By RinorM in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-30-2014, 03:52 PM
  3. Replies: 2
    Last Post: 03-07-2014, 11:52 AM
  4. Combined Excel & Word Report
    By Alan Stuart in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-03-2010, 03:37 PM
  5. Fill an individual report with values from a team report?
    By alymcmorland in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-13-2005, 04:39 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1