Use Excel Fields to Populate Word Document

[macropod]

Paul----thanks for that clarification! That explains why I haven't been able to make it work. I have just been grinding through making the additional links manually. JimC

The macro-based solution in the link (http://windowssecrets.com/forums/showth ... rnal-Files" onclick="window.open(this.href);return false;), does work with LINK fields, though, so that may address that aspect of your needs.

[JimmyC]

Paul---thank you. I sure have been learning allot. JimC

[JimmyC]

Paul----thanks for that clarification! That explains why I haven't been able to make it work. I have just been grinding through making the additional links manually. JimC

The macro-based solution in the link (http://windowssecrets.com/forums/showth ... rnal-Files" onclick="window.open(this.href);return false;), does work with LINK fields, though, so that may address that aspect of your needs.

Paul,
I am using the linked field approach following Hans 2nd post in this thread. I have read through the Windows Secrets post you reference and I am trying to use the AutoFldUptd macro. I created a module in Word (2010) and imported the BAS file. I then saved the Word file. Do I need to save the word file as a macro enabled file? If so, I will need to get permission from my system admin. If I must save it as macro enabled file--can it be "re-saved" as a regular word document if I ever get the links changed? Is the macro a "one time" event that I won't need once the links are changed? This may help me "sell" this internally to the system admins to run the macro once but that the Word files do not have to be permanently saved as macro enabled files. When I check the links--all the links still point to the desktop folder that contains the Excel file. I even closed the Word doc and Word---re-opened Word and then the document--but the links are unchanged.

I confirmed that the Word file is located the folder that it now needs to reside--along with the Excel file that the fields are linked to (i.e. all 25 Word files and the single Excel file are in the same drive/folder--it is a first level folder on a "shared" network drive (P:\ and the folder name is: _A_A_A_EstatePlanningDocs). As mentioned previously, the original Word/Excel links were created using a folder on my desktop and the Word files do not have any passwords, etc. There are 61 links in the document with the AutoFldUptd macro/module. I have assumed that AutoFldUptd only updates the current Word file, could it be that It updates the fields in ALL word files in the same directory? If so, there are probably 1,400 links in the 25 word files--so I would guess that would take a while. Maybe I will see if my boss will let me leave the Word file open all night to see if the macro is working on all of the 25 word documents....

I am truly "stuck" as I don't know what I have done wrong---and I do apologize if I have made a "stupid" mistake and missed something in your instructions. Also, I thought that the macro would work on "link" fields--but maybe I have misunderstood. I have 24 more Word files to change the links to the new drive/folder---all with about 60-80 links in each Word file--so I greatly appreciate any advice. Thanks for your patience and any advice. JimC

[macropod]

If you're not using the older .doc format, the document will need to be saved as a macro enabled document (.docm). An alternative would be to add the macro to a template that you attach to the documents you want to apply it to. That way, only the template needs to contain the macro and the document formats won't need changing.

The macro runs every time the document containing it is opened - that's the whole point of having such a macro. It is designed to resolve link issues where the folder paths are subject to ongoing changes. IMHO it would be overkill for a once-off exercise. The macro also only affects the active document. Over a network, latency is liable to introduce delays into the update process. You may be able to reduce those delays by applying the registry patch at: https://support.microsoft.com/en-us/kb/970154" onclick="window.open(this.href);return false;
Note that the registry subkey referred to in the article depends on the Office version:
• Office 2007 - HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Word\Options
• Office 2010 - HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Word\Options
• Office 2013 - HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Word\Options
• Office 2016 - HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Word\Options

[JimmyC]

Paul--thanks for the advice. I am using the new word format (docx). I will try the template approach on Thursday when I return to the office as the system admin refused to permit me to create macro enabled documents on a shared network drive. I am not that familiar with templates---so I will do some research but since this is a "once-off" exercise--but it looks like a painful one, can a template be easily "removed" once I run the macro once?

I tried a second time after I last posted with a macro enabled word file (docm) and let it run for 2 hours but the macro never finished which is hard for me to understand since you confirmed that the macro only functions on the active document and there were only 68 links. I know the folder name is long and somewhat odd (_A_A_A_EstatePlanningDocs) does the macro have any limitation with long file names or dealing with the underscore? Thanks for all of your help. I sure wish I knew before I created all of these links that the path was not easily modified. Take care. JimC

[macropod]

You could attach the template, run the macro, then re-attach the original template. However, for a once-off exercise, I'd be inclined to forego the macro entirely and just use Find/Replace to change the paths. To do that, simply press Alt-F9 to expose the field codes, then use Find/Replace to replace the old paths with the new one. Note that you'll need to use double backslashes as the separators. Once you've done the Find/Replace, press Ctrl-A, F9 to refresh the links.

[JimmyC]

Paul,
Thanks for the advice...I will use the Find/Replace that you recommend.

I do have a follow-up question though---just to learn. I had opened one of the 25 Word files--but not the "linked" Excel File. The "actual" linked Excel file is still on my desktop. I went to EXCEL and tried to open the same Excel file in its new location on the mapped drive. Excel refused--with a dialog box that stated that I cannot open an Excel file with the same name even if it is in a different folder. So I completely closed EXCEL. After closing Excel, when I look at the software running via Ctl-Alt-DEL--Excel is not running but when I closed the Word file, then tried to open the same Excel file on the network drive it opened.

So my question is when you open a Word document that has linked fields to an Excel file---is the Excel file actually "open" even if I cannot "see" it in the software running in task mgr.? Thanks
Jim

[macropod]

Word doesn't need to open the Excel file to refresh the links in the way you do to work on it, so there is no conflict between what Word does and whatever you might want to do with the file. Not only that, if you open the file in Excel while you have the document open in Word and you update & save the Excel file, the update will appear in the Word document.

[JimmyC]

Paul,
Thanks for the clarification...I will need to continue to investigate why I cannot open the "Same-named" Excel worksheet stored on the network drive when I have open a Word document from my desktop with links to the "same-named" Excel folder that is STORED on my desktop. I have re-booted my WIn 7 Pro PC and can duplicate this without fail. As soon as I close the Word file, I can then open the Excel worksheet from the network drive.

When you state that I need to use double backslashers as the separators is this for both the "find" and "replace" meaning that when I see the path in the document with Alt-F9 pressed---do I need to place the extra slash mark in the path once I copy it to the "find" location?

Jim

[macropod]

You need the double backslashes for both the Find and the Replace. You'll see them already being used that way once you expose the field codes.

[JimmyC]

Paul, thank you for the additional advice---I have been able to successfully use the Find/Replace as you suggested. This will save me countless hours of work as I have 26 Word files with 1,700+ linked fields. Thank you again for sharing your time and talent with others...I greatly APPRECIATE your advice. Take care. JimC

[JimmyC]

Sorry to post to an old thread; but I have a follow-up question about an issue that occurred for the first time today. To refresh everyone's memory, I have an Excel workbook where names, addresses are inputted into a worksheet and the cell data is linked to populate names, address, etc. in 30 or so Word documents. Once the Word documents are generated from the Excel worksheet and printed---I need to be able to save the documents in Word format without the Excel links and just keep whatever is populated in the document when it is saved.

We have been testing this process for six months on a small scale (i.e. mostly one customer at a time and not saving the created test documents), but I just realized that when I change the information in the Excel worksheet and open a document previously created and saved, the links are still "active" and the linked data in the Word file is changed using the most current information in the Excel worksheet thus compromising the original Word document---I need to be able to save or "preserve" the newly created Word documents when saving them that "breaks the link" so that the document will not be altered when re-opened in the future after the initial "build" from the Excel worksheet. There is a prompt that asks you if you want to update the links when Word opens a saved document and you can click, "no"---but I already had the person helping me quickly click "update the links" so I know that this terrible event will probably recur in the future.

To further complicate the issue, we now have a document management software at work and it has a "hook" into Word so that when the user clicks "file save" or "file save as" in Word 2010, the document management software fires and offers its own save screen. There is no way for the end user to by-pass this screen as the idea is that all documents must be saved into the document management software system. The document management software also automatically fires when the user selects File, Open in Word---so you cannot hold down keys when opening the document to prohibit the links from being active.

I need to be careful in breaking the Excel/Word links so that I do not or have someone else that is helping me inadvertently break the links in the "master" documents---that would mean that the links are now broke permanently going forward and we could not "build" future Word documents using the links from the Excel worksheet.

I hope that I have accurately explained this issue and not confused those fine individuals that provide advice in the lounge. Thanks for all help/advice. JImC

[Charles Kenyon]

First, use a template for your master.
http://www.addbalance.com/usersguide/templates.htm

Have people use File > New to start a new document from the master.

You could use Ctrl+A followed by Ctrl+Shift+F9 or Ctrl+6 to unlink the fields permanently or
use Ctrl+A followed by Ctrl+F11 or Ctrl+3 to lock the fields so they will not update unless unlocked.

I know this works with MailMerge. I expect it works with inserted links.

[macropod]

I just realized that when I change the information in the Excel worksheet and open a document previously created and saved, the links are still "active" and the linked data in the Word file is changed using the most current information in the Excel worksheet

That is, of course, the whole point of using that approach.

I need to be able to save or "preserve" the newly created Word documents when saving them that "breaks the link" so that the document will not be altered when re-opened in the future after the initial "build" from the Excel worksheet.

In that case, you'll need to unlink the fields - which then defeats the purpose of having the macro in the document or its template. Accordingly, you should look at using static links in a template rather than relative ones in a document and simply unlink the fields immediately they're updated upon creation of a new document. That's a trivial coding exercise. Alternatively, you could simply use a macro to populate bookmarks in each of the documents.

[JimmyC]

Paul, Charles,
Thank you for your replies. I am at the point in this project---that I "wish what I know now, I knew at the beginning of this project".

I have tried to merely re-save the docx Word document as a Word template (dotx)--but this process "breaks" all of the links. Given this finding, is my understanding correct, that all 30+ documents linked to Excel fields would need to re-done from scratch and saved as a dotx file? This would be an extensive time consuming project as most of these documents each have between 25-35 linked fields.

The idea initially was to create the "once and done" documents using the EXCEL links. While unlikely, should any future changes be required to the documents, these changes would be manually entered. But it is critical that the Word document is preserved as it was created or subsequently manually modified and NOT be changed by re-opening the document in the future should someone erroneously click on the button "update links" when the document first opens.

I found instructions on how to break the links https://wordribbon.tips.net/T010024_Wor ... Links.html. Charles, I could not get the keystokes you provided in your recent post to "break" the links. I guess my question as whether a macro could go through all open MS Word documents and break the links prior to saving them---or whether the links in each document must be broken manually per the attached weblink instructions. All 30 documents are never used as there are different versions, etc...but there could be 20 documents simultaneously created by the Excel/Word links. So if the links in all 20 documents can be broken via a macro prior to saving the document this would be of great benefit versus breaking the links in each document manually.

Thanks for all of your advice and ideas. JImC

[macropod]

So what is the process that actually generates the new Word documents? An Excel macro? Or do you just create a new document from a template (or a copy of an existing document - not recommended), then save the new document?

If you're using an Excel macro, the code to unlink the Word document could be added to that. Similarly, if you're creating a new document from a template, a Document_New macro in the template could be used to both update and unlink the fields in the new document immediately it is created.

[JimmyC]

Paul,
Here is the code that Hans was kind enough to provide to me:

Code: Select all

Sub OpenDocs()
    Dim r As Long
    Dim m As Long
    ' Find the last used row
    m = Range("C" & Rows.Count).End(xlUp).Row
    ' Loop through the rows
    For r = 2 To m
        ' Does column A contain an "X"?
        If Range("A" & r).Value = "X" Then
            ' If so, open the document whose path is in column C
            ActiveWorkbook.FollowHyperlink Range("C" & r).Value
        End If
    Next r
End Sub

I, or my assistant, populate all the data on the Excel input worksheet. One a separate sheet in the workbook, rows contain document names with columns of drop drown boxes to select items from the input sheet to include in that particular document (i.e. customer name, billing account, etc.). Once the drop box data is all selected, I place a capital "X " in column B to select those documents that I want to generate and click the macro button on this worksheet. The macro button fires Hans' code and the document path in Column C is opened. As each Word document opens, I click update link for each document (i.e., all Word files are docx extensions--Word 2010).

Once the documents are printed to send to the customer, the documents are saved--using a different file name for future reference. The document management software actually helps ensure that I , or my assistant, do not overwrite the original Word document name referenced in the Excel workbook. The Excel worksheet is not saved--so the next time it is opened, the input screen is back to its original values (i.e. Customer Name, Customer Address, etc.) not the actual customer name that was used to generate the last iteration of documents. I also inquired whether it would acceptable to print the documents to PDF and use that as future reference but my boss wants the native Word file "just in case" an unforeseen edit is required. He feels that it would be much easier to do an edit in Word format than trying to edit a PDF.

If possible, I would prefer to make the "breaking of the links" a separate macro / button. What I have learned is that if there are any typos, etc. in the original data entry on the Excel data entry worksheet--these could not be remedied if the links are immediately broke after the document is created. Perhaps a second button is added, that identifies all the documents open (i.e. indicated by an "X" in a row in Column B---just like Hans' code does) and this breaks the links in the open Word documents. This would give me and my assistant an opportunity to review the documents and to correct input data for incorrect spellings, typos, etc. in the Excel file with a simultaneous correction in the Word document prior to breaking the link.

Hopefully I have not frustrated you with my explanation...Thanks for all of your time, patience and talent to help me. I greatly appreciated it!! Jim

[HansV]

You could run the following macro from within Word; if you store it in Normal.dotm, it will be available wherever you are in Word.

The macro will unlink all fields in all open documents (so be careful!)

Code: Select all

Sub UnlinkFieldsAll()
    Dim doc As Document
    Application.ScreenUpdating = False
    For Each doc In Documents
        doc.Fields.Unlink
    Next doc
    Application.ScreenUpdating = True
End Sub

[JimmyC]

Hans---as always I am very grateful and appreciative of your assistance.

Rather than have it saved in the Normal.dotm so that it is always available---and possibly accidentally executed/used---could I save this macro in a single Word file and have it opened automatically from Excel like the other documents? Is it possible to automatically fire this macro code in Word when Word initially opens the file with the code? If so, what I would do is place a second button on the same Excel sheet and it would open the Word file with your macro code & hopefully fire the code automatically. This would be the "last" step prior to saving the documents into our document management software.

Is this even possible?

Thank you
Jim

[HansV]

Yes, it is possible.

Create a blank new document.
Activate the Visual Basic Editor (Alt+F11).
Select Insert > Module.
Copy the following slightly modified macro into the module:

Code: Select all

Sub UnlinkFieldsAll()
    Dim doc As Document
    If MsgBox("Break links in all open documents?", vbOKCancel + vbQuestion) = vbOK Then
        Application.ScreenUpdating = False
        For Each doc In Documents
            doc.Fields.Unlink
        Next doc
        Application.ScreenUpdating = True
        MsgBox "Done!", vbInformation
    End If
End Sub

Next, double-click ThisDocument under Microsoft Word Objects.
Copy the following code into ThisDocument:

Code: Select all

Private Sub Document_Open()
    Call UnlinkFieldsAll
End Sub

Switch back to Word (Alt+F11).
Save the document as a macro-enabled document (.docm).

You can now create a hyperlink to this document in your Excel sheet.