Help on VBA.

[mario]

I am using Excel 2000

I want to print the following information in the footer:

1. File path, File name and worksheet name
2. Print Date and time in the print out
3. Last Saved by user "xyz"
4. Last saved time
5. Page numer

I am currently using the following VBA code in my file:
VBAProject(mario.xls) > Microsoft Excel Objects >
ThisWorkBook

----------------------------------------------------------
Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
.LeftFooter = "&8" & _
LCase(ActiveWorkbook.FullName) & "; Worksheet:"
& " &A " & _
vbCrLf & "Printed on:" & Format(Now(), "dd-mmm-
yyyy")
.RightFooter = "&8" & "Page &P of &N"
End With
End Sub
----------------------------------------------------------

Please help me with

1. How to go to the next line in footer after printing
the file name & path. "vbcrlf" actually introduces an
empty line and then prints on the second line., which I
dont want

2. What will be the VBA code for Printing the date in the
format like 19 Sept 2003, 13:30

3. What will be the VBA code to print the "Last Saved By"
and "Last Saved time"

Please help.

Thanks

 

[Bob Phillips]

Please help me with

1. How to go to the next line in footer after printing
the file name & path. "vbcrlf" actually introduces an
empty line and then prints on the second line., which I
dont want

Try vbLf instead of vbCrLf

2. What will be the VBA code for Printing the date in the
format like 19 Sept 2003, 13:30

Can't get 4 char month I am afraid, but can do 3

Format(Now(),"dd mmm dddd, hh:mm")

3. What will be the VBA code to print the "Last Saved By"
and "Last Saved time"

ActiveWorkbook.BuiltinDocumentProperties("Last Author")
and
ActiveWorkbook.BuiltinDocumentProperties("Last Save Time"),

giving a final result of


Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
.LeftFooter = "&8" & _
LCase(ActiveWorkbook.FullName) & "; Worksheet:" & " &A " & _
vbLf & "Printed on:" & Format(Now(), "dd-mmm-yyyy ") & _
vbLf & "Saved: by " & ActiveWorkbook.BuiltinDocumentProperties("Last
Author") & _
" on " & Format(ActiveWorkbook.BuiltinDocumentProperties("Last Save
Time"), "dd mmm dddd, hh:mm")
.RightFooter = "&8" & "Page &P of &N"
End With
End Sub

 

[James S]

Hi Mario,

Bob's code for the date formatting should be:

Format(Now(),"dd mmm yyyy, hh:mm")

I'm sure he accidentally put "dddd" instead of "yyyy". You
could also use:

Format(Now(),"dd mmm yyyy, h:mm AM/PM")

If you want to show the time differently.

Regards,
James S

-----Original Message-----



Try vbLf instead of vbCrLf



Can't get 4 char month I am afraid, but can do 3

Format(Now(),"dd mmm dddd, hh:mm")


ActiveWorkbook.BuiltinDocumentProperties("Last Author")
and
ActiveWorkbook.BuiltinDocumentProperties("Last Save Time"),

giving a final result of


Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
.LeftFooter = "&8" & _
LCase(ActiveWorkbook.FullName) & "; Worksheet:" & " &A " & _
vbLf & "Printed on:" & Format(Now(), "dd-mmm- yyyy ") & _
vbLf & "Saved: by " & ActiveWorkbook.BuiltinDocumentProperties("Last
Author") & _
" on " & Format

(ActiveWorkbook.BuiltinDocumentProperties("Last Save

 

[Mario]

Hey Man,

Thanks a million. Every thing is perfect now.

Have a niece weekend.

Regards
Mario

-----Original Message-----



Try vbLf instead of vbCrLf



Can't get 4 char month I am afraid, but can do 3

Format(Now(),"dd mmm dddd, hh:mm")


ActiveWorkbook.BuiltinDocumentProperties("Last Author")
and
ActiveWorkbook.BuiltinDocumentProperties("Last Save Time"),

giving a final result of


Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
.LeftFooter = "&8" & _
LCase(ActiveWorkbook.FullName) & "; Worksheet:" & " &A " & _
vbLf & "Printed on:" & Format(Now(), "dd-mmm- yyyy ") & _
vbLf & "Saved: by " & ActiveWorkbook.BuiltinDocumentProperties("Last
Author") & _
" on " & Format

(ActiveWorkbook.BuiltinDocumentProperties("Last Save

 

[mario]

Thanks for your help. Can u suggest how I can save these
changes to default or template file, so that every file I
create have those information on the footer

Thanks

 

[Bob Phillips]

Mario,

Notwithstanding my previous typo.

Put the workbook event code into a new, pristine workbook, and save the
file as a template file called Book.xlt in the XLStart directory. Be aware
though, every time you New a workbook, it will throw up the macros dialog.

 

[mario]

Hi Bob,

One final help. What will be the VBA code for Created by
and Created on which i should be adding to your code.

Please help
Thanks