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.