Take a look here:
http://www.mcgimpsey.com/excel/fullnameinfooter.html
In article <eya1bYGQFHA.1932@tk2msftngp13.phx.gbl>,
"Howard Brazee" <howard@brazee.net> wrote:
> I want my footing in an Excel spreadsheet to include the path as well as the
> &[file]. How do I do that?
I want my footing in an Excel spreadsheet to include the path as well as the
&[file]. How do I do that?
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
On Wed, 13 Apr 2005 20:19:33 GMT, "Howard Brazee" <howard@brazee.net> wrote:
>I want my footing in an Excel spreadsheet to include the path as well as the
>&[file]. How do I do that?
On 13-Apr-2005, JE McGimpsey <jemcgimpsey@mvps.org> wrote:
> Take a look here:
>
>
> http://www.mcgimpsey.com/excel/fullnameinfooter.html
>
>
> In article <eya1bYGQFHA.1932@tk2msftngp13.phx.gbl>,
> "Howard Brazee" <howard@brazee.net> wrote:
>
> > 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.
In article <u$94goRQFHA.1172@TK2MSFTNGP12.phx.gbl>,
"Howard Brazee" <howard@brazee.net> wrote:
> 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.
On 14-Apr-2005, JE McGimpsey <jemcgimpsey@mvps.org> wrote:
> > 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.
Yep, that's what the page says, and that's what I hoped it would do.
> > 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.
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.
> > 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.
I didn't see that link off to the right. I'll try it.
In article <#QLh7ncQFHA.1932@tk2msftngp13.phx.gbl>,
"Howard Brazee" <howard@brazee.net> wrote:
> 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.
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.
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
"Howard Brazee" <howard@brazee.net> wrote in message
news:OJvgv8eQFHA.3404@TK2MSFTNGP12.phx.gbl...
> 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.
On 15-Apr-2005, JE McGimpsey <jemcgimpsey@mvps.org> wrote:
> Well, the documentation for the first macro 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
I printed that out and will look at it Monday morning. Things were much
simpler in the days before GUI was invented.
On 15-Apr-2005, "Chip Pearson" <chip@cpearson.com> wrote:
> 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.
On 15-Apr-2005, JE McGimpsey <jemcgimpsey@mvps.org> wrote:
> > 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.
That could be. I haven't been able to find a setting for this yet. I'll
check again Monday.
Well, the documentation for the first macro 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
In article <OJvgv8eQFHA.3404@TK2MSFTNGP12.phx.gbl>,
"Howard Brazee" <howard@brazee.net> wrote:
> 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.
On 15-Apr-2005, JE McGimpsey <jemcgimpsey@mvps.org> wrote:
> > 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.
Security is medium.
I'm reading through the other documentation and trying to compare it to my
worksheet. It's still Greek to me.
On 15-Apr-2005, "Chip Pearson" <chip@cpearson.com> wrote:
> Your workbook event procedures must be placed in the ThisWorkbook
> code module, not a regular code module like Module1.
I'm not sure where it is. I tried dragging Module1 to ThisWorkbook in the
window at the top-left but that didn't take.
It's getting worse. Trying to move the code around, I deleted module1 &
module2. I now have both code in one module:
With a syntax error. And I still get the message wanting to know if I want to
turn on macros when I start my spreadsheet. I had those in a spreadsheet I
wrote before that involved Visual Basic, and finally figured that they were
enough of an irritation that I didn't need so much automation.
(general) (declarations)
Option Explicit
Dim clsPrinterFooter As New PrintFooterClass
Private Sub Woorkbook_Open()
Set clsPrintFooter.MyPrintApp = Application
End Sub
___________________________________________
Private Sub Workbook_Open()
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
End Sub
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.
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
On Thu, 21 Apr 2005 14:36:07 GMT, "Howard Brazee" <howard@brazee.net> wrote:
>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.
How do I remove VB macros from a spreadsheet?
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
"Howard Brazee" <howard@brazee.net> wrote in message
news:eW361pbSFHA.1232@TK2MSFTNGP15.phx.gbl...
> How do I remove VB macros from a spreadsheet?
On 25-Apr-2005, "Chip Pearson" <chip@cpearson.com> wrote:
> 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.
On 25-Apr-2005, "Howard Brazee" <howard@brazee.net> wrote:
> > 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.
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.
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
On Tue, 3 May 2005 19:34:49 GMT, "Howard Brazee" <howard@brazee.net> wrote:
>
>On 25-Apr-2005, "Howard Brazee" <howard@brazee.net> wrote:
>
>> > 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.
>
>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.
Debra Dalgleish has nice instructions at:
http://www.contextures.com/xlfaqMac.html#NoMacros
Howard Brazee wrote:
>
> On 25-Apr-2005, "Howard Brazee" <howard@brazee.net> wrote:
>
> > > 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.
>
> 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.
--
Dave Peterson
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks