How to update a link field with preserve format using VBA?
in [Word VBA]
|
Prev: Display SaveAs Dialog with suggested filename using underscore
Next: Obtain Range text-column number in Word Interop
From: Anthony on 17 Feb 2010 01:39 HI All, How to update a link field with preserve format using VBA? Anthony
From: Graham Mayor on 17 Feb 2010 02:03 Without knowing more about what you are trying to do it is difficult to advise. You could for example switch to print preview and back or you could use the example code at http://www.gmayor.com/installing_macro.htm both of which will update fields in your document. If, as your e-mail address suggests, this is a link to an Excel range then I suspect your concern may be more to do with maintaining the formatting than updating the field. That will depend on the type of link you have inserted. If you need to scale the linked object in Word and maintain that, you should consider using Edit Paste Special and one of the picture options, rather than the default html option. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> "Anthony" <excelmodeling(a)gmail.com> wrote in message news:54a966fb-ca34-4e45-95c2-7c43557e2199(a)f8g2000yqn.googlegroups.com... > HI All, > > How to update a link field with preserve format using VBA? > > Anthony
From: Anthony on 17 Feb 2010 15:55 Thank you for your help, Graham. Assume a document has a link to an Excel cell A1. The font color of the link in the Word doc is black. In A1, there is a stock price. The font color is blue. Now the file is rename to , for example, stock_price_today.xls, from stock_price_yesterday.xls. I used activedocument.Fields(i).LinkFormat.SourceFullName = "C:\data \stock_price_today.xls" to update the link. However, when it is updated, the color in the doc became blue. I like it to preserve whatever format it is before. (The i in the above code would be 1 in this case as it is assumed there is only link in the example.) Of course, this is just a very simple example. The really thing may involve much more links with different font size, color, style, etc. If necessary, I can create a sample file for you to look at. Thanks. Anthony
From: Doug Robbins - Word MVP on 17 Feb 2010 19:42 Press Alt+F9 to display the field codes in the document and you will see something like { LINK Excel.Sheet.8 "Book1" "Sheet1!R1C1" \a \f 4 \r } Add a \* charformat switch as shown below and then apply the desired formatting to the L of LINK { LINK Excel.Sheet.8 "Book1" "Sheet1!R1C1" \a \f 4 \r \* Charformat } -- 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:36bcbe1b-39a2-42d3-a972-2a09c994f7b4(a)i39g2000yqm.googlegroups.com... > Thank you for your help, Graham. > > Assume a document has a link to an Excel cell A1. The font color of > the link in the Word doc is black. In A1, there is a stock price. > The font color is blue. Now the file is rename to , for example, > stock_price_today.xls, from stock_price_yesterday.xls. > > I used activedocument.Fields(i).LinkFormat.SourceFullName = "C:\data > \stock_price_today.xls" to update the link. However, when it is > updated, the color in the doc became blue. I like it to preserve > whatever format it is before. (The i in the above code would be 1 in > this case as it is assumed there is only link in the example.) > > Of course, this is just a very simple example. The really thing may > involve much more links with different font size, color, style, etc. > > If necessary, I can create a sample file for you to look at. > > Thanks. > > Anthony > > >
From: Anthony on 17 Feb 2010 23:33
Thank you, Doug. I think the switch for \a \f4 should be \a \f5. I got the info from http://office.microsoft.com/en-us/word/HP051861701033.aspx. \f 4 Maintain the formatting of the source file, if the source file is an Excel workbook 5 Match the formatting of the destination document, if the source file is an Excel workbook I tried f5 and it works. I was using \a \f5 before, but it did not work. Then I used your method with \* Charformat and it worked. Thanks again! However, when I used only \a \f5 \* Charformat, after update, it created an un-need line break under each of the field. Later, I use \a \f 4 \r \* Charformat and the extra line break did not come up. The challege that I am having is that there are many files to be updated throght this VBA program and each files may contain up to thousands of different types of links. Right now, I am using Graham's VBA method (see below) to update the code. I replace "MERGEFORMAT" with "Charformat", "[space] \r [space]" with "[space] \r \a \f 5 [space]", and "[space] \t [space]" with "[space] \r \a \f 5 [space]". There may be other varity of switches needed to be replace. I am trying to get an effective way to do this. If anyone has any idea, please let me know. Also, if I have duplicate switch codes in the fields, how would that impact the update? The reason I asked is that if there are both "\t" and "\r" in the field, after I run the replacement code, I will get a pair of "[space] \r \a \f 5 [space]". In addition, does anyone know why Graham use the For Loop in a reversed order (and not For i = 1 to ActiveDocument.Fields.Count)? (Graham's example was found on other thread. The code was kind of slow. I then discovered that it updates the whole document for every loop. After I move the .update out side of the loop, it works 100 times faster.) Anthony ----------------------------------- Graham's VBA method (with modfication) ----------------------------------- For i = ActiveDocument.Fields.Count To 1 Step -1 With ActiveDocument.Fields(i) If .Type = wdFieldTOC Then If InStr(1, .Code, "MERGEFORMAT") <> 0 Then ..Code.Text = _ Replace(.Code.Text, _ "\* MERGEFORMAT", "") End If ..Update End If End With Next i |