A
Anthony
HI All,
How to update a link field with preserve format using VBA?
Anthony
How to update a link field with preserve format using VBA?
Anthony
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Graham Mayor
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
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
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
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
A
Anthony
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
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
D
Doug Robbins - Word MVP
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.
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.
A
Anthony
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
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
D
Doug Robbins - Word MVP
The f4 or f5 depends upon the formatting that you select when you insert the
Link. It is the \* Charformat switch that controls the font formatting and
hence colour.
--
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.
Link. It is the \* Charformat switch that controls the font formatting and
hence colour.
--
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.
A
Anthony
Thanks again, Doug.
A side issue, how to close any instance of Excel opened? Include the
one hidden (but can be seen in Task Manager Process tab).
Anthony
A side issue, how to close any instance of Excel opened? Include the
one hidden (but can be seen in Task Manager Process tab).
Anthony
D
Doug Robbins - Word MVP
If you are talking about an instance of Excel opened by some code that you
are running, you should use the following method:
Dim xlApp As Object
Dim xlbook As Object
Dim Excelwasnotrunning As Boolean
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err Then
Excelwasnotrunning = True
Set xlApp = CreateObject("Excel.Application")
End If
On Error GoTo ErrMsg
With xlApp
Set xlbook = .Workbooks.Open("Drive:\Path\Workbookname")
'Do what you will with xlbook
Set xlbook = Nothing
End With
If Excelwasnotrunning Then
xlApp.Quit
End If
Set xlApp = Nothing
If it is just some left over instance, just delete it form the list of
processes in the Task Manager. You will get a warning message, but if you
are not running Excel for anything at that point, you can ignore the warning
(at least I do).
--
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.
are running, you should use the following method:
Dim xlApp As Object
Dim xlbook As Object
Dim Excelwasnotrunning As Boolean
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err Then
Excelwasnotrunning = True
Set xlApp = CreateObject("Excel.Application")
End If
On Error GoTo ErrMsg
With xlApp
Set xlbook = .Workbooks.Open("Drive:\Path\Workbookname")
'Do what you will with xlbook
Set xlbook = Nothing
End With
If Excelwasnotrunning Then
xlApp.Quit
End If
Set xlApp = Nothing
If it is just some left over instance, just delete it form the list of
processes in the Task Manager. You will get a warning message, but if you
are not running Excel for anything at that point, you can ignore the warning
(at least I do).
--
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.
A
Anthony
Doug, thanks again for your solution. However, I was trying to delete
a "left over" instance that was created by other process. I need to
do this programmatically. I am wondering if there is way to do that.
Anthony
a "left over" instance that was created by other process. I need to
do this programmatically. I am wondering if there is way to do that.
Anthony
D
Doug Robbins - Word MVP
I doubt that it can be done using VBA. Really, the other process should be
modified so that it does not leave anything behind.
--
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
modified so that it does not leave anything behind.
--
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
P
Peter Jamieson
You could try the approach described at, e.g.
http://www.vbaexpress.com/kb/getarticle.php?kb_id=811
Personally, I would want to understand a lot more about the consequences
of closing processes using that technique before using it except in
emergencies, and also explore whether it would be possible to modify it
to get Excel to save/close open documents, quit properly, etc. etc. Not
something I've ever looked at in depth.
Peter Jamieson
http://tips.pjmsn.me.uk
http://www.vbaexpress.com/kb/getarticle.php?kb_id=811
Personally, I would want to understand a lot more about the consequences
of closing processes using that technique before using it except in
emergencies, and also explore whether it would be possible to modify it
to get Excel to save/close open documents, quit properly, etc. etc. Not
something I've ever looked at in depth.
Peter Jamieson
http://tips.pjmsn.me.uk
A
Anthony
Thank you Doug and Peter for your input. I will give the code in
vbexpress a try.
I am having a lot of issue with Excel links (source) in Word
(destination). I used VBA to update links (in case when the source
file is relocated).
When I open up the Word doc, Excel also came up in the background (as
shown in the Windows Task Manager). Most of the time, when I close up
Word doc, and the Word applicaiton itself, the Excel instance would be
cleared. But sometimes, it is not; I can still see it in the Task
Manager. While developing the VBA code, I got the following errors -
sometimes they came up during the run time and sometimes came up
without the VBA running (just by manually process):
(1)
Run-Time Error '6083'
Objects in this document contain links to files that cannot be found.
The linked information will not be updated.
(2)
The server application, source file, or item cannot be found.
Make sure the applicaiton is properly installed, and that it has not
been deleted, moved, renamd, or blocked by policy,
(3)
The link could not be created because of a server error.
(4)
A document with the name "xxx.xlsm" is already open. YOu cannot open
two documents with teh same name, even if the documents are in
different folders.
To open the second documents, either close the document that's
currently open, or rename one of the documents.
Because they are such issues, I am wondering if I can close the Excel
instance to avoid these problem. But sometimes, the problem is still
there even affter I manually delete the instance from the Task
Manager.
Another odd thing is that, I cannot update the links even manually
because it kept saying the source file could not be found. This
happen when I copy the both the doc and xls files to a new folder.
When I use Link window to select the the source file, even though it
is there, I got an error message saying the source file could not be
found.
What is goiing on here? Could any one help? Should I start a new
thread?
Anthony
vbexpress a try.
I am having a lot of issue with Excel links (source) in Word
(destination). I used VBA to update links (in case when the source
file is relocated).
When I open up the Word doc, Excel also came up in the background (as
shown in the Windows Task Manager). Most of the time, when I close up
Word doc, and the Word applicaiton itself, the Excel instance would be
cleared. But sometimes, it is not; I can still see it in the Task
Manager. While developing the VBA code, I got the following errors -
sometimes they came up during the run time and sometimes came up
without the VBA running (just by manually process):
(1)
Run-Time Error '6083'
Objects in this document contain links to files that cannot be found.
The linked information will not be updated.
(2)
The server application, source file, or item cannot be found.
Make sure the applicaiton is properly installed, and that it has not
been deleted, moved, renamd, or blocked by policy,
(3)
The link could not be created because of a server error.
(4)
A document with the name "xxx.xlsm" is already open. YOu cannot open
two documents with teh same name, even if the documents are in
different folders.
To open the second documents, either close the document that's
currently open, or rename one of the documents.
Because they are such issues, I am wondering if I can close the Excel
instance to avoid these problem. But sometimes, the problem is still
there even affter I manually delete the instance from the Task
Manager.
Another odd thing is that, I cannot update the links even manually
because it kept saying the source file could not be found. This
happen when I copy the both the doc and xls files to a new folder.
When I use Link window to select the the source file, even though it
is there, I got an error message saying the source file could not be
found.
What is goiing on here? Could any one help? Should I start a new
thread?
Anthony
A
Anthony
I discovered it is error message that is causing all the issue.
----------------------------------------------------------
A document with the name "xxx.xlsm" is already open. You cannot open
two documents with the same name, even if the documents are in
different folders.
To open the second documents, either close the document that's
currently open, or rename one of the documents.
----------------------------------------------------------
How can I trap this error? I use On Error Goto ErrorHandler statement
trying to trap it, but it never got trapped. It seems like it is not
considered as an error. Any idea anyone?
Anthony
----------------------------------------------------------
A document with the name "xxx.xlsm" is already open. You cannot open
two documents with the same name, even if the documents are in
different folders.
To open the second documents, either close the document that's
currently open, or rename one of the documents.
----------------------------------------------------------
How can I trap this error? I use On Error Goto ErrorHandler statement
trying to trap it, but it never got trapped. It seems like it is not
considered as an error. Any idea anyone?
Anthony
P
Peter Jamieson
The error won't be trapped because Word is running its own code when it
opens the file and not your VBA code.
If you do not absolutely have to use the new Excel formats .xlsx/.xlsm
etc., you /may/ find that the .xls format functions better in this case.
AFAICS Office 2007 is much more particular about multiple processes
accessing the newer OOXML format files simultaneously.
Does this occur when you already have xxx.xlsm open when you try to open
your Word file, or even when nothing has it open but you have multiple
links from the same Word document? If it is the former, I think your
only other option is to try to force anything that has xxx.xlsm open to
close it first.
Peter Jamieson
http://tips.pjmsn.me.uk
opens the file and not your VBA code.
If you do not absolutely have to use the new Excel formats .xlsx/.xlsm
etc., you /may/ find that the .xls format functions better in this case.
AFAICS Office 2007 is much more particular about multiple processes
accessing the newer OOXML format files simultaneously.
Does this occur when you already have xxx.xlsm open when you try to open
your Word file, or even when nothing has it open but you have multiple
links from the same Word document? If it is the former, I think your
only other option is to try to force anything that has xxx.xlsm open to
close it first.
Peter Jamieson
http://tips.pjmsn.me.uk
A
Anthony
Hi Peter, thank you for your help again. You are right, the error
cannot be trapped because it is running by Word. However, I might
post another thread in other VBA groups to see if anyone will have a
solution for it.
Using Excel file in 2007 format is a requirement, unfortunately. The
problem comes up only for Word 2007 file format. When I save the 2007
files into 2003, there is no such problem. As you have brought up
"AFAICS Office 2007 is much more particular about multiple processes
accessing the newer OOXML format files simultaneously.", that it
probably why.
But there must be a way to deal with this issue, right? Because if
someone has 100 such links (in my case, several hundred+) in a Word
2007 file, he or she will need to click the button on the error dialog
window that pop up 100 times in order to proceed. This just does not
make sense at all.
The problem would occur whether the Excel source file is opened or
not. I have the code run a check to see if the particular file is
opened or not. If it is opened, then close it. I will need to pause
the run-time for 3 seconds after this procedure is executed, else, the
links will not be updated - probably because it takes sometimes for
the file to be fully unloaded after the code closed it. But despite I
have the code running, the "A document with the name "xxx.xlsm" is
already open" message still come up in the beginning. I would have to
click the "ok" button (as many times as the number of the links) in
order for the code above to be ran.
It is very simple to replicate the error that I have:
1. Create a link from an Excel xlsx or xlsm file (say cell A1) in a
Word 2007 docx and save both files in folder 1.
2. Copy the folder and rename it to folder 2.
3. Open the Word file from folder 2.
When you have done all these you will encounter the error. This has
nothing to do with VBA as it is alll manual process,
When I use VBA (In Word) to open the docx file , I have
Application.DisplayAlerts = wdAlertsNone in the beginning before
the Word file is selected and opened, but the message still came up.
I do not understand why Microsoft allows such shortcoming their
application to happen. Haven't they ever test this functionality
during the development as link to Excel and relocate the source file
should be a very common thing.
If Peter or anyone has any more ideas please continue to share.
Thanks.
(A side note: I found it interesting that posts in this group got
published in many forums on the Internet within a day. The pages are
also indexed by Google search (that was how I found out). So my post
is all over the Web right now. How did they do it?)
Anthony
cannot be trapped because it is running by Word. However, I might
post another thread in other VBA groups to see if anyone will have a
solution for it.
Using Excel file in 2007 format is a requirement, unfortunately. The
problem comes up only for Word 2007 file format. When I save the 2007
files into 2003, there is no such problem. As you have brought up
"AFAICS Office 2007 is much more particular about multiple processes
accessing the newer OOXML format files simultaneously.", that it
probably why.
But there must be a way to deal with this issue, right? Because if
someone has 100 such links (in my case, several hundred+) in a Word
2007 file, he or she will need to click the button on the error dialog
window that pop up 100 times in order to proceed. This just does not
make sense at all.
The problem would occur whether the Excel source file is opened or
not. I have the code run a check to see if the particular file is
opened or not. If it is opened, then close it. I will need to pause
the run-time for 3 seconds after this procedure is executed, else, the
links will not be updated - probably because it takes sometimes for
the file to be fully unloaded after the code closed it. But despite I
have the code running, the "A document with the name "xxx.xlsm" is
already open" message still come up in the beginning. I would have to
click the "ok" button (as many times as the number of the links) in
order for the code above to be ran.
It is very simple to replicate the error that I have:
1. Create a link from an Excel xlsx or xlsm file (say cell A1) in a
Word 2007 docx and save both files in folder 1.
2. Copy the folder and rename it to folder 2.
3. Open the Word file from folder 2.
When you have done all these you will encounter the error. This has
nothing to do with VBA as it is alll manual process,
When I use VBA (In Word) to open the docx file , I have
Application.DisplayAlerts = wdAlertsNone in the beginning before
the Word file is selected and opened, but the message still came up.
I do not understand why Microsoft allows such shortcoming their
application to happen. Haven't they ever test this functionality
during the development as link to Excel and relocate the source file
should be a very common thing.
If Peter or anyone has any more ideas please continue to share.
Thanks.
(A side note: I found it interesting that posts in this group got
published in many forums on the Internet within a day. The pages are
also indexed by Google search (that was how I found out). So my post
is all over the Web right now. How did they do it?)
Anthony
P
Peter Jamieson
Interestingly, if I follow your procedure:
I do not see the error. However, I close all the files and both Word and
Excel after step 1. What I see is that Word still tries to open the
..xlsx from folder 1. This is Word 2007 SP2 on Vista 32-bit SP2, with
automatic link updating switched on.
However, I would certainly expect a problem if either
a. you have links to different /Excel/ files with the same name or
b. for some reason, a copy of Excel was left open with the first Excel
file open, then you opened the Word file, because then you also have a
situation in which Word tries to use Excel to open two different Excel
files with the same name.
FWIW, at one point, Word did update links automatically when you /moved/
the container and contained files from one folder to another (i.e. so
the files were in the same folder). I don't know whether Microsoft got
rid of this by accident or by design.
They probably use an NNTP client program to access all the newsgroup
feeds they want and reformat to fit in with however they want their
websites to work.
Peter Jamieson
http://tips.pjmsn.me.uk
1. Create a link from an Excel xlsx or xlsm file (say cell A1) in a
Word 2007 docx and save both files in folder 1.
2. Copy the folder and rename it to folder 2.
3. Open the Word file from folder 2.
I do not see the error. However, I close all the files and both Word and
Excel after step 1. What I see is that Word still tries to open the
..xlsx from folder 1. This is Word 2007 SP2 on Vista 32-bit SP2, with
automatic link updating switched on.
However, I would certainly expect a problem if either
a. you have links to different /Excel/ files with the same name or
b. for some reason, a copy of Excel was left open with the first Excel
file open, then you opened the Word file, because then you also have a
situation in which Word tries to use Excel to open two different Excel
files with the same name.
I do not understand why Microsoft allows such shortcoming their
application to happen. Haven't they ever test this functionality
during the development as link to Excel and relocate the source file
should be a very common thing.
FWIW, at one point, Word did update links automatically when you /moved/
the container and contained files from one folder to another (i.e. so
the files were in the same folder). I don't know whether Microsoft got
rid of this by accident or by design.
(A side note: I found it interesting that posts in this group got
published in many forums on the Internet within a day. The pages are
also indexed by Google search (that was how I found out). So my post
is all over the Web right now. How did they do it?)
They probably use an NNTP client program to access all the newsgroup
feeds they want and reformat to fit in with however they want their
websites to work.
Peter Jamieson
http://tips.pjmsn.me.uk