CODEWITHSUNDEEP
excelvba
blahblahblah
blahblahblah

Reputation: 2447

VBA Find and replace part of file path

I'm having difficulty writing VBA to find and replace part of a file path. I need to isolate the filename without the file extension (i.e. replace everything including and to the left of the last \ and the .flac for the example below)

The type find and replace involves file paths that look like --

C:\Users\myname\Desktop\PhoneCallFolder1\123456789_20140101120101.flac
C:\Users\othername\Desktop\PhoneCallFolder2\123456789_19990101120101.flac

and the result should look like --

20140101120101
19990101120101

Thanks for your help. My existing code is below:

Columns("A:A").Select
Selection.Replace What:= _
    "C:\Users\myname\Desktop\PhoneCallFolder1\" _
    , Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:= _
    False, SearchFormat:=False, ReplaceFormat:=False

Selection.Replace What:= _
    ".flac" _
    , Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:= _
    False, SearchFormat:=False, ReplaceFormat:=False

Upvotes: 2

Views: 2889

Answers (3)

Bernard Saucier
Bernard Saucier

Reputation: 2260

The easiest way to do this, since you're always getting a 14 characters string, positionned 5 characters from the end, is the following. You could do it with a formula that would look like this.

For cell A1:

=LEFT(RIGHT(A1, 19), 14)

Then streth this formula all the way down.

I'll elaborate a vba way of doing this.

VBA : 

lastRow = Cells(Rows.Count, 1).End(xlUp).Row 'this only finds the last row
For x = 1 To lastRow 'This loops through all the rows
    Cells(x, 1) = Left(Right(Cells(x, 1), 19), 14)
Next

Upvotes: 0

Jeff1265344
Jeff1265344

Reputation: 123

If you need to handle your replacements one at a time you could use a couple functions like the following:

Function FileNameParse(strFile as String)
' Return a file name from a full path
FileNameParse = Left(strFile, InStrRev(strFile, "\") - 1)
End Function

Function FilePathParse(strFile as String)
' Return a directory from a full path
FilePathParse = Right(strFile, Len(strFile) - InStrRev(strFile, "\"))
End Sub

Upvotes: 0

chris neilsen
chris neilsen

Reputation: 53156

You can use a wild card to replace any path, like this

With Columns("A:A")
    .Replace What:= _
      "*\", _
      Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, _
      MatchCase:= False, SearchFormat:=False, ReplaceFormat:=False

    .Replace What:= _
      ".*", _
      Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, _
      MatchCase:= False, SearchFormat:=False, ReplaceFormat:=False
End With

Upvotes: 1

Related Questions