"You cannot open two documents with the same name" problem and how to trap the error in VBA.
in [Word VBA]
|
Prev: marvendas@gmail.com Kit completo de Solenoides ( solenoid ) + chicotePara Cambio automatico 01M hidramatico Audi A3 Vw Golf gti turbo 90715
Next: Editing Word 2007 menu that was created in Word 2003
From: Anthony on 20 Feb 2010 23:54 On Feb 20, 5:45 pm, "Doug Robbins - Word MVP" <d...(a)REMOVECAPSmvps.org> wrote: > Please do not post the same question separately to multiple newsgroups. > > I came across what is essentially the same issue in an application that I > had first developed for versions before 2007 when it was moved to that > version and the way that I got around it was instead of using links, insert > DOCVARIABLE fields in the document and use vba code to set the values of the > variables to the data in the Excel spreadsheet. > Doug, Sorry about that. I thought when more people see it, the more chance I will have people answer it. Thank you for sharing your method. Could you tell me how did you insert the DOCVARIABLE fields and use vba code to set the values of the variables to the data in the Excel spreadsheet? Thanks. Anthony
From: Anthony on 20 Feb 2010 23:56 And by the way, is there a why using VBA to replace the link field code with the DOCVARIABLE field code? Thanks. Anthony
From: Doug Robbins - Word MVP on 21 Feb 2010 01:25 There is nothing wrong with posting to multiple newsgroups IF you do it by inserting those newsgroups into the header of the one post. When you do that, when the message is read in one newsgroup, it is then marked as read in all of the newsgroups and if it is answered in one of the groups, the answer will also appear in all of the newsgroups. That not only makes it easier for you to find an answer to your post (you only have to look in one place) but it can also minimise the possibility of someone creating an answer to a post in one newsgroup only to find that someone else has already provided an answer in another newsgroup Are all of the links to the one workbook? Are they all to the cells on one sheet in that spreadsheet? Or do the links come from multiple sheets in multiple workbooks? The following code will convert a Link field to a Docvariable field and assign a value to it. (It got a bit complicated by having to replace all of the (illegal as far as a docvariable name is concerned) characters from the address in the Link). Rather than just assigning a static value to the variables as I did (for test purposes), it would be possible to convert the name of the variable back to the address of the cell in the Excel spreadsheet and get the value of that cell using the method provided previously. It is going to be a lot simpler however if all of the linked cells are from the one spreadsheet. Dim i As Long Dim lfcode As Range Dim strcode As String With ActiveDocument For i = 1 To .Fields.Count Set lfcode = .Fields(i).Code strcode = Replace(lfcode.Text, Chr(34), "chr34") strcode = Left(strcode, InStr(strcode, " \")) strcode = Replace(strcode, "LINK ", "") strcode = Replace(strcode, ".", "chr46") strcode = Replace(strcode, "!", "chr33") strcode = Replace(strcode, ":", "chr52") strcode = Replace(strcode, "\", "chr92") strcode = LTrim(strcode) strcode = RTrim(strcode) strcode = Replace(strcode, " ", "_") MsgBox strcode lfcode.Text = "DOCVARIABLE " & strcode Next i .Variables(strcode).Value = "test" .Range.Fields.Update End With -- 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 "Anthony" <excelmodeling(a)gmail.com> wrote in message news:29f3f3fb-0437-43c9-8828-4db9b8ff8889(a)f42g2000yqn.googlegroups.com... > On Feb 20, 5:45 pm, "Doug Robbins - Word MVP" > <d...(a)REMOVECAPSmvps.org> wrote: >> Please do not post the same question separately to multiple newsgroups. >> >> I came across what is essentially the same issue in an application that I >> had first developed for versions before 2007 when it was moved to that >> version and the way that I got around it was instead of using links, >> insert >> DOCVARIABLE fields in the document and use vba code to set the values of >> the >> variables to the data in the Excel spreadsheet. >> > > Doug, > > Sorry about that. I thought when more people see it, the more chance > I will have people answer it. > > Thank you for sharing your method. Could you tell me how did you > insert the DOCVARIABLE fields and use vba code to set the values of > the > variables to the data in the Excel spreadsheet? > > Thanks. > > Anthony >
From: Anthony on 21 Feb 2010 15:22 Thank you, Doug, for your info and code again. If it is necessary to post in multiple groups, I would put in the subject something like this: "Help with link problem and how to trap the error in VBA (also post in work.newusers, work.vba.beginner, and work.vba.general)"? So the main idea of the code is to replace the "LINK" in the field codes with "DOCVARIABLE" and make it a "DOCVARIABLE" field instead of a link field? I used similiar code to replace the folder path in the field code. Thank you for asking the following questions: Are all of the links to the one workbook? Are they all to the cells on one sheet in that spreadsheet? Or do the links come from multiple sheets in multiple workbooks? That is very considering of yours. 99% of the links are link to the same workbooks, where as the other 1% are not. The links in that 1% are purposely ignored. I do have some IF conditional statement to take care of this so that only the 99% portion is modified. (There are also PAGEREF, table of content links, in the files. They are ignored too.) I will try your code and let you know how it works out. Despite the fact that we may switch the link to DOCVARIABLE type, before we even do this, the alert message will continue to come up when the file is opened. One will need to click throght the [OK] button in each of the popped up message in order to proceed further. As I have mentioned previously, to avoid this, either use a doc file (2003 version) or run the VBA file in Word 2003. (Even the VBA file is developed in Word 2007 and was saved in docx, it can still be loaded and run in Word 2003 - if there is no 2007 functionality in it.) By the way, should I post the link of this thread in the threads that I posted in mutliple locations, so that people know that the answer has been provided? Anthony
From: Doug Robbins - Word MVP on 21 Feb 2010 20:23
I do not know how you post simultaneously to multiple newsgroups using Mozilla. In Outlook Express/Windows Mail/Windows Live Mail, you select all of the Newsgroups to which you want to post by clicking on the Newsgroups button in the header of the message and in the dialog that then appears, you select (using the Ctrl key) each one and then click on the Add button. You can limit the application of the code that changes Link fields to DocVariable Fields by incorporating the following into the code: If ActiveDocument.Fields(i).Type = wdFieldLink Then 'other commands End if -- Hope this helps, Doug Robbins - Word MVP Please reply only to the newsgroups unless you wish to obtain my services on a paid professional basis. "Anthony" <excelmodeling(a)gmail.com> wrote in message news:e04aae8e-9ee4-4edd-a63c-02151ec3f154(a)k11g2000vbe.googlegroups.com... > Thank you, Doug, for your info and code again. > > If it is necessary to post in multiple groups, I would put in the > subject something like this: "Help with link problem and how to trap > the error in VBA (also post in work.newusers, work.vba.beginner, and > work.vba.general)"? > > So the main idea of the code is to replace the "LINK" in the field > codes with "DOCVARIABLE" and make it a "DOCVARIABLE" field instead of > a link field? I used similiar code to replace the folder path in the > field code. > > Thank you for asking the following questions: > > Are all of the links to the one workbook? Are they all to the cells > on one > sheet in that spreadsheet? Or do the links come from multiple sheets > in > multiple workbooks? > > That is very considering of yours. 99% of the links are link to the > same workbooks, where as the other 1% are not. The links in that 1% > are purposely ignored. I do have some IF conditional statement to > take care of this so that only the 99% portion is modified. (There > are also PAGEREF, table of content links, in the files. They are > ignored too.) > > I will try your code and let you know how it works out. > > Despite the fact that we may switch the link to DOCVARIABLE type, > before we even do this, the alert message will continue to come up > when the file is opened. One will need to click throght the [OK] > button in each of the popped up message in order to proceed further. > As I have mentioned previously, to avoid this, either use a doc file > (2003 version) or run the VBA file in Word 2003. (Even the VBA file > is developed in Word 2007 and was saved in docx, it can still be > loaded and run in Word 2003 - if there is no 2007 functionality in > it.) > > By the way, should I post the link of this thread in the threads that > I posted in mutliple locations, so that people know that the answer > has been provided? > > Anthony > |