CODEWITHSUNDEEP
vbaexcel
JeanLo
JeanLo

Reputation: 101

VBA loop throught cells and extract the file name from cells in excel sheet

I have an excel sheet that in each cell in column A , the path of a source folder : 

         column A
P:\Desktop\Source\Test1-folder\file1.txt
P:\Desktop\Source\Test1-folder\file2.txt

and i want to take just the file name (file1.txt) for each file , how can i do it ? Can you help me please ?

 For Each oFolder In oSourceFolder.SubFolders
   lastcol = Cells(1, Cells.Columns.Count).End(xlToLeft).Column
   For Each oFile In oFolder.Files
      If Dir(destinationFolder, 16) = "" Then MkDir (destinationFolder)
      For k = 1 To dercol
          numrows = worksh.Cells(Rows.Count, icol).End(xlUp).Row
          For w = 2 To numrows
         filepath = worksh.Cells(w, icol).Value

But this one loops through the files, not the cells. How can I loop through the cells?

Upvotes: 0

Views: 701

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

If all you want is the file name, you can do this with a simple worksheet formula:

=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",99)),99))

Upvotes: 0

RDJ
RDJ

Reputation: 191

Try this:

' Get the sheet
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)

' How you will slash it
Dim strSlash As String
strSlash = "\"

' Set the range for your tool
Dim rngFiles As Range
Dim rngCell As Range
Dim lngMaxRow As Long
lngMaxRow = Range("A" & Rows.Count).End(xlUp).Row
Set rngFiles = Range("A1:A" & lngMaxRow)

' Grab it from the rear
For Each rngCell In rngFiles.Cells
    Debug.Print Right(rngCell.Value, Len(rngCell.Value) - InStrRev(rngCell.Value, strSlash))
Next

Upvotes: 1

Related Questions