Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    New Lounger
    Join Date
    Aug 2014
    Posts
    10
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Word 2010 .docx model with graphs and tables imported and linked from excel for further uses

    Hi, I'm a newbie. I'm Italian so sorry for my bad English. Firstable I want to thank you for this useful forum.

    I use office 2010.
    I have a problem. I analise lots of data in Excel (file A.xlsx) than I have to produce a summary Word document (file B.docx) with tables and Graphs.
    Of course I want to keep .docx always updated so I link the tables and Graphs in Word from Excel.
    I use "past special" and "paste link like an image".
    Field LINK (ex. {LINK Excel.Sheet.12 "C:\\...).
    All it's ok.

    Now I have to do this several times for several Customers with some kind of different data.

    I copy both files (A.xlsx, B.docx) to another folder in my hard disk. I modify the Excel data (A_mod.xlsx) and I should want to keep the Word file B_mod.docx automatically updated with A_mod.xlsx data.

    I edit manually (with alt+F9) the fields strings...but nothing happened...Word still points to A.xlsx data.

    I Try method described in
    http://windowssecrets.com/forums/sho...External-Files
    but nothing happened.


    Please help me...I'm desperate.

    How can I reach my goal?
    Edit field string seemed to be a good method but it doesn't work...
    Do I maybe use a different method from "paste special"?
    Last edited by chiccomarad; 2014-08-28 at 12:56.

  2. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    Alt-F9 toggles the field codes but doesn't refresh/update the field result.

    Did you update the field after editing the field code? You do this by pressing F9 when the field is selected.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by chiccomarad View Post
    I copy both files (A.xlsx, B.docx) to another folder in my hard disk. I modify the Excel data (A_mod.xlsx) and I should want to keep the Word file B_mod.docx automatically updated with A_mod.xlsx data.

    I edit manually (with alt+F9) the fields strings...but nothing happened...Word still points to A.xlsx data.

    I Try method described in
    http://windowssecrets.com/forums/sho...External-Files
    but nothing happened.
    Changing the filenames won't have any effect unless you refresh the fields and, if you do that before updating the paths, you'll get a link error. The macro in http://windowssecrets.com/forums/sho...External-Files only updates the paths, not the filenames. It would be a waste of time to try using the field code solution there, as the additional field coding there would be lost as soon as the LINK field updates.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    New Lounger
    Join Date
    Aug 2014
    Posts
    10
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi guys, thank you very very very much for your replies. I came up just now from days of tries.

    Did you update the field after editing the field code? You do this by pressing F9 when the field is selected.
    Ok, I press F9 just after I had modified the path...it seems to work.
    Is there a method to update all fileds with one click?

    For the image pasted with "link as image" with Alt+F9 I can see the path, but for the graph pasted with "link as object" with Alt+F9 I can see nothing.
    So what is the way for updating that?

    The macro in http://windowssecrets.com/forums/sho...External-Files only updates the paths, not the filenames
    Tell me if I understand rigth: your macro is useful if I want to move the files in any location I decide. So the path will be relative and not absolute.
    It will be also fantastic. I read the article many times but I can't understand how I can use it.

    Last question: I explicate well what is my target.
    The method I use (paste special) is the best? or is there a simpler method?

    if you want I have some little files for example.

    Thanks for your help again!
    Last edited by chiccomarad; 2014-09-12 at 11:17.

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by chiccomarad View Post
    Ok, I press F9 just after I had modified the path...it seems to work.
    Is there a method to update all fileds with one click?
    Ctrl-A, F9 will update all fields in the body of a document in one go. A Print Preview will update most others.
    [quote]For the image pasted with "link as image" with Alt+F9 I can see the path, but for the graph pasted with "link as object" with Alt+F9 I can see nothing.
    So what is the way for updating that?[quote]
    That may reflect differences in wrapping - Alt-F9 only works with in-line objects.
    Tell me if I understand rigth: your macro is useful if I want to move the files in any location I decide. So the path will be relative and not absolute.
    No, the paths remain absolute, but the macro updates them.
    I read the article many times but I can't understand how I can use it.
    You simply download the code module and import it into your document's VBE, via File>ImportFile.
    Last question: I explicate well what is my target.
    The method I use (paste special) is the best? or is there a simpler method?
    I don't understand the question.
    Last edited by macropod; 2014-09-16 at 17:54.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    New Lounger
    Join Date
    Aug 2014
    Posts
    10
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks for your reply.

    So what is the way to change the link for the non in-line item pasted with "link as object"?

    I don't understand the question.
    What is the simpler way you will suggest me to reach my target? Is the method I described above the best?

    I copy both files (A.xlsx, B.docx) to another folder in my hard disk. I modify the Excel data (A_mod.xlsx) and I should want to keep the Word file B_mod.docx automatically updated with A_mod.xlsx data.

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    The macro in the link I posted handles both in-line and floating objects. If you want to do it manually, you need to use the Edit Links option, which you can access via right-clicking on the object then choosing 'Linked worksheet object'.

    If you don't want to use the macro, you'll need to edit the links manually. You can make that easier to do by adding the 'Edit Links to files' button to the QAT. That will open a dialogue that gives access to all the links in one place.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    New Lounger
    Join Date
    Aug 2014
    Posts
    10
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I attached two images of right-clicking on the object graph...can't see edit link option (in italian "modifica collegamento").
    Where go I wrong?

    I can't use your macro because I have to edit all the links to all the objects (in-line and floating) and make sure they will point to a different Excel file (before B.docx is linked to A.xlsx ----> then ----> B.docx must be linked to A_mod.xlsx).

    In the dialogue that gives access to all the links in one place (on the QAT) I have to change link for any object one by one (I have a lot of objects and it is annoying ...is there the possibility to modify all the links in one click selecting all the objects with Ctrl+left click?

    thank you very very much
    Attached Images Attached Images
    Last edited by chiccomarad; 2014-09-17 at 05:43.

  9. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Presumably, it's under:
    Modifica dati...
    Did you look?

    As for:
    In the dialogue that gives access to all the links in one place (on the QAT) ... is there the possibility to modify all the links in one click selecting all the objects with Ctrl+left click?
    No.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  10. #10
    New Lounger
    Join Date
    Aug 2014
    Posts
    10
    Thanks
    1
    Thanked 0 Times in 0 Posts
    "modifica dati" opens Excel application and let you change manually the data inside Excel...not my target unfortunately...

    Do you want my example files to try to help me? (they are reduced files with 4 or 5 objects only...my real files have 100 or more objects)

    So there no way to change link in .docx file to another .xlsx automatically, simply pointing to the different Excel file only one time.

    The structure of all my Excel files is the same (names, rows, colums... only the values data are different...)
    It seem to be not a good job from Microsoft... don't you think?

  11. #11
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Because each object is independent, Word manages each link independently. If it didn't, imagine the problems you'd have if you decided to use a different source for one of the existing objects.

    If you care to upload a copy of both the document and some sample Excel data, I can take a look at them. That said, I know this isn't a problem with Office 2010 as such. That's the version I use and I do not have the problems you say you have. So, no, I don't agree with "It seem to be not a good job from Microsoft".
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  12. #12
    New Lounger
    Join Date
    Aug 2014
    Posts
    10
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks for your reply.

    I remember that in Word 2003 (if I don't go wrong) I have to change only one time the link to .xls file which Word refers. Automatically Word changes all the internal links that point to that .xls

    So if I have three different .xls to point to I have to do only three click, indipendent to how many objects I have inside.

    By the way I understand I can't do that, but I'm happy if you would help me.

    I attached 3 files:

    A.docx must link to A.xlsx.
    Create your own the B.docx (twin of A.docx) that must point to B.xlsx.

    Please explain me the whole operations you will do. (you can writing, posting a video, etc...)

    Imagine that I have to do this for several couple of files composed by hundreds objects and images inside.

    thanks again!
    Attached Files Attached Files

  13. #13
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    All Word versions have always required all links to be updated individually. Indeed, the 'Relative Links' thread had its genesis when Office 2003 was the latest version.

    To make your A.docx file automatically update its links to point to the A.xlsx file in its own folder (regardless of what that folder might be), simply:
    1. Download the zip file from http://windowssecrets.com/forums/sho...External-Files
    2. Extract the AutoFldUpdt.bas file from the zip file
    3. Import the AutoFldUpdt.bas file into your A.docx file's VBE, via
    press Alt-F11
    select your A project in the left-hand panel
    choose File|Import File, then navigate to the folder containing the AutoFldUpdt.bas file and select it
    press Alt-F11
    4. Save the document as a macro-enabled (docm) document.
    From now on, whenever you move the A.docm file to a new folder, it will look for the A.xlsx file in that folder. The same applies to any other links you create for the A.docm file (e.g. to B.xlsx).

    See attached.
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  14. #14
    New Lounger
    Join Date
    Aug 2014
    Posts
    10
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks. This is my feedback:

    Starting Point:
    A.docx points to A.xlsx saved in folder c:\old\

    Target:
    In a new folder (c:\new\) create a B.docx (twin of A.docx) that must point to B.xlsx that has the same structure of A.xlsx but with different data inside.

    Method 1:

    1. Copy both A.docx and A.xlsx from c:\old\ to c:\new\
    2. Rename both files in B.docx and B.xlsx
    3. Open B.docx (say no at the automatic update)
    4. Press Alt+F9
    5. Using "find and substitute" edit all the links that point to A.xlsx and make sure than they will point to B.xlsx
    6. Select all pressing Ctrl+A
    7. Update all pressing F9
    8. Repeat steps 5,6 and 7 several times till all the links are OK:
    First pass updates html tables
    Second pass updates images
    etc...
    9. save all
    10. Open c:\new\B.docx and check that all the links point correctly to c:\new\B.xlsx.

    N.B.
    Floating object must be updated manually from “Edit Links to files” dialogue box.
    At the end of the process formatting of all objects remains the same (good thing).

    Tips:
    The names of all files (.docx and .xlsx), even they are located in different folders, must be different or some problems may occur.


    Method 2:

    1. Apply the macro of Macropod described above at A.docx and save A.docm in c:\old\
    2. Copy A.docm and A.xlsx in c:\new\
    3. Rename temporary with a different name (example C.xlsx) the A.xlsx file located in c:\old\
    This is important because at the first opening Word loads both files c:\new\A.xlsx and c:\old\C.xlsx and if the names of both files are the same (A.xlsx) the macro fails
    4. Open c:\new\A.docm and let the macro doing its work (it works great, thanks a lot Macropod!)
    5. Save the c:\new\A.docm with links updated
    6. Edit directly the data contained in c:\new\A.xlsx using data contained in B.xlsx (name must remain A.xlsx due to the macro)
    7. Save all
    8. Open c:\new\A.docm and check that all the links point correctly to the modified c:\new\A.xlsx
    9. Rename back c:\old\C.xlsx in c:\old\A.xlsx to prevent errors during the next openings of c:\old\A.docm.

    N.B.
    Floating object must be updated manually from “Edit Links to files” dialogue box (macro don't work).
    At the end of the process the alignment of html tables would be lost.


    Method 3: THE ONE I WOULD PREFER

    Repeat steps of method 2 but instead of edit manually data of A.xlsx you can just override the file (with the B.xlsx)

    1. Apply the macro of Macropod described above at A.docx and save A.docm in c:\old\
    2. Copy A.docm and A.xlsx in c:\new\
    3. Rename temporary with a different name (example C.xlsx) the A.xlsx file located in c:\old\
    This is important because at the first opening Word loads both files c:\new\A.xlsx and c:\old\C.xlsx and if the names of both files are the same (A.xlsx) the macro fails
    4. Rename B.xlsx (stored in any folder you want) in A.xlsx copy and paste in c:\new\ overriding existing A.xlsx file
    5. Now c:\new\A.xlsx contains data of B.xlsx
    6. Open c:\new\A.docm and let the Macro doing its work (it works great, thanks a lot Macropod!)
    7. Save the c:\new\A.docm with links updated
    8. Save all
    9. Open c:\new\A.docm and check all the links point correctly to the modified c:\new\A.xlsx
    10. Rename back c:\old\C.xlsx in c:\old\A.xlsx to prevent errors during the next openings of c:\old\A.docm.

    N.B.
    Floating object must be updated manually from “Edit Links to files” dialogue box (macro don't work).
    At the end of the process the alignment of html tables would be lost.

    This is my experience. Do you agree? I really would appreciate your comment!

    Ps: thank you very very much for you time and patience!

  15. #15
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    As I have said before, the macro is for changed paths, not for changed filenames. There is no need to change the filenames just because you change folders and, indeed, doing so breaks the update process. So:
    1. Your steps 3 & 9 in Method 2 are wrong.
    2. Your NB for method 2 regarding floating objects is wrong.
    3. There is no particular need to save the updated document per step 5 in Method 2, since the same link update runs every time the document is opened. In any event, unless you edit the document after the macro has run, there isn't anything to save.
    4. Your steps 3 & 10 in Method 3 are wrong.
    5. Your NB for Method 3 regarding floating objects is wrong.
    6. There is no particular need to save the updated document per step 7 in Method 3, since the same link update runs every time the document is opened. In any event, unless you edit the document after the macro has run, there isn't anything to save.
    7. All of your method 1 until you get to the NB ignores the other Edit Links options discussed earlier in this thread.


    Your NB for methods 2 & 3 refer to problems with the alignment of html tables. This has nothing to do with the macro. The unwanted resizing of Excel objects is a known issue. I believe the issue was resolved in Office 2010 with SP1. There is also registry key to fix it. Navigate to:
    [HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\E xcel\Options]
    Add a new DWORD value:
    QFE_Boston
    Set the new DWORD value to 1

    For Excel 2003, see: http://support.microsoft.com/kb/834009 and http://support.microsoft.com/kb/833855. Note that the issue was resolved with SP1.
    For Excel 2007, see: http://support.microsoft.com/kb/970889 and http://support.microsoft.com/kb/970941. Note that SP1 or SP2 must be installed.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Page 1 of 2 12 LastLast

Posting Permissions

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