How do I insert a partial filename?

[Admin]

I am inserting a filename to my footer; however the file path is very long
and I wanted to condense the file name by only showing the last 2-3 "folder
tiers". Any ideas?

[Jim Cone]

A,
Using code is the only way I know to do it.
Jim Cone
San Francisco, USA

'--------------------------
Sub FillInFooter()
Dim strPath As String
Dim strFooter As String
'Number of folders to display in footer.
Const lngNUMBER As Long = 2
strPath = ActiveWorkbook.FullName
'Call the function that parses the file path.
strFooter = ShowPartOfPath(strPath, lngNUMBER)
'Add path to the right footer.
ActiveSheet.PageSetup.RightFooter = strFooter
End Sub

Function ShowPartOfPath(ByVal strPath As String, ByRef lngFolders As Long)
'Jim Cone - San Francisco, USA - 11/30/2005
Dim lngCount As Long
Dim lngItem As Long
Const strCHAR As String = "\"
'Determine number of "\" in the file path.
lngCount = Len(strPath) - Len(Application.Substitute(strPath, strCHAR, vbNullString))
'If file path has more than the desired number of folders then proceed.
If lngCount > lngFolders Then
lngItem = lngCount - lngFolders
lngCount = 0
'Loop thru filepath until correct "\" is found.
Do While lngItem > 0
lngCount = InStr(lngCount + 1, strPath, strCHAR, vbTextCompare)
lngItem = lngItem - 1
Loop
strPath = "..." & Right$(strPath, Len(strPath) - lngCount)
End If
ShowPartOfPath = strPath
End Function
'--------------------------


"Admin" <Admin@discussions.microsoft.com>
wrote in message
news:A1D6CBDA-DC02-468B-81AE-DCC290D0876E@microsoft.com
I am inserting a filename to my footer; however the file path is very long
and I wanted to condense the file name by only showing the last 2-3 "folder
tiers". Any ideas?