Forum Discussion
OLE Links incompatible with DOCX format?? Help...
Doug_Robbins_Word_MVP I like this workaround suggestion quite a bit but after locking all the fields in the document and reopening the document in Word, it still looks for each field in Excel when opening the document and takes quite a bit of time to open the file as it tries to find the source of each OLE link. This did allow me to open the document (whereas before it would get corrupted and totally crash) but it is a very slow open - several minutes. I'm open to any other ideas/suggestions you have thank you!!
AmandaAaron Does the data come for a single Excel workbook, or from multiple workbooks?
If it is just from one workbook, if you upload a copy of the Word document with the fields locked, and a copy of the workbook, I will investigate the issue.
Doug_Robbins_Word_MVP It's very kind of you to offer to take a look at the documents. Yes, all the links are from a single Excel workbook.
I will provide the excel workbook and the Word file saved in both DOC and DOCX formats so you can see the difference in file opening time for the two formats. I believe you'll need to redirect the path of all the OLE links to the local location on your machine first, however, since the path of the links in my Word template points to my desktop - my links have absolute paths, not relative ones.
Once the links point to the right place on your computer, I believe you'll see that the DOCX file takes several minutes to open and it appears to be looking for all the links as it opens (even if you didn't have Excel running, it will be opening in the background and you can see Excel running as a task on your machine) while the DOC file opens right away without opening Excel in the background. This seems to happen even with the links locked. In my experience, the DOCX file will open and function for a while but eventually it will crash and become corrupted and the repair will involve breaking a bunch of the OLE links to workbook objects.
I don't see a way to upload these documents here, but here are links to download them:
https://www.dropbox.com/s/mepewaml4qo8zam/Aaron%20Valuation%20mixed-use%20excel%20template%20JM.xlsx?dl=0
https://www.dropbox.com/s/65tie7603rjday5/Aaron%20Valuation%20mixed-use%20Word%20template%20OLE%20links%20LOCKED.docx?dl=0 with OLE fields locked
https://www.dropbox.com/s/tgcqselylvsq5yx/Aaron%20Valuation%20mixed-use%20Word%20template%20OLE%20links%20LOCKED.doc?dl=0 with OLE fields locked
Thank you!
AmandaAaron As you may note from the attached videos, there was virtually no difference in the time taken for each of the files to open and for the links to be updated.
I had changed the number of stories from 4 to 5 and then from 5 to 4 to be sure that the fields were actually being updated.
If I were asked to set up something like this from scratch,
- In the Excel Workbook, I would set up a sheet with two columns, one for the name of each data item (in a form that could be used as a document variable) and the other for the value or text of each data item.
- In the Word Template, I would insert { DOCVARIABLE [Name of data item] } fields where each of the links are now located
- In the Word Template, I would have an AutoNew Macro that would access the Excel Workbook and set the values of each of the variables in the document to the data from teh second colum and then update the fields in the document.
You could have the Autonew macro display a dialog box in which the user could select the Excel workbook to be used, assuming that you have a different workbook for each case (property\client) with each workbook making use of the same names for each data item.
With a system like that, you would hardly have time to blink before the data from the Excel workbook appeared in the document.
Doug_Robbins_Word_MVP Thank you for looking into this on your computer. It's a mystery to me why the DOCX takes 2-3 minutes to open on my computer as it seems to check excel for all 550 OLE links in the document as it opens while the DOC does not do that. I'm surprised it didn't have the same behavior on your machine.
I'm interested in your idea about using document variables instead of OLE links - I haven't used that functionality. However, I have some questions about how it could work -
--Many of our OLE links are not for single cells of text - they are for ranges that are shown in the Word as Microsoft Excel objects with the excel formatting - I'm guessing the document variables would not work for those types of links?
--These templates are not static documents. The employees in my office are regularly creating new content in the workbook (both individual cells and ranges in excel) on any of 10 or 15 worksheets which need to be quickly linked in their word document. With the OLE links all they have to do is copy the cell or range in Excel to the clipboard and then "paste special" as a link (unformatted or as an excel object) in Word. I'm guessing the method you suggest with document variables might not be as flexible?