problem with fullpath in footer

    Hi,
    I use the following code to add the pathname in a footer.


    Private Sub Workbook_beforePrint(cancel As Boolean)


    For Each sht In ActiveWorkbook.Sheets
    Ampersand = Chr(38)
    Quote = Chr(34)
    FooterFormat = "Arial"
    FooterTextSize = "8"
    FooterText = ActiveWorkbook.FullName
    Formatting = Ampersand & Quote & FooterFormat & Quote & Ampersand & FooterTextSize
    FOOTER = Formatting & FooterText
    sht.PageSetup.LeftFooter = FOOTER
    Next sht


    End Sub


    But sometimes it gives a result like
    P:\test\demo.xls and another workbook with the same code gives
    \\zwy\public\test\demo1.xls.
    Sometimes the code gives the drive letter of the server, in this case P, and on another workbook it gives the fullname of the server. :(
    I want to see the driveletter and not the fullname (P:\test\demo.xls ). How does this come and what can I do about it?


    Thanx;
    Fluppe

    Is the workbook being opened on the same machine? If a drive has been mapped on one machine it will return the drive letter, if a drive has not been mapped it will return the UNC path.


    Bruce

    What do you mean with "mapped"?


    I think I have a solution for my problem.
    I shall test the footertext on the pathname of the server and then replace it with the text, in this case, P:\.

    When you map a drive it acts as a local drive like your "C:" Drive. On your desktop if you right click on "Network Neighborhood" or "My Network Places" and click on "Disconnect Network Drive" (DO NOT CLICK OK on next window, CLICK CANCEL), if you have any mapped drives on your machine they will appear in another window, otherwise you will get an error message saying "There Are No Network Drives Connected to this machine".


    If there is a mapped drive then replacing it with the P: will work, otherwise it will not recognize the P: Drive.


    To connect or make a mapped drive right click again on "Network Neighborhood" or "My Network Places" and click on "Map Network Drive" you will then be prompted to select a drive letter, in your case "P:" and you will also need to enter or browse to the share path


    Bruce

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login