Static Footer Filepath

[jomili]

using the information found at http://msdn.microsoft.com/en-us/libr.../ff822794.aspx I'm using the below macro to put the Filepath and Workbook name into the sheets of the active workbook. Everything works as designed, except when I send the workbook through email, and the person on the other end opens the workbook, the filepath is to their Temporary Internet Files instead of the path to where the workbook is saved. How do I get the workbook path to stay static?

Please Login or Register  to view this content.

[shg]

Perhaps

Please Login or Register  to view this content.

[JosephP]

you'd have to literally put the path in there rather than use the setup codes

Please Login or Register  to view this content.

of course it won't update if Julie saves the file somewhere else either :-)

[jomili]

SHG,
using your idea, constructing the line like this:

Please Login or Register  to view this content.

the result is

Please Login or Register  to view this content.

There's a long space, moving the path to almost the center of the sheet, instead of to the left.

Joseph P,
using your idea, the result is

Please Login or Register  to view this content.

, which stays static through email. I think you've got it. Now, any idea how to remove the comma from in front of the "C"?

[JosephP]

remove it from before the &10

Please Login or Register  to view this content.

[jomili]

Sweet! That does the trick.

going to "of course it won't update if Julie saves the file somewhere else either :-) ", how would we do this if wanted it to be global, tied to any workbook/save event that she does, so on a save it would be refreshed with the new file path?

[JosephP]

you'd have to put it in an add-in and trap the app level workbookbeforeprint event

add a class module called AppEvents to a new workbook and add this code

Please Login or Register  to view this content.

then in the ThisWorkbook module add

Please Login or Register  to view this content.

then save the workbook as an add-in

[JosephP]

by the way in the original code this line

Please Login or Register  to view this content.

oughta be

Please Login or Register  to view this content.

;-)

[jomili]

Thanks for catching my "WS/Activesheet" error, and thanks so much for including the code for the global solution. I don't know whether I'll use it, as I can imagine there would be times she WOULDN'T want the file path on the worksheet, so on "on demand" macro would suit her better. But I'll let her make the final call.

Thanks for all your help!