[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

Excel hyperlink base doesn't seem to work when used in template. Links changes automatically. Please help!

I thought the hyperlink base property would solve my problem, but I was to quick. It doesn't.

I have a button in one Excel workbook sheet pointing to a macro in another open workbook. This works fine. When I save this workbook as a template and move it along with the other workbook to another directory, open the other workbook, then open a new sheet based on my template and click the button, I get an error message telling me that Excel cannot find the macro in the sheet which was part of the original workbook in the original directory. In other words, it has preserved the original path/link to the macro, not just the filename and macro name as I entered it.

If I manually edit the link to the macro and simply remove the part of the link that is before the file name (Workbook filename!ThisWorkbook.Macro-name, it works fine.

When I saved the original workbook, I made sure to remove the path in front of the file name for all my buttons pointing to macros, but they seem to reappear no matter what I do, I also tried to add the corect path to the Hyperlink Base property, but that doesn't seem to have any effect at all.

2014-08-20-115600.jpg
What can I do the get rid of that path in front of the Excel filename!ThisWorkbook,Macro-name?

Please help!

Thanks!
0
Iver Erling Arva
Asked:
Iver Erling Arva
  • 5
  • 3
  • 2
  • +1
1 Solution
 
Iver Erling ArvaSenior consultantAuthor Commented:
I suspect that this issue arose as a result of upgrading to Office 2013, but I'm not sure.

IVer
0
 
byundtCommented:
As a workaround, consider calling the macro in the other workbook from a sub in the workbook containing the hyperlink. As long as both workbooks are open and macros enabled in each, you can do this with code like this:
Sub IndirectHelloWorld()
Application.Run "'Hello World.xlsm'!HelloWorld"
End Sub

Open in new window

In the above snippet, there is a macro called HelloWorld in workbook Hello World.xlsm. This macro will run when IndirectHelloWorld is triggered.

Note the single quotes surrounding the name of the workbook containing the macro. These are necessary if the workbook name contains an embedded space, and are optional otherwise.
0
 
jkpieterseCommented:
As a side note I would suggest not to put code called from buttons in the ThisWorkbook module, but in a normal module. In fact, try to keep code in Excel objects (ThisWorkbook, Sheet modules) to the bare minimum.
VBA modules can become corrupt and if the thisworkbook module becomes corrupt all you can do is recreate the workbook in question. Normal modules can be exported, deleted and imported to get rid of corruption.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Iver Erling ArvaSenior consultantAuthor Commented:
Thanks for your answers!

I have been thinking along the same lines as byundt suggests. My problem is that I have taken over support for an old system, so I am hesitant to change things unless I absolutely have to. This stuff used to work, and I haven't knowingly changed anything. All I have done is to open up and look at stuff in the macros and save. Also, the sheet with the buttons calling the macros is a xlsx-sheet and as far as I can tell cannot have any macros. I guess I will have to change that to use the workaround. I have simlar sheets (variations on a theme ;-) that do work!. Strange! I guess they were saved using Office 2010. Could that have anything to do with it? It is really strange that some sheets work and others don't.

Iver in Oslo
0
 
jkpieterseCommented:
In my experience it does happen that buttons loose their 'connection' to the right workbook on occasion, even before Excel 2010. It is for that exact reason I wrote an article like this one (though it concerns UDFs):

www.jkp-ads.com/articles/fixlinks2udf.asp

Perhaps you can use the ideas in that article for your purpose?
0
 
byundtCommented:
Another possibility is to include a Workbook_Open sub in the template that "repairs" the links in all other open workbooks. The template could also include code that traps application-level events, such as the opening of a new workbook, and redirects those links as well.

After looking at jkpieterse's link, I see that he has anticipated those suggestions exactly, and included code that does the trick. When you read the webpage (and its four linked subpages), just think of macros instead of UDFs, and your template file instead of an add-in.
0
 
Iver Erling ArvaSenior consultantAuthor Commented:
This sounds promising! I will look into it at work tomorrow. Thank you!

Iver in Oslo
0
 
byundtCommented:
Though not documented by Microsoft, if your hyperlink references a user-defined function in the template workbook, you can call it with a HYPERLINK formula in the workbook that doesn't contain macros. By using the HYPERLINK function, you can specify the name of the other workbook and its UDF using text. As a result, there are no external links in that workbook. The HYPERLINK formula looks like this:
=HYPERLINK("#'Hello world.xlsm'!HelloWorld()","Hyperlink that calls a UDF")

In the above formula, a user-defined function HelloWorld in open workbook Hello world.xlsm is being called. Note the workbook name is enclosed by single quotes, and is preceded by a #. You'll get an error message if you omit the #.

When testing a user-defined function, I was surprised that it could change the user interface--I changed the color of an adjacent cell. I also found that there would be an error message if the HYPERLINK called a macro, or if the user-defined function didn't return a range reference. My workaround in the UDF below is to select the cell below the active cell.

I tested the code and HYPERLINK formula in Excel 2003 and Excel 2013--and was pleased to find that it works in both.
Function HelloWorld() As Range
Dim cel As Range
Set cel = ActiveCell.Offset(1, 0)
MsgBox "Hello world"
cel.Interior.ColorIndex = 6
Set HelloWorld = cel
End Function

Open in new window

Hello-World.xlsm
IndirectHelloWorld.xlsx
0
 
Iver Erling ArvaSenior consultantAuthor Commented:
Very helpful thoughts and ideas. I will make note of them for future reference. Thank you!

Now, for my project. Just as suddenly as it broke, it started working again. I have absolutely no idea what changed, but both I and the users now can run it just fine again.

Strange! Really!!
0
 
Iver Erling ArvaSenior consultantAuthor Commented:
The only way I have found to fix this is to manually open each workbook and change the macro assignments and save. If I use a macro to create the sheets with macros, the links gets messed up.

Thanks anyway!
0
 
Martin LissRetired ProgrammerCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now