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
    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?


  • 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.


  • 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


Participate now!

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