How to insert the path for a workbook 1

[jercisneros]

I have a lot of excel files that get lost on our network. I can put the filename but I also want to be able to put the full path at the footer of the page. I tried using help for this but shows nothing. Is there a way to do this in excel?

Using Excel 97

Any help would be greatly appreciated.

Jerry

 

[DaleWatson123321]

Jerry,

Here are the steps for one method...

1) On a separate sheet (out of the way), enter the following formula:
=CELL("filename",C1)
(The reference to a cell can be to "any" cell nearby.)

2) Assign a range name to the cell containing the formula. In the VBA code, I use the range name "hdr_formula".

3) Include the following VBA code prior to printing...

Sub Set_Footer()
hdr = Range("hdr_formula&quot.Value
ActiveSheet.PageSetup.LeftFooter = hdr
End Sub

I hope this helps. Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

P.S.

If you don't want the "Sheet" name to show after the path\filename, then place the formula on a separate sheet, and change the "sheet tab" name to "a couple of space characters".

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[jercisneros]

Thanks Dale! You gave me the solution.

 

[Draig]

Another way to extract just the path (no sheet name) is to include a second formula, namely :
=MID(C1,1,FIND("]",C1))
and name that cell (hdr_formula). Barborne
Worcester
UK

 

[rmikesmith]

Another way to add full path & filename without using worksheet cell:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
  ActiveSheet.PageSetup.LeftFooter = ThisWorkbook.FullName
End Sub

Note: The BeforePrint event procedure must be in the ThisWorkbook code module.


Regards,
Mike

 

[jercisneros]

Man you guys are awesome, thanks for all your suggestions they all seem to work.

Jerry