Hi niuyuer,
I had another thought,
_ Maybe the codes below better meet your requirement.
_ Both Codes should go in the ThisWorkbook Code Module of the Template
_ .............
The codes are all based on Norie’s ideas and codes.
Code 1:
When you open the Template a
Private Sub Workbook_Open()
Springs into action:
This does the following
Rem 1) Document Property of String Path where Template currently is
_ ‘1a) A check is done to see if the Document Property of the Path name Exists.
_’1b) If it does not exist, then it is made.
Rem 2) Output info
_’ 2a) The Current Path to the Template is pasted out to the Immediate window. ( So if you hit Ctrl+G when in the VB Editor ( Alt F11 ), then you will get displayed a copyable String
_’2b) As before a message box displays that path.
_.............................
Code 2
Before you share the Template you should run the code
Sub DeleteCustomDocumentProperty()
This code deletes the Current Path Property. So when someone opens the code from anywhere else, then the first code springs into action and gives the Current Path to where the Template is.
_.................
Let us know please how you get on
Alan
Codes:
Private Sub Workbook_Open() 'Sub WhereAmICurrently() ' Norie http://www.excelforum.com/showthread.php?t=1143775&p=4412281&highlight=#post4412281
Rem 1)' Norie..."....add the path to the template as a CustomDocumentProperty of the template...."
'1a) .. But I only Add it if it is not there.... So determine if it exists.. http://stackoverflow.com/questions/29204801/test-whether-a-property-name-exists
Dim propertyExists As Boolean: Let propertyExists = False
Dim prop As DocumentProperty ' Object to steer For Next Loop
For Each prop In ThisWorkbook.CustomDocumentProperties
If prop.Name = "CurrentTemplatePath" Then
Let propertyExists = True
Exit For
End If
Next prop
'1b) .. if it does not exist, then make it
If propertyExists = False Then
ThisWorkbook.CustomDocumentProperties.Add Name:="CurrentTemplatePath", LinkToContent:=False, Type:=msoPropertyTypeString, Value:=ThisWorkbook.Path ' The value is converted to match the data type specified by the Type argument, and if it can't be converted, an error occurs. If LinkToContent is True, the argument is ignored and the new document property is assigned a default value until the linked property values are updated by the container application (usually when the document is saved)... , LinkSource:= ... - Ignored if LinkToContent is False. The source of the linked property. The container application determines what types of source linking you can use.
Else
End If
Rem 2) Output Path Info
Dim strTempPath As String
Let strTempPath = ThisWorkbook.CustomDocumentProperties("CurrentTemplatePath")
'2a) Get copyable path output in Immediate Window
Debug.Print "" & strTempPath & "" ' Hit Ctrl+G from VB Editor window to see this !
' Application.VBE.MainWindow.Visible = True These two lines will only work when running code in VB Editor window ???????
' Application.SendKeys "^g" 'Open Imedite window , then next line will give a Path yoiu can Highlight and Copy
'2b) Message Box option for Path
MsgBox prompt:="" & strTempPath & "" ' I use "" & .. Norie..."You don't need "" & here...." http://www.excelforum.com/excel-programming-vba-macros/1102805-learing-about-range-objects-2.html#post4183530
End Sub
'
' Run this Code before you share this Template to Delete the Current Template path Property
Sub DeleteCustomDocumentProperty()
ThisWorkbook.CustomDocumentProperties.Item("CurrentTemplatePath").Delete
End Sub
Bookmarks