Solved: Macro to Launch Template from button on Ribbon which works for different user names

[bstephens]

I am looking to create a macro which will launch an instance of a template (.dot) file from the ribbon. The macro I created using the word's "macro recorder" is as follows:

Sub SKLetter()
'
' SKLetter Macro
'
'
Documents.Add Template:= _
"C:\Documents and Settings\Administrator\Application Data\Microsoft\Templates\SKLetter.dot" _
, NewTemplate:=False, DocumentType:=0
End Sub
The macro works OK, but the problem is that I now want to deploy it on different users machines, and the macro is hard coded to the user name "Administrator" so if I copy the macro over to a new machine where the user name is anything else other than "Administrator" (for example say the user name on the new machine is "Assistant1") the macro no longer works.

I tried using the Windows XP environment variable "%APPDATA%\Microsoft\Templates" so that the macro reads:

Sub SKLetter()
'
' SKLetter Macro
'
'
Documents.Add Template:= _
"%APPDATA%\Microsoft\Templates\SKLetter.dot" _
, NewTemplate:=False, DocumentType:=0
End Sub
but that doesn't work either.

I am not very sophisticated with VBA. Can someone point me in the right direction to write a macro that simply loads a new instance of a template (for example SKLetter.dot) when you click on a button on the ribbon and will work on computers that have different user names?

If this also helps, the following is the callback code I am using to link "Sub SKLetter" back to the ribbon.

'Callback for SKLetter onAction
Sub rxbtnSKLetter_click(control As IRibbonControl)
Call SKLetter
End Sub

Thank you in advance.

[Paul_Hossler]

Option Explicit
Sub SKLetter()
'
' SKLetter Macro
'
'
' MsgBox "%APPDATA%\Microsoft\Templates\SKLetter.dot"

MsgBox Environ("APPDATA") & "\Microsoft\Templates\SKLetter.dot"

End Sub


Paul

[bstephens]

Paul, thank you so much for your answer. I should of said before when I don't know VBA too well yet, I meant literally picked up the book at Borders yesterday :) Just so I'm not confused the "MsgBox" is intended to be a demonstration of sorts?

I revised the code you gave me to this:

Option Explicit
Sub SKLetter()
'
' SKLetter Macro
'
' MsgBox "%APPDATA%\Microsoft\Templates\SKLetter.dot"

Documents.Add Template:= _
Environ("APPDATA") & "\Microsoft\Templates\SKLetter.dot"

End Sub
and it seems to work! But is this "best practice" with respect to creating a new "instance" of a document from a template? I guess my confusion arises from the difference between "Documents.Add" and "Documents.Open" Thanks again!

[Tinbendr]

... the difference between "Documents.Add" and "Documents.Open"
Documents.Add creates a new document based on a template. If you don't specify a template, Word uses Normal.dot.
You can also use
Set MyDoc = Documents.AddThis sets an object that you can use to work with the newly create document.

Documents.Open opens an already created document.

[fumei]

This should be emphasized:

"If you don't specify a template, Word uses Normal.dot. "

Documents.Add ' a new document based on Normal

Documents.Add Template:= _
Environ("APPDATA") & "\Microsoft\Templates\SKLetter.dot"
' a new document based on SKLetter.dot
Paul's suggestion of a document object is good, but perhaps he did not fully explain.
' this declares a document object
Dim MyDoc As Document

' this sets the object as a document, using Normal
Set MyDoc = Documents.Add


' this sets the document object as a document, using SKLetter.dot
Set MyDoc = Documents.Add (Template:=_
Environ("APPDATA") & "\Microsoft\Templates\SKLetter.dot" )
Note the use of parenthesis!!!

Documents.Add Template:=_
Environ("APPDATA") & "\Microsoft\Templates\SKLetter.dot"
' NO parenthesis...NO object being Set

Set MyDoc = Documents.Add (Template:=_
Environ("APPDATA") & "\Microsoft\Templates\SKLetter.dot" )
' parenthesis...object being Set
You can also attach a template after the fact.

Dim MyDoc As Document

' this sets the object as a document, using Normal
Set MyDoc = Documents.Add

MyDoc.AttachedTemplate = Environ("APPDATA") _
& "\Microsoft\Templates\SKLetter.dot"
Assuming of course the above string is correct and valid.

[Paul_Hossler]

fumei - Very minor clarification ... in my #2, I was only talking about the use of Environ ( ), I don't get credit for any thing else :(



Option Explicit
Sub SKLetter()
'
' SKLetter Macro
'
'
' MsgBox "%APPDATA%\Microsoft\Templates\SKLetter.dot"

MsgBox Environ("APPDATA") & "\Microsoft\Templates\SKLetter.dot"

End Sub



Paul

[fumei]

Oh I think you should get credit for mentioning the possible use of a document object. Oh....crap....I need to pay attention. Tinbendr mentioned the document object. Oops. My apologies.