strip away the directory structure on file name

  • Thread starter Thread starter a924fan
  • Start date Start date
A

a924fan

I can now import a file and store the file name into a cell. Thanks
Dave. I get the directory structure too. "C:\My Documents\directory
\file.hex". is there a way in a macro to strip away the directory
structure and leave only "file.hex"
 
Try something like this:

Function GetFileName(strFullPath as String)
GetFileName = Mid(strFullPath ,InStrRev(strFullPath ,"\")+1,255)
End Function

To test:
In a sheet cell
=GetFileName("C:\My Documents\directory\file.hex")
returns: file.hex

Does that help?
***********
Regards,
Ron

XL2003, WinXP
 
Unfortunate choice of function name....GetFileName is a method of the
FileSystemObject. It's generally a bad idea to reuse an existing item.

Consequently, this would be better:
Function ShowFileName(strFullPath As String)
ShowFileName = Mid(strFullPath, InStrRev(strFullPath, "\") + 1, 255)
End Function

***********
Regards,
Ron

XL2003, WinXP
 
Your "spreadsheet formula" roots are showing... inside VBA, the third
argument to the Mid function is optional. If you leave it out, the remainder
of the text is returned. So, you could have written you active line this
way...

ShowFileName = Mid(strFullPath, InStrRev(strFullPath, "\") + 1)

As long as we are talking one-liner solutions, here are a few other ways to
do this (but probably not as efficiently)...

ShowFileName = Split(strFullPath, "\")(UBound(Split(strFullPath, "\")))

ShowFileName = StrReverse(Split(StrReverse(strFullPath), "\")(0))

ShowFileName = Replace(strFullPath, Left(strFullPath, InStrRev(strFullPath,
"\")), "")

Rick
 
Thanks guys
"Sub or function not defined"
Error message I am getting and the following words are highlighted on
the various versions above.
InStrRev
StrReverse
Split
 
"Sub or function not defined"
Error message I am getting and the following words are highlighted on
the various versions above.
InStrRev
StrReverse
Split
Click to expand...

What version of Excel do you have?

Rick
 
Dim cCtr As Long
Dim myStr As String
Dim myName As String
myStr = "c:\my documents\excel\book1.xls"
For cCtr = Len(myStr) To 1 Step -1
If Mid(myStr, cCtr, 1) = "\" Then
myName = Mid(myStr, cCtr + 1)
Exit For
End If
Next cCtr
MsgBox myName

Split, join, and instrrev were added in xl2k.
 
Error message I am getting and the following words are highlighted on
Excel 97
Click to expand...

Okay, that is why... none of the functions I made use of existed in your
version. For future reference when posting question on these newsgroups, it
would be a good idea to mention the version of Excel. That way, the answers
you received can be tailored to what your version.

Rick
 
Just for anyone's convenience, code for the functions for use in XL97:
http://support.microsoft.com/kb/188007
For my own organizational benefit I created a personal.xls sibling
EXTRA97.XLS with that; used Long instead of VbCompareMethod; topped it
with Global xlPasteColumnWidths; and gave it a
Private Sub Workbook_Open()
xlPasteColumnWidths = xlPasteFormats
End Sub

Other practical additions are welcomed.
 
Back
Top