none
Linking between Word and Excel is Slow RRS feed

  • Question

  • Hi everyone,

    I'm having issue with some excel links ({LINK Excel.Sheet.12} field codes) in my word document. For some reason, updating links seems extremely slow.

    I'm talking about 60 different links for now, and it can take so long as 15 mins to update them all... I've noticed though that I can cut back on that using the "Application.ScreenUpdating = False" in VBA, and also opening the excel source file before making the updates (waht is happening if I select all and update is that for each field code, the excel source file is opened, value is taken, and then the source file is closed... you can clearly see that excel is operating in the background if you try it by opening another excel file and updating the links in word). Operation time goes down to about two minutes, which is still a bit much.

    However, this are just test values, and I will need to include as much as 50 times more links. The whole idea behind this process was that it would save me time to generate my document, but it defeats the whole purpose if the updating time takes more than several hours.

    I'm wondering if anyone knows of any "tricks" such as the "Application.ScreenUpdating = False" or the "Open source file before opening".

    Any ideas?

     

    Wednesday, September 11, 2013 12:35 PM

All replies

  • The only idea I have is "don't do that"... OLE is going to be slow, up to the point of crashing your system for the amount of links you envision.

    Speaking purely as a developer, since that is the topic of this forum, I'd probably look at embedding the Excel workbooks inside the docx/docm document with information in a custom xml part about their source. The use the Open XML SDK to replace the existing workbooks (in xlsx) format with copies their source files - this before opening the document in Word.

    Or, if this is document generation (new report each week, for example), an Open XML SDK solution that just pulls in the workbooks (rather than replacing existing).


    Cindy Meister, VSTO/Word MVP, my blog

    Thursday, September 12, 2013 9:03 AM
    Moderator
  • Hi Cindy, and thanks for your reply.

    I'm technically NOT a programmer, I'm just trying to use the little I know and what I can find on internet to generate my documents faster. I only know a bit of Visual Basic, and I'm not familiar with Open XML SDK.

    What I'm trying to do here is that I need to write up a document, where each section is simply a blank template (template should be the same for all sections), and values are simply fillled in those templates. Values will obviously change for each section, but the template should be indentical from one section to the other. To get a rough idea, I'm talking about 20 to 50 sections for each document.

    The simplest option would be to directly work in Word: i.e. use the template, copy and paste it for each section and fill out the form normally. HOWEVER, the template is being issued for the first time, and is being constantly updated. So if you have a minor change (say a paragraph and a value is supposed to be moved before another paragraph) than you have to repeat that operation 20 to 50 times per documents (and we havi around 20 documents in total).

    This is why I was thinking of using excel to store the values in places that wouldn't change. Everytime you update the template though, you can still manipulate it with Links to excel cells, and once your version is final, then you can just update word to have it import all the information. 

    Can Open XML SDK do that for me? If it can, how much time (roughly of course) would i need to invest to actually come up with a word document that ould be able to do this?

    Thanks in advance,


    Jules

    Thursday, September 12, 2013 11:51 AM
  • Hi Jules

    I think I understand even less now than I did before about what you want to achieve...

    But I do think there must be a better/different way than linking to Excel spreadsheets using OLE.

    Have you considered linking in Word documents, instead? (Insert/Object/Text from file, choosing to Link) This does NOT use OLE and thus isn't such a resource hog. OTOH the updates may not be automatic, but no big deal to write a macro to force the update, if you find you need it.


    Cindy Meister, VSTO/Word MVP, my blog

    Thursday, September 12, 2013 5:24 PM
    Moderator
  • Hi Cindy,

    What I'm trying to achieve may seem odd. I would like to use two documents and merge them in a master document, I don't really care if they are Word or Excel... (the master has to be Word though).

    I'm going to try to explain with an example: say I want a summary of all information on all the employees in a company.

    Document 1 would be a formatted template with blank spaces (such as First Name: (Blank) / Last Name: (Blank), Job Title : (Blank)). This template is still under process of being updated, i.e. sometimes we realize we forgot to ask for an information (such as Middle Initial), or we want to put the Job Title before the name etc.

    Document 2 would be an Excel Workbook, with all the data (fist names, last names, job titles etc.). For each employee there is one sheet.

    The master document would combine:

    -Document 1 and Sheet 1 of Document 2 to create section 1 on employee number one. 

    -Document 1 and Sheet 2 of Document 2 to create section 2 on employee number two.

    -Document 1 and Sheet 3 of Document 2 to create section 3 on employee number three.

    -And so on. (So far I'm using bookmarks to incorporate document 1, and code field (LINK) to incorporate data from Document 2)

    This way if document 1 is updated (Say you put the job title before the name for example), we are sure that ALL sections in the master document will be updated, without having to go through the document and make the update for every single employee.

    I am basically able to link Document 1 to the master document using bookmarks, and I don't think this is too much of an issue. The process is slowed down as soon as I start importing the values from Document 2.

    Also, the nice thing about using excel is that I can simply copy and paste my section and then toggle the code fields on, and replace sheet name "Employee001" by "Employee002". Putting those values in a Word document, I would need to make a bookmark for every single value, correct? (we are talking about 3000 values roughly, and at this point typing the values directly would be quicker).

    Is this any clearer?

    I've tried inserting text from file, then selecting an excel sheet with a sheet and a cell as the range, but word tells me the document is corrupt... I assume you can only do this with text files?

    Thanks.

    Jules

    Thursday, September 12, 2013 6:42 PM