Workgroup templates

Hi Tortoise

I'm sure there is no way of doing this in Excel. You can change the default file location path via Tools>Options>General

Not sure if this will help, but try this.

Go to File>Save as and then select Template (*.xlt) from the Files of type: box. Now create a new folder in here and save any templates to it. Now when you go to File>New and choose Templates, you will see your folder as a new Tab. The same applies when you right click on a sheet name tab and select Insert.

Hi Tortoise,

There is a way to do this using VBA, using the Application.DefaultFilePath command, syntax being:

Application.DefaultFilePath = "path"

Not sure if this helps or not.

Hi, try this links

http://support.microsoft.com/d…aspx?scid=kb;en-us;304486

....and this from MS

Templates
Templates (*.xlt) that you place in a startup folder are not loaded when you start Excel, but you can use the templates by clicking New on the File menu.
If you save a workbook called Book.xlt and place it in a startup folder location, that workbook becomes the default workbook when you start Excel, and when you open any additional new workbooks.

But having said this, if you are on a network you are most likley using a Multi licence version of MS Office and the default templates folder should be available to all?

Hi DrGuru

I think that will do the same as Tools>Options>General Default file location:.

Good morning Dave,

Right you are. I thought that maybe the VBA method changed the default file path only for the workbook that the code was placed in.

I suppose if you wanted it to be that way, you could code it up like so:

Private Sub Workbook_Open()
OldPath = Application.DefaultFilePath
Application.DefaultFilePath = "NewPath"
End Sub

Private Sub Workbook_BeforeClose()
Application.DefaultFilePath = OldPath
End Sub

Of course, please feel free to correct me if I am wrong.

Hi,

I have just been playing with this.
Creates a shortcut in the users template folder that points to the networkfolder containing the shared templates.

If it works you will get a new template tab when using the New dialog.

'
' MakeShortCut apadted by Andy Pope from,
'
' Code used from a reply By John Green, Sydney, Australia
' Who accredited the MakeShortCut Code:
'   The following code has been adapted from code presented by Romke Soldaat in MS
'   Office & VBA Developer magazine - Feb 99. It will overwrite an existing
'   shortcut of the same name:
'
Option Explicit
Sub MakeNetworkTemplatesTab()


    Dim strTabName As String
    Dim strNetworkFolder As String
    
    strTabName = "Andys Test"   ' Template Tab Name
    strNetworkFolder = "X:\Group1\CompanyTemplates\"  ' location of shared template folder
    
    If MakeShortCut(strTabName, strNetworkFolder) Then
        MsgBox "New Template Tab " & strTabName & " Successfully created", vbInformation
    Else
        MsgBox "Unable to create shortcut", vbExclamation
    End If


End Sub
Function MakeShortCut(TabName As String, Path As String) As Boolean
'
' Create a Shortcut link in the default templates folder
'
    Dim wsh As Object
    Dim oShortcut As Object
    Dim strTemplatePath As String
    
    On Error GoTo ErrMakeShortcut
    
    Set wsh = CreateObject("WScript.Shell")
    strTemplatePath = Application.TemplatesPath
    Set oShortcut = wsh.CreateShortcut(strTemplatePath & _
    TabName & ".lnk")
    With oShortcut
        .TargetPath = Path
        .Save
    End With
    Set wsh = Nothing
    MakeShortCut = True
    Exit Function


ErrMakeShortcut:
    Set wsh = Nothing
    MakeShortCut = False
    Exit Function
End Function

Hope this helps.

Cheers
Andy

ps

Quote

Dave
But having said this, if you are on a network you are most likley using a Multi licence version of MS Office....

Even with +20 users, we all have stand- alone copies.