Splitting String in VBA

[kwvh]

I am sure I had read how to do this someplace, but cannot find the function(s). I have a string that comes in via:

Code: Select all

      

Dim strFile As String
Dim fDialog As Office.FileDialog
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
   With fDialog
      ' Allow user to make multiple selections in dialog box
      .AllowMultiSelect = False
            
      ' Set the title of the dialog box.
      .Title = "Please select CURRENT file."

      ' Clear out the current filters, and add our own.
      .Filters.Clear
'      .Filters.Add "Access Databases", "*.MDB"
'      .Filters.Add "Access Projects", "*.ADP"
      .Filters.Add "Excel Files", "*.xls"

      ' Show the dialog box. If the .Show method returns True, the
      ' user picked at least one file. If the .Show method returns
      ' False, the user clicked Cancel.
      If .Show = True Then
        strFile = .SelectedItems(1)
      Else
         MsgBox "You clicked Cancel in the file dialog box."
        GoTo Exit_cmd_Update_FromTAC_Click:
      End If
   End With
' strip out the path and file name as separate strings

I would like to separate the string strFile into the full path to the file, and the filename. A point in the right direction is GREATLY appreciated.

Ken

[JohnH]

You can user the InStrRev function will tell you the position of the last \ character from the end.
Then use the Left function to extract every before that - the path, and the Right function to find what comes after it - the file name.

So if the full filename path is StrFile

Path = left(StrFile,InstrRev(StrFile,"\")-1)
and the FileName is
FileName = Right(Strfile, Len(strfile)-InstrRev(StrFile,"\"))

[HansV]

Or a slightly simpler expression for the file name:

FileName = Mid(StrFile, InStrRev(StrFile, "\") + 1)

[HansV]

I have moved this thread from the Access forum to the VB/VBA/.NET forum because it's not specific to Access. The method for splitting a filename will work in all applications that have VBA.

[kwvh]

Hans,
as usual, dead on. Thanks!

Ken

[HansV]

Ken,

You have to thank JohnH for the solution! I merely posted an addition.

[kwvh]

John,

Thanks! Exactly what I was seeking! I thanked Hans instead of you, not noticing that you had posted to the solution, and Hans had only added to your post. I used your code to do exactly what I needed.

THANKS!
Ken

Path = left(StrFile,InstrRev(StrFile,"\")-1)
and the FileName is
FileName = Right(Strfile, Len(strfile)-InstrRev(StrFile,"\"))[/quote]