Relative link to Excel spreadsheet with macro problem

Topic

New Reply

Hi all,

I’ve been trying to use the macro posted by macropod here:
http://windowssecrets.com/forums/showthread//154379-Word-Fields-and-Relative-Paths-to-External-Files

…to link excel worksheets to my Word document and have the links automatically updated when the containing folder is moved.

Somehow I always get an error telling me that a file with the name of my Excel document is already open. This message pops up a couple of times, then I get a runtime error from the code. I ran the code step by step and all of the filepaths are ok, but still I get the error when running the replace() function.
I really tried everything I could think of, without luck.

FYI, the Word and the Excel documents are side by side. Only the containing folder is meant to change place
Also, here is an example of the links I have:
{ LINK Excel.Sheet.12 OldPathSpreadSheet.xlsx WorkSheet!L1C1:L17C4 a f 4 h }

Any ideas?

Thanks so much,
20Syl

Reply | Quote

Replies

Try changing the links from auto update to manual update. You may also encounter problems in the linked objects are in the page header/footer.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Try changing the links from auto update to manual update. You may also encounter problems in the linked objects are in the page header/footer.

I have already tried switching to manual update (by removing the a command). Didn’t work. And my links are not part of the header nor footer.
It seems that every time a link is replaced, Word automatically opens an instance of the linked file which I can’t see. Is there something I could add to the code to detect if the file is opened and if so, close it?

Also, I have tried .xls as well as .xlsx file types to see if this could help. It worked the first time after the file type change, then stopped working. I noticed Word automatically updated Excel.Sheet.8 to Excel.Sheet.12 at that moment… Is this normal? What file types are preferrable?

Thank you,
20Syl

Reply | Quote

Hi again,

I ran some further tests.
I can confirm that, when opening the Word document when the Excel document is not opened, an Excel process is automatically started and I get a runtime error from the code. What I can do is debug the code and once debugging, kill the Excel process, then finish running the code. This works, however it is not a viable solution.

I also tried opening the Excel file first, then opening the word document. Doing this makes the error popup from Excel appear, but I can dismiss it and then everything works fine without runtime errors from Word. This is a more viable solution, but stil not the best.

Any suggestions?
Thanks.

Reply | Quote

Have you tried using application.DisplayAlerts = False ?

This shuts off all alerts. It’s best to turn it back on at the end of the macro via

Application.DisplayAlerts = True

Reply | Quote

Have you tried using application.DisplayAlerts = False ?

This shuts off all alerts. It’s best to turn it back on at the end of the macro via

Application.DisplayAlerts = True

That really isn’t going to help as it has nothing to do with the underlying issue.

I’ll have to devise a different approach, but I don’t have time for that ATM – perhaps next week.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Just tried it. This doesn’t prevent any of the error messages to pop up.

Reply | Quote

Ok, thank you. I will work on something else for the moment.
Perhaps it will help telling you that this issue does not occur on every PCs. I had it tried out by two of my colleagues, and one of them could open the file without problem, and the other had the same issue as me.

Reply | Quote

Perhaps it will help telling you that this issue does not occur on every PCs. I had it tried out by two of my colleagues, and one of them could open the file without problem, and the other had the same issue as me.

In that case, it seems the problem is likely to be a configuration issue, perhaps caused by an errant 3rd-party addin, or a faulty Office installation. Have you tried repairing the Office installation (via Start > Windows Control Panel > Programs > Programs & Features > Microsoft Office (version) > Change)?

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote