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.Macr
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.
What can I do the get rid of that path in front of the Excel filename!ThisWorkbook,Macr
Please help!
Thanks!
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.Macr
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.
What can I do the get rid of that path in front of the Excel filename!ThisWorkbook,Macr
Please help!
Thanks!
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:
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.
Sub IndirectHelloWorld()
Application.Run "'Hello World.xlsm'!HelloWorld"
End SubNote 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.
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.
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.
ASKER
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
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
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?
www.jkp-ads.com/articles/fixlinks2udf.asp
Perhaps you can use the ideas in that article for your purpose?
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.
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.
ASKER
This sounds promising! I will look into it at work tomorrow. Thank you!
Iver in Oslo
Iver in Oslo
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()",
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.
IndirectHelloWorld.xlsx
=HYPERLINK("#'Hello world.xlsm'!HelloWorld()",
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 FunctionIndirectHelloWorld.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
Thanks anyway!
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
ASKER
IVer