Dynamic Menu - based on file structure

[nooch]

Hi

I've created a script which dynamically creates a menu system based on a folder on our network. It sources out every template within each subfolder and creates a menu to the right of the 'Help' menu which holds a list of all these templates.

I have the following function, which is in charge of creating the button within the menu/submenu:


Public Function CreateButtonIn(Parent As String, strCaption As String, Optional strHyperlink As String = "") As CommandBarButton
Dim ctl As CommandBarButton
Set ctl = CommandBars(Parent).Controls.Add(Type:=msoControlButton)
ctl.Caption = strCaption
ctl.HyperlinkType = msoCommandBarButtonHyperlinkOpen
ctl.TooltipText = strHyperlink

Debug.Print ("Created Button: " & strCaption & " in: " & Parent)

Set CreateButtonIn = ctl
End Function


If i pass it a parent commandbar, caption and a file.path it will open the template when the user clicks on the menuitem, but i want it to load up the template as a new document based on the template. Any ideas?

[nooch]

I guess i could link it up to a macro or something? But how would i pass the document template string to the macro?

[Nelviticus]

I only have Office 2007 here at work so I can't try anything out but you can set a CommandBarButton's OnAction and Parameter properties. You could write a macro that takes the template as a parameter and creates a new document based on it.

Regards

[TonyJollans]

Nelveticus is essentially correct: you must do this with a macro assigned to the button - a hyperlink can not do this. Simplistically changing your code:

Public Function CreateButtonIn(Parent As String, strCaption As String, Optional strHyperlink As String = "") As CommandBarButton
Dim ctl As CommandBarButton
Set ctl = CommandBars(Parent).Controls.Add(Type:=msoControlButton)
ctl.Caption = strCaption
' ctl.HyperlinkType = msoCommandBarButtonHyperlinkOpen
ctl.OnAction = "ModuleName.MacroName"
ctl.TooltipText = strHyperlink

Debug.Print ("Created Button: " & strCaption & " in: " & Parent)

Set CreateButtonIn = ctl
End Function

Sub MacroName()
Documents.Add Template:=CommandBars.ActionControl.TooltipText
End Sub

[nooch]

Thank you so much! I've been looking for this for a day and a half now, this is exactly what i needed to do, cheers Tony!!

[nooch]

Nelveticus is essentially correct: you must do this with a macro assigned to the button - a hyperlink can not do this. Simplistically changing your code:

...

Sub MacroName()
Documents.Add Template:=CommandBars.ActionControl.TooltipText
End Sub

This works brilliantly for Word documents, but how would i go about opening, for example Powerpoint Presentations, or Excel Documents. I'm pretty sure all the documents in the list will be of the office suite.

[Nelviticus]

Hmm, I can understand using Word to launch Word templates but it seems very odd to be using Word to launch PowerPoint and Excel templates plus possibly (taking your "pretty sure" to mean "not 100% sure") other file types. Are you sure you're going down the right path?

Assuming you just stick to Office templates you'd have to 1) determine the application (probably by checking the file extension) 2) use a 'Select Case' block to run different code depending on the application and 3) for non-Word files, use Office automation to launch the appropriate app and create a new document based on the template.

If you really want to launch non-Office items as well you'd have to resort to the 'Shell' command.

Regards

[nooch]

I'm certain for the time being we only deal with Office applications. I wasn't sure whether i would be able to use shell from within word.

I know it seems bizar, but the menu is to launch internal documents based upon templates. This includes a large amount of xls and the odd ppt. So I guess as long as I create subs to handle these file types based on the select case statement i've got i should be ok.

Is the best way to open an excel document to create an instance of the referenced Excel object from within word or is there a shortcut method whereby i don't have to instanciate an excel (or otherwise) object? Seems a bit crazy controlling this object from word when office seems so interweaved.

Thanks for your responses so far.

[Nelviticus]

Well you could just launch the app from the Shell with the template as a parameter but then you could end up with more than one instance of the app open, which is why it's best to do it using automation. I've only done this once or twice so I'd just Google the specifics if I had to write any code but it would essentially be:
- Include reference to Office library
- Declare an Excel (or Powerpoint) object
- Find any running instance of the app and set object to it
- If no running app, set object to new instance
- Call whatever the app's 'Documents.Add Template:=' command is.

Regards

[nooch]

Brilliant, thanks for your breakdown Nelviticus, very helpful.