Removing File Names From Paths

[GreyEyedWolf]

Hello, I was wondering if anyone had a good method for taking cells with file paths and removing the file names from them?

For example if I had the following path

C:\TEST\WORD.DOC I would want the cell to read C:\TEST to just contain the folder.

I have a long list of file paths with names and want to extract only the paths. Text to columns ends up being more work because some of the folder structures are very deep, so I would like an easier solution.

Thanks

 

[GTO]

Hi again,

You should be able to just change:

<font face=Courier New>Cell.Value = Left$(Cell.Value, IndexSlash - 1)</FONT>

Mark

 

[shg]

Hi shg,

I am not entirely sure that the fullnames are from the same PC as where the code is being run, but hopefully

Mark

Given that the OP says a directory could have a name that looks like a file, how else could you know?

 

[GTO]

Hi shg,

Certainly no way that I can think of.

@GreyEyedWolf:

I did forget to ask that. Do the paths/filenames listed on the sheet exist in the computer our workbook's code is running on?

Mark

 

[GreyEyedWolf]

Hi shg,

Certainly no way that I can think of.

@GreyEyedWolf:

I did forget to ask that. Do the paths/filenames listed on the sheet exist in the computer our workbook's code is running on?

Mark

The paths/filenames listed on the sheet in my case would exist in a network path location. Thanks

 

[GreyEyedWolf]

Hi shg,

Certainly no way that I can think of.

@GreyEyedWolf:

I did forget to ask that. Do the paths/filenames listed on the sheet exist in the computer our workbook's code is running on?

Mark

Do you know if it would be possible to make the macro delete up to the second slash. For example some of my paths include the archive name within them. For example.

C:\TEST\TEST.ZIP\WORK.DOC I would want to transform to just C:\TEST to exclude the archive name.

This could be accomplished by running your macro twice, but I was wondering whether it can be accomplished in a single macro?

Thanks

 

[GTO]

The paths/filenames listed on the sheet in my case would exist in a network path location. Thanks

Then please try Shg's code at post #8 I believe. This eliminates deleting a folder name by accident, as it is checking against an existing file, and thus the filename is required before it would trim.

Do you know if it would be possible to make the macro delete up to the second slash. For example some of my paths include the archive name within them. For example.

C:\TEST\TEST.ZIP\WORK.DOC I would want to transform to just C:\TEST to exclude the archive name.

This could be accomplished by running your macro twice, but I was wondering whether it can be accomplished in a single macro?

Thanks

Well, it would not accomplish anything on a second run, unless of course we really do have dots as part of folder names.

Anyways, could we take a step back here? Initially I was under the impression that a quick/short throwaway snippet could be used, but this now sounds as if it is something to be run every so often - against paths that are accessible and using existing filenames.

Could you provide more detail as to the logic one would use? For instance, in a range of fullnames, how would we know (and can provide rule(s) for our code to use) on what constitutes an archive folder?

Mark

 

[shg]

This puts the path in the next cell over:

Sub GEW()
    Dim cell        As Range
    Dim s           As String
    Dim iAttr       As Long

    On Error Resume Next

    For Each cell In Intersect(ActiveWindow.RangeSelection, ActiveSheet.UsedRange).Cells
        s = cell.Value2
        Do While Len(s)
            iAttr = Not vbDirectory
            iAttr = GetAttr(s)
            ' Debug.Print iAttr & " " & s
            If iAttr And vbDirectory Then Exit Do
            s = Left(s, InStrRev(s, "\", Len(s) - 1))
            If Right(s, 1) = "\" Then s = Left(s, Len(s) - 1)
        Loop

        cell(1, 2).Value = s
    Next cell
End Sub

 

[GreyEyedWolf]

Could you provide more detail as to the logic one would use? For instance, in a range of fullnames, how would we know (and can provide rule(s) for our code to use) on what constitutes an archive folder?
Mark

Sure so the file listings I'm working with aren't perfect for what I need, and archives contents are provided. I will have a listing like "C:\ZIPS\Archive.ZIP\Subdirectoryofzip\Document.XLS" which I will want to convert to C:\ZIPS to erase the content of the archive and just provide the folder it's in. In this example I would want to delete all the contents to the third "\". What I am currently doing is using Autofilter to select all the archive type paths which I can easily identify, and then running your macro by selecting visible cells. My only question was whether it was possible to manipulate the macro to delete towards a "\" further to the left than the first one? Thanks

 

[shg]

You can lead a horse to water ...

 

[GreyEyedWolf]

You can lead a horse to water ...

I now have the solutions that suffice for my needs thanks.