H
Howard Brazee
I want my footing in an Excel spreadsheet to include the path as well as the
&[file]. How do I do that?
&[file]. How do I do that?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
JE McGimpsey
G
Gord Dibben
Howard
Here's a couple of macros. One for all sheets and one for a single sheet.
Strip out everything after FullName that you don't need/want.
Sub Path_All_Sheets()
Set wkbktodo = ActiveWorkbook
For Each ws In wkbktodo.Worksheets
ws.PageSetup.RightFooter = ActiveWorkbook.FullName & " " & Chr(13) _
& Application.UserName & " " & Date
Next
End Sub
Sub PathInFooter()
ActiveSheet.PageSetup.RightFooter = ActiveWorkbook.FullName & " " & _
ActiveSheet.Name & " " & Application.UserName & " " & Date
End Sub
You could also put the code into Thisworkbook BeforePrint
Gord Dibben Excel MVP
Here's a couple of macros. One for all sheets and one for a single sheet.
Strip out everything after FullName that you don't need/want.
Sub Path_All_Sheets()
Set wkbktodo = ActiveWorkbook
For Each ws In wkbktodo.Worksheets
ws.PageSetup.RightFooter = ActiveWorkbook.FullName & " " & Chr(13) _
& Application.UserName & " " & Date
Next
End Sub
Sub PathInFooter()
ActiveSheet.PageSetup.RightFooter = ActiveWorkbook.FullName & " " & _
ActiveSheet.Name & " " & Application.UserName & " " & Date
End Sub
You could also put the code into Thisworkbook BeforePrint
Gord Dibben Excel MVP
H
Howard Brazee
Take a look here:
http://www.mcgimpsey.com/excel/fullnameinfooter.html
Howard Brazee said:I want my footing in an Excel spreadsheet to include the path as well as the
&[file]. How do I do that?
That says:
Automatic Method - Per Workbook
You can automate this task by putting this macro in the ThisWorkbook code module
of your workbook The header or footer will be updated whenever the user chooses
to Print or Print Preview:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wsSht As Worksheet
For Each wsSht In ActiveWindow.SelectedSheets
wsSht.PageSetup.LeftFooter = Me.FullName
Next wsSht
End Sub
You can use template or class module to make new or all workbooks print with the
path and filename in the footer.
=======================================================
I started a macro in This WorkBook named Workbook_BeforePrint, and pasted the
above into it. When I tried opening the macro I have to type in its name and I
see it. The only effect I can find, is if I exit the spreadsheet and return,
it has a very irritating message asking me if I want macros enabled. I
experimented by putting the file name in LeftFooter and leaving it out. Either
way, I found no evidence of a path.
So I need to find out how to tell the spreadsheet to do the macro when I press
Print or Print Preview, how to make all workbooks do this, and how to tell Excel
to stop asking me if I want macros enabled.
J
JE McGimpsey
Howard Brazee said:I started a macro in This WorkBook named Workbook_BeforePrint, and
pasted the above into it. When I tried opening the macro I have to
type in its name and I see it.
This is an event macro. You don't run it directly. Rather it
automatically runs when you invoke the Print or Print Preview command.
The only effect I can find, is if I exit the spreadsheet and return,
it has a very irritating message asking me if I want macros enabled.
I experimented by putting the file name in LeftFooter and leaving it
out. Either way, I found no evidence of a path.
You don't say whether you attempted to print (or use print preview) -
did you? That's what will cause the macro to fire.
So I need to find out how to tell the spreadsheet to do the macro
when I press Print or Print Preview, how to make all workbooks do
this, and how to tell Excel to stop asking me if I want macros
enabled.
For all workbooks, see the "How to make all or new workbooks print the
path and filename" quick link on the page I referenced. If you follow
the advice there (using an add-in - one's available for download, or you
could make your own), you won't be asked about macros in each workbook.
H
Howard Brazee
This is an event macro. You don't run it directly. Rather it
automatically runs when you invoke the Print or Print Preview command.
Yep, that's what the page says, and that's what I hoped it would do.
You don't say whether you attempted to print (or use print preview) -
did you? That's what will cause the macro to fire.
I didn't know that there was a different way to test to see if there was
evidence of a path. I tried both those options.
For all workbooks, see the "How to make all or new workbooks print the
path and filename" quick link on the page I referenced. If you follow
the advice there (using an add-in - one's available for download, or you
could make your own), you won't be asked about macros in each workbook.
I didn't see that link off to the right. I'll try it.
J
JE McGimpsey
Howard Brazee said:Yep, that's what the page says, and that's what I hoped it would do.
Do you have your Security setting at High?
It works for me using XL98/01/v.X/03/04. And when I had XL00 installed,
it worked there too.
H
Howard Brazee
I've been reading your links. So far, I've used START to find 5 XLSTART
folders on my computer, I've created an empty spreadsheat called Personal.xls in
My Documents, and have added two macros to my spreadsheet. I'm pretty sure I'm
entering the macros wrong, as I can't enter them unless I type in their names
first. At any rate, they don't work - except to make me say "Yes, I want
macros loaded".
I have a few spreadsheets with VB macros. I stopped using them because it was
such an irritation having to enable macros.
I opened HOURS.XLS and then opened Visual Basic. The project lists
- VBAProject (hours.xls)
- Microsoft Excel Objects
Sheet1 (Howard Bra
This Workbook
- Modules
Module1
Modul2
Module1 says:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wsSht As Worksheet
For Each wsSht In ActiveWindow.SelectedSheets
wsSht.PageSetup.LeftFooter = Me.FullName
Next wsSht
End Sub
When that didn't work, I added module2:
Module2 says:
Option Explicit
Dim clsPrinterFooter As New PrintFooterClass
Private Sub Woorkbook_Open()
Set clsPrintFooter.MyPrintApp = Application
End Sub
I bet what I'm missing is very basic and the documentation figures it doesn't
need to mention it because *everybody* knows that part.
folders on my computer, I've created an empty spreadsheat called Personal.xls in
My Documents, and have added two macros to my spreadsheet. I'm pretty sure I'm
entering the macros wrong, as I can't enter them unless I type in their names
first. At any rate, they don't work - except to make me say "Yes, I want
macros loaded".
I have a few spreadsheets with VB macros. I stopped using them because it was
such an irritation having to enable macros.
I opened HOURS.XLS and then opened Visual Basic. The project lists
- VBAProject (hours.xls)
- Microsoft Excel Objects
Sheet1 (Howard Bra
This Workbook
- Modules
Module1
Modul2
Module1 says:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wsSht As Worksheet
For Each wsSht In ActiveWindow.SelectedSheets
wsSht.PageSetup.LeftFooter = Me.FullName
Next wsSht
End Sub
When that didn't work, I added module2:
Module2 says:
Option Explicit
Dim clsPrinterFooter As New PrintFooterClass
Private Sub Woorkbook_Open()
Set clsPrintFooter.MyPrintApp = Application
End Sub
I bet what I'm missing is very basic and the documentation figures it doesn't
need to mention it because *everybody* knows that part.
C
Chip Pearson
Your workbook event procedures must be placed in the ThisWorkbook
code module, not a regular code module like Module1.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
code module, not a regular code module like Module1.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
J
JE McGimpsey
Well, the documentation for the first macro says
which is what you need to do, rather than put it in a regular code
module. There's even a link to my
http://www.mcgimpsey.com/excel/modules.html#workbook
page, which has a picture illustrating how to select the ThisWorkbook
module.
Likewise the class example says:
You can automate this task by putting this macro in the ThisWorkbook code
module of your workbook
which is what you need to do, rather than put it in a regular code
module. There's even a link to my
http://www.mcgimpsey.com/excel/modules.html#workbook
page, which has a picture illustrating how to select the ThisWorkbook
module.
Likewise the class example says:
In the ThisWorkbook module, enter
H
Howard Brazee
Do you have your Security setting at High?
It works for me using XL98/01/v.X/03/04. And when I had XL00 installed,
it worked there too.
That could be. I haven't been able to find a setting for this yet. I'll
check again Monday.
H
Howard Brazee
Your workbook event procedures must be placed in the ThisWorkbook
code module, not a regular code module like Module1.
I figured it was something like that. It looks like I have some studying to do
to figure out what that means and how to change it.
H
Howard Brazee
Well, the documentation for the first macro says
which is what you need to do, rather than put it in a regular code
module. There's even a link to my
http://www.mcgimpsey.com/excel/modules.html#workbook
page, which has a picture illustrating how to select the ThisWorkbook
module.
Likewise the class example says:
I printed that out and will look at it Monday morning. Things were much
simpler in the days before GUI was invented.
H
Howard Brazee
I saved my workbook with a different name, and re-opened my workbook and tried
to figure out how to eliminate the macros with the old spreadsheet, but failed.
How do I get rid of macros?
Also, I tried attaching my workbook to an e-mail to e-mail it to you, hoping you
would be able to glance at at without work and tell me that there was some very
simple misunderstanding I have. But your e-mail automatically rejects e-mail
with attachments.
to figure out how to eliminate the macros with the old spreadsheet, but failed.
How do I get rid of macros?
Also, I tried attaching my workbook to an e-mail to e-mail it to you, hoping you
would be able to glance at at without work and tell me that there was some very
simple misunderstanding I have. But your e-mail automatically rejects e-mail
with attachments.
G
Gord Dibben
Howard
Have a look at Debra Dalgleish's site for where macros can reside and how to
get rid of them.
http://www.contextures.on.ca/xlfaqMac.html#NoMacros
Gord
Have a look at Debra Dalgleish's site for where macros can reside and how to
get rid of them.
http://www.contextures.on.ca/xlfaqMac.html#NoMacros
Gord
H
Howard Brazee
How do I remove VB macros from a spreadsheet?
C
Chip Pearson
Open the VBA Editor (ALT+F11), and delete all the code in the
"Microsoft Excel Objects" modules, and delete any other modules
or userforms.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
"Microsoft Excel Objects" modules, and delete any other modules
or userforms.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
H
Howard Brazee
Open the VBA Editor (ALT+F11), and delete all the code in the
"Microsoft Excel Objects" modules, and delete any other modules
or userforms.
That worked. I tried deleting the modules without deleting the code, and that
didn't work.
Thanks.
H
Howard Brazee
That worked. I tried deleting the modules without deleting the code, and
that
didn't work.
I just got upgraded to Office 2003, and the macros are back. I can't find them
to delete them, but when I start my spread sheet it asks if I want them enabled.
Macros can be useful - but not worth being hassled that way.
I open it, open VB, and see a Sheet1 worksheet and a ThisWorkbook which don't
appear to exist, and cannot be deleted. Maybe there's a way to export this
spreadsheet and leave the non-existent macros behind.
G
Gord Dibben
Howard
When in VBE and Project Explorer.
Double-click on Sheet1 to open the module.
Delete(clear) anything you find in there.
Same for Thisworkbook.
Save.
Gord Dibben Excel MVP
When in VBE and Project Explorer.
Double-click on Sheet1 to open the module.
Delete(clear) anything you find in there.
Same for Thisworkbook.
Save.
Gord Dibben Excel MVP