 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2007strip away the directory structure on file name
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 > 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" 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 > Try something like this: > [quoted text clipped - 18 lines] > > \file.hex". is there a way in a macro to strip away the directory > > structure and leave only "file.hex" 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 > Unfortunate choice of function name....GetFileName is a method of the > FileSystemObject. It's generally a bad idea to reuse an existing item. [quoted text clipped - 32 lines] >> > \file.hex". is there a way in a macro to strip away the directory >> > structure and leave only "file.hex" 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 What version of Excel do you have? Rick On Sep 23, 1:52 am, "Rick Rothstein \(MVP - VB\)" <rickNOSPAMn...@NOSPAMcomcast.net> wrote: > > "Sub or function not defined" > > Error message I am getting and the following words are highlighted on [quoted text clipped - 6 lines] > > Rick Excel 97 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. > On Sep 23, 1:52 am, "Rick Rothstein \(MVP - VB\)" > <rickNOSPAMn...@NOSPAMcomcast.net> wrote: [quoted text clipped - 10 lines] > > Excel 97  SignatureDave Peterson Thanks Dave worked beautifully as usual. >> > Error message I am getting and the following words are highlighted on >> > the various versions above. [quoted text clipped - 5 lines] > > Excel 97 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. On Sun, 23 Sep 2007 14:04:49 -0400, "Rick Rothstein \(MVP - VB\)" <rickNOSPAMnews@NOSPAMcomcast.net> wrote: >>> > Error message I am getting and the following words are highlighted on >>> > the various versions above. [quoted text clipped - 12 lines] > >Rick
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.