Prevent word from accessing excel file on open
in [Word VBA]
|
Prev: Selecting some text in a Word table Cell
Next: There is no other way but using a password recovery software
From: Nick Fejer Nick on 5 May 2010 03:15 Hello, I am using word 2007, excel 2007 on a windows XP machine with all updates done. I have a word file that has links to excel file. These are {Link Excel.Sheet.8 ......} fields. None of the fields have the \a switch - i.e. all links are set to manual update. The word switches "Update Automatic Links on Open", "Update fields before printing" and "Update Linked data before printing" are all unchecked. I am trying to prevent the word file from accessing the excel data at all before i do some management in a Sub AutoOpen() routine. Specifically we have excel files that are accessed over out intranet via mapped drives - and the service is not the quickest. In the case whereby the links refer to a excel file over a networked drive, and the file is not opened prior to opening hte word document, it's a very slow process. Looking at Process explorer it seems that for each link, Excel is opened then closed again. However I cannot seem to stop these file accesses. Is there anything I can do to prevent word from accessing a file at all on opening?
From: Doug Robbins - Word MVP on 5 May 2010 04:22 You could use Ctrl+F11 to lock the field and use Ctrl+Shift+F11 to unlock it so that it can be updated when required -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Nick Fejer" <Nick Fejer(a)discussions.microsoft.com> wrote in message news:F7590D68-CD91-4327-BF74-85A44A88EEDC(a)microsoft.com... > Hello, > > I am using word 2007, excel 2007 on a windows XP machine with all updates > done. > > I have a word file that has links to excel file. These are {Link > Excel.Sheet.8 ......} fields. None of the fields have the \a switch - i.e. > all links are set to manual update. > The word switches "Update Automatic Links on Open", "Update fields before > printing" and "Update Linked data before printing" are all unchecked. > > I am trying to prevent the word file from accessing the excel data at all > before i do some management in a Sub AutoOpen() routine. > Specifically we have excel files that are accessed over out intranet via > mapped drives - and the service is not the quickest. In the case whereby > the > links refer to a excel file over a networked drive, and the file is not > opened prior to opening hte word document, it's a very slow process. > Looking > at Process explorer it seems that for each link, Excel is opened then > closed > again. > > However I cannot seem to stop these file accesses. Is there anything I can > do to prevent word from accessing a file at all on opening?
From: Nick Fejer on 5 May 2010 20:28 Hi Doug, thanks for your thoughts. Even when the fields are locked, when I open the document word accesses the file - but it does not update the data. When I lock the fields then I cannot update the data. I'm talking about a different characteristic - just of Word accessing the file. I can see it as a process under svchost.exe - Excel process is spawned once for every link that is in the document. I'm guessing that Word (using the file system) establishes the links to the relevant data in the excel document, but does not update the displayed data. So in the case a link type is set to Auto, when you open the Word document, word: a) establishes the links to the data in the excel file b) gets the new data c) inserts it in the word document In the case where the link type is set to manual, when you open the Word document, word: a) establishes the links to the data in the excel file b) then does nothing I also guess that when word does establish the links is when the right context button menu changes. When word has established the links, the menu becomes "Update links" and "Linked Worksheet Object ->". But when links are not established (for example broken), the menu is "Object" I am trying to stop the initial establishment of the links. And so I would like to prevent Word from doing any type of file access on the links as it opens. Is there any way of doing this? (Sorry for the long post - I didn't have time to write a short one) "Doug Robbins - Word MVP" wrote: > You could use Ctrl+F11 to lock the field and use Ctrl+Shift+F11 to unlock it > so that it can be updated when required > > -- > Hope this helps. > > Please reply to the newsgroup unless you wish to avail yourself of my > services on a paid consulting basis. > > Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
From: Doug Robbins - Word MVP on 5 May 2010 21:12 Right, I came up against that issue with an application I developed a couple of years ago where the user had hundreds of links to data from Excel in a large Word document. The way that we got around it was to use docvariable fields in the Word document in place of link fields and use code to set/refresh the values of the variables to the relevant data in the Excel spreadsheet. For that particular application, it was a perfectly acceptable work around. Whether it will work for you would depend on your situation. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Nick Fejer" <NickFejer(a)discussions.microsoft.com> wrote in message news:9B6390BD-3CB6-4B58-B675-60C3EDA7C17F(a)microsoft.com... > Hi Doug, > > thanks for your thoughts. Even when the fields are locked, when I open the > document word accesses the file - but it does not update the data. When I > lock the fields then I cannot update the data. > > I'm talking about a different characteristic - just of Word accessing the > file. I can see it as a process under svchost.exe - Excel process is > spawned > once for every link that is in the document. > I'm guessing that Word (using the file system) establishes the links to > the > relevant data in the excel document, but does not update the displayed > data. > So in the case a link type is set to Auto, when you open the Word > document, > word: > a) establishes the links to the data in the excel file > b) gets the new data > c) inserts it in the word document > > In the case where the link type is set to manual, when you open the Word > document, word: > a) establishes the links to the data in the excel file > b) then does nothing > > I also guess that when word does establish the links is when the right > context button menu changes. When word has established the links, the menu > becomes "Update links" and "Linked Worksheet Object ->". But when > links > are not established (for example broken), the menu is "Object" > > I am trying to stop the initial establishment of the links. And so I would > like to prevent Word from doing any type of file access on the links as it > opens. > Is there any way of doing this? > > (Sorry for the long post - I didn't have time to write a short one) > > > > "Doug Robbins - Word MVP" wrote: > >> You could use Ctrl+F11 to lock the field and use Ctrl+Shift+F11 to unlock >> it >> so that it can be updated when required >> >> -- >> Hope this helps. >> >> Please reply to the newsgroup unless you wish to avail yourself of my >> services on a paid consulting basis. >> >> Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
From: Nick Fejer on 6 May 2010 21:04
Thanks Doug. |