"You cannot open two documents with the same name" problem and how to trap the error in VBA.
Anthony
I am wondering if anyone has a solution on this.
There is a Word 2007 file (docx) that has links to an Excel file.
They are both resided in the same folder. When the files are moved
to
another folder, the link path does not get updated – which I
understand. But when the Word file is first opened, the following
message came up:
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.
(Notice that it does not matter if the source file is xlsm or xlsx.)
This message would come up for every links in the file. One has to
keep clicking the [Ok] button to by pass this. If there are 100
links
in the file, one would have to click 100 times. Unfortunately, there
are over 100 files like that and some may contains hundreds of
links.
(Also, when the Word docx is opened, Excel also came up in the
background - as shown in the Windows Task Manager.)
The problem would occur whether the Excel source file is opened or
not. When I save the 2007 docx file into 2003 doc file, there is no
such problem for the 2003 doc file.
You may replicate the error following the simple steps below:
1. Create a link from an Excel xlsx or xlsm file (say in cell A1)
into
a Word 2007 docx (using paste special.. with the link button checked)
and save both files in “Folder 1”.
2. Copy the folder with the two files and rename the folder to
“Folder
2”.
3. Open the Word file from folder 2.
When you have done all these you will encounter the error.
I use VBA in Word to open the docx file and have the code run a check
to see if the particular xls file is opened or not. If it is pened,
then close it. But despite I have the code running, the "A document
with the name "xxx.xlsm" is already open" message still came up in the
beginning. The error message came up before the code can be executed
and I cannot trap it using error handler. When I use VBA 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.
So what I need to know is how to programmatically bypass the error
message and how to trap the error. (If the error can be trapped then
it can be bypass.)
Could anyone help?
Anthony
Doug Robbins - Word MVP
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.
I appreciate that method may not be suitable however for your circumstances
--
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" <excelm...@gmail.com> wrote in message
news:37ba12b3-2826-4a8d...@z39g2000vbb.googlegroups.com...
> Hi All,
>
> I am wondering if anyone has a solution on this.
>
> There is a Word 2007 file (docx) that has links to an Excel file.
> They are both resided in the same folder. When the files are moved
> to
> another folder, the link path does not get updated � which I
> understand. But when the Word file is first opened, the following
> message came up:
>
> 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.
>
> (Notice that it does not matter if the source file is xlsm or xlsx.)
>
> This message would come up for every links in the file. One has to
> keep clicking the [Ok] button to by pass this. If there are 100
> links
> in the file, one would have to click 100 times. Unfortunately, there
> are over 100 files like that and some may contains hundreds of
> links.
>
> (Also, when the Word docx is opened, Excel also came up in the
> background - as shown in the Windows Task Manager.)
>
> The problem would occur whether the Excel source file is opened or
> not. When I save the 2007 docx file into 2003 doc file, there is no
> such problem for the 2003 doc file.
>
> You may replicate the error following the simple steps below:
> 1. Create a link from an Excel xlsx or xlsm file (say in cell A1)
> into
> a Word 2007 docx (using paste special.. with the link button checked)
> and save both files in �Folder 1�.
> 2. Copy the folder with the two files and rename the folder to
> �Folder
> 2�.
Anthony
<d...@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
Anthony
code with the DOCVARIABLE field code? Thanks.
Anthony
Doug Robbins - Word MVP
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" <excelm...@gmail.com> wrote in message
news:29f3f3fb-0437-43c9...@f42g2000yqn.googlegroups.com...
Anthony
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
Doug Robbins - Word MVP
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" <excelm...@gmail.com> wrote in message
news:e04aae8e-9ee4-4edd...@k11g2000vbe.googlegroups.com...
Doug Robbins - Word MVP
open", which should suppress the alert message.
--
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" <excelm...@gmail.com> wrote in message
news:e04aae8e-9ee4-4edd...@k11g2000vbe.googlegroups.com...
Anthony
Thank you for your help again. Checking off the "Update automatic
links at open" off does not help and neither check the "Update
Manually" option under Links (right click). The messge still pop
up.
I do not know why you mention this "do not know how you post
simultaneously to multiple newsgroups using
Mozilla". Were you asking me how I did it or you are trying to show
me how to do it? (If the former, then I did it by going to each of
the groups and made the post by one by.)
Regarding this info "In Outlook Express/Windows Mail/Windows Live
Mail, you select all of the Newsgroups ....", would this allow me to
read Newsgroups posts using Outlook without going to the Web? I use
Outlook 2007, could you show me how to do this in it?
Thanks.
Anthony
Doug Robbins - Word MVP
it should be done in Mozilla.
The answer to the second question is "Yes". The following instructions were
written by Tom Kock for Outlook Express, but the method is pretty much the
same in Windows Mail and Windows Live Mail
1.1. Connect to a newsgroup server using Outlook Express
Open Outlook Express and follow these steps:
1. On the Tools menu, click Accounts.
2. In the Internet Account dialog box, click Add, and then click News.
The Internet Connection Wizard starts. Follow these steps in the wizard:
1. On the first screen, in the Display name box, type the name that other
users will see when you post a message to the newsgroup, and then click
Next.
2. On the second screen, type your full e-mail address in the E-mail address
box, and then click Next.
3. On the third screen, in the News (NNTP) Server box, type the name of your
news server. To subscribe to the newsgroups hosted on Microsoft's servers,
type msnews.microsoft.com. If you don't know the name of your news server,
contact your Internet provider.
4. If necessary, select the My news server requires me to log on check box,
and then click Next. If you don't know whether your server requires you to
log on, contact your Internet provider. The Microsoft server does not
require a user name and password.
5. On the last screen, click Finish.
3. Click Close, and then click Yes when asked if you want to download
newsgroups from the account you just created. The Newsgroup Subscriptions
dialog box appears.
4. Select the newsgroups you want to use, click Subscribe, and then click
OK.
Note Most of Microsoft newsgroups use this naming convention:
microsoft.public.application_name.specific_area. For example: you could
subscribe to microsoft.public.office.dataanalyzer, or
microsoft.public.excel.programming. However, that isn't always the case.
Don't be afraid to browse the list of groups in order to find something that
you think will be useful.
5. On the Tools menu, click Options, click the Read tab, and then clear the
Get 300 headers at a time check box.
6. Click the Maintenance tab, clear the Delete new messages n days after
being downloaded check box, and then click OK.
Step 5 allows you to download and read all the newsgroup's postings when you
first subscribe. Step 6 allows you to read older messages.
1.2. Subscribe or unsubscribe to newsgroups
After you establish a connection to your newsgroup server, you add
newsgroups to your list by subscribing. You can subscribe or unsubscribe to
newsgroups at any time. Typically, you use the group name as a guide when
subscribing. For example, a name such as
microsoft.public.office.developer.outlook.forms would be a good place to
start if you need help writing a custom form for Outlook.
To subscribe
1. Start Outlook Express.
2. In the folder pane, click the name of your news server. For example, if
you established a connection to Microsoft's server, click
msnews.microsoft.com.
3. In the main viewing pane, click Newsgroups.
4. In the Newsgroup Subscriptions dialog box, select a newsgroup from the
list, and then click Subscribe.
5. Repeat the previous step to subscribe to additional newsgroups, and then
click OK when you finish.
6. In the main viewing pane, double-click the name of the new newsgroup. If
you only want to receive new messages since the last time you visited,
select the New messages only check box, and then double-click the newsgroup
name.
Note When you subscribe to a newsgroup for the first time, you receive all
the messages whether or not you select the New messages only check box.
To unsubscribe
. In the Outlook Express folder pane, right-click the newsgroup that you
want to stop using, and then click Unsubscribe on the shortcut menu.
Tips
. To reduce the number of items in the list of available newsgroups, enter a
phrase in the Display newsgroups which contain box. For instance, you can
enter an application name such as "publisher" or the name of a discipline
such as "programming." The list changes automatically when you finish
typing.
. Newsgroups in languages other than English are identified by their names;
for example, microsoft.public.fr.excel is a newsgroup for Microsoft Excel
users who speak French.
--
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" <excelm...@gmail.com> wrote in message
news:a202738b-6b36-4b7d...@c28g2000vbc.googlegroups.com...
Anthony
Thank you for your instruction on how to set up accessing newgroups in
Outlook.
If you find out how to disable the "You cannot open two documents with
the same name" message pop up, please let me know. Thanks again.
Anthony
Doug Robbins - Word MVP
Sub FileOpen()
Application.DisplayAlerts = wdAlertsNone
Dialogs(wdDialogFileOpen).Show
End Sub
--
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" <excelm...@gmail.com> wrote in message
news:c777a5de-b3dc-4627...@f8g2000yqn.googlegroups.com...
Anthony
Again, thank you for your persistancy in trying to help. I tested the
code in a module and opened the file through the
"Dialogs(wdDialogFileOpen).Show", but the message is showed up. I did
have "Application.DisplayAlerts = wdAlertsNone " before the
DialogFileOpen statement on my VBA program before I tried yours (which
is similiar - place Application.DisplayAlerts = wdAlertsNone before
any code). The two methods all yield the same result.
Any more idea?
(By the way, do I really need to put the code in the normal.dotm
template to try this or what I just did is enough to test the code?
Also, even if this works, how can I implement normal.dotm template
change on other people's PCs (uses' PCs)? I do not have access to
their PCs. Is there a way to do this programmatically?)
Anthony