Creating a default for all new workbooks
 

I'm needing to create a default in Excel so that every new workbook that is
created up in my department, automatically has the path & file name displayed
in the footer of every sheet.

I've tried setting up a template, but I can't seem to make it work.

Creating a default for all new workbooks
 

Tasha,

You need to have saved your workbook as a template (in the file type box), named it
book.xlt, and put it in the XLStart folder. You can use either the XLStart folder in your
user path of the Documents and Settings folder, or in the one in Program Files path,
depending on whether it's' for one user or all.

--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Tasha" wrote in message
...
I'm needing to create a default in Excel so that every new workbook that is
created up in my department, automatically has the path & file name displayed
in the footer of every sheet.

I've tried setting up a template, but I can't seem to make it work.

Creating a default for all new workbooks
 

You could try this in your template:-

Put a formula in any cell in say "Sheet1": =CELL("filename",A1)
Name the cell "path" (for example).
Then enter this macro in the "ThisWorkbook" object in VBA:-

Private Sub Workbook_Open()
Dim WS As Worksheet
Dim ThePath As String

ThePath = Me.Worksheets("sheet1").Range("path").Value
For Each WS In Me.Worksheets
WS.PageSetup.LeftFooter = ThePath
Next
End Sub

If you need to get rid of the worksheet name on the end of the string, then
you can use additional formulas using LEFT(...) and SEARCH("[",... ), which I
expect you can work out for yourself.

"Tasha" wrote:

I'm needing to create a default in Excel so that every new workbook that is
created up in my department, automatically has the path & file name displayed
in the footer of every sheet.

I've tried setting up a template, but I can't seem to make it work.

Creating a default for all new workbooks
 

Tasha,
Further to my last post, I've realised that my suggestion only works once
the new file has been saved for the first time (and then re-opened) - before
that the file path and file name is unknown. That is a flaw in your original
request. Let me know, by replying, if my suggestion is helpful.

"Tasha" wrote:

I'm needing to create a default in Excel so that every new workbook that is
created up in my department, automatically has the path & file name displayed
in the footer of every sheet.

I've tried setting up a template, but I can't seem to make it work.

Creating a default for all new workbooks
 

Thanks for your help John; lots of very useful information.

I was able to create the Macro and have it run; however, it only worked for
my current workbook . I'm not very savvy with these kinds of things but I've
got a good idea of how to proceed from here.

Thanks again!
Tasha

"John Crosher" wrote:

Tasha,
Further to my last post, I've realised that my suggestion only works once
the new file has been saved for the first time (and then re-opened) - before
that the file path and file name is unknown. That is a flaw in your original
request. Let me know, by replying, if my suggestion is helpful.

"Tasha" wrote:

I'm needing to create a default in Excel so that every new workbook that is
created up in my department, automatically has the path & file name displayed
in the footer of every sheet.

I've tried setting up a template, but I can't seem to make it work.

Creating a default for all new workbooks
 

Tasha

I've tried setting up a template, but I can't seem to make it work.

What didn't work?

How did you set up the template?

Here is my standard reply to creating Templates for new workbook and
insertsheet

Open a new workbook. Customize as you wish.
Note: you can set all sheet footers at the same time by "grouping" the sheets.
Right-click on a sheet tab and "select all sheets". Set up a custom footer on
the active sheet and will be done to all. Ungroup when setup is complete.

FileSave As Type: scroll down to Excel Template(*.XLT) and select. Name your
workbook "BOOK"(no quotes). Excel will add the .XLT to save as BOOK.XLT.

Store this workbook in the XLSTART folder usually located at........

C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART

This will be the default workbook for FileNew or the Toolbar button FileNew

Do not use FileNew...Blank Workbook or you will get the Excel default workbook.

Existing workbooks are not affected by these settings and would require a macro
or grouping the sheets and make settings as above.

You can also open a new workbook and delete all but one sheet. Customize as
you wish then save this as SHEET.XLT in XLSTART folder also. It now becomes
the default InsertSheet.

More can be found on this in Help under "templates"(no quotes).


Gord Dibben Excel MVP

On Thu, 22 Feb 2007 14:41:48 -0800, Tasha
wrote:

Thanks for your help John; lots of very useful information.

I was able to create the Macro and have it run; however, it only worked for
my current workbook . I'm not very savvy with these kinds of things but I've
got a good idea of how to proceed from here.

Thanks again!
Tasha

"John Crosher" wrote:

Tasha,
Further to my last post, I've realised that my suggestion only works once
the new file has been saved for the first time (and then re-opened) - before
that the file path and file name is unknown. That is a flaw in your original
request. Let me know, by replying, if my suggestion is helpful.

"Tasha" wrote:

I'm needing to create a default in Excel so that every new workbook that is
created up in my department, automatically has the path & file name displayed
in the footer of every sheet.

I've tried setting up a template, but I can't seem to make it work.

Creating a default for all new workbooks
 

Tasha, thanks for your feedback. Hope you can get it fully working.

John

"Tasha" wrote:

Thanks for your help John; lots of very useful information.

I was able to create the Macro and have it run; however, it only worked for
my current workbook . I'm not very savvy with these kinds of things but I've
got a good idea of how to proceed from here.

Thanks again!
Tasha

"John Crosher" wrote:

Tasha,
Further to my last post, I've realised that my suggestion only works once
the new file has been saved for the first time (and then re-opened) - before
that the file path and file name is unknown. That is a flaw in your original
request. Let me know, by replying, if my suggestion is helpful.

"Tasha" wrote:

I'm needing to create a default in Excel so that every new workbook that is
created up in my department, automatically has the path & file name displayed
in the footer of every sheet.

I've tried setting up a template, but I can't seem to make it work.