I'm trying to add a default path to the departmental templates folder in excel 2000. The help says to right click on the toolbar, go to customize then click on the settings tab. I don't have a settings tab! - only Toolbars, Commands and Options. I've looked in Excel 2002 and its the same. In word 2000/2002 there's a path field in options, file locations to put the templates path in, but where is it in Excel?
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.
Thanks, I just tried that and it worked, in that I have have a new folder under templates. However, only I can see that -I want a network folder that the department can see, as they all share the same group of templates. This was possible in excel 97, so surely it must be in 2000?
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
....and this from MS
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?
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"
Private Sub Workbook_BeforeClose()
Application.DefaultFilePath = OldPath
Of course, please feel free to correct me if I am wrong.
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.Code
' ' 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.
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.
Well thanks everyone! I've never had so may good technical replies in so short a time. Unfortunately I have never used VBA and wouldn't know how to implement it. I was hoping for a simple -"oh you just go to options-properties-location" or something similar. As to the network version -no I have a multiple license, but each copy is loaded individualy on each pc, so its a standard copy just pointed to shared areas on the server. I was wrong about it being the same on 2000 and 2002. In 2000 the templates put themselves into a tab when you go to file-new, but not in 2002. So I've reloaded Office 2000 which is prefered by all my users..