Referencing Partial Folder Path

[samuel.nunn]

If starting on a cell such as D7, how do I reference a PARTIAL folder path listed in cell F7? The path will vary in how many subfolders it has. I simply want to reference the path MINUS the last subfolder. (e.g. \\foldler1\folder2\FolderIDon'tWantToReference, but remember the names of the subfolders and the number of subfolders will vary.)

I imagine offset and len will need to be used, I'm just not sure on the specifics.

 

[Peter_SSs]

Not certain which way round your cells are, but see if this is what you want.

Excel Workbook
	D	E	F
7	\\foldler1\folder2		\\foldler1\folder2\FolderIDon'tWantToReference
Path

 

[samuel.nunn]

Thanks, but let me clarify a little.

I'll start by giving a little background. I've created a program that allows users to search any given path and then choose to move or zip certain folders. Moving a folder works fine, and so does zipping a folder. However, after a folder is zipped it will not show up in future queries by this program since this program only searches for folders (zipped folders are considered individual files).

I want those zipped files to show up, so I am aiming to create a Macro to automatically place that zipped file into a folder called "FolderName_ZIPPED".



Here is the code I'm trying to use:

Dim fso As Object
        Dim file As String, sfol As String, dfol As String
        file = cell.Offset(0, 2).Value & ".zip"  ' This will already be created
        sfol =  ' This is the source folder.  The zipped file will appear in _ 'C:\Documents and Settings\kq146c\My Documents\Sweeper Test\, NOT _ 'C:\Documents and Settings\kq146c\My Documents\Sweeper Test\New 'Folder (2) _ 
'since the "New Folder (2)" was what was actually zipped.  How _
'do I tell it  not to reference the last folder?  Some kind of len function? _ 'Can I tell it to somehow subtract from column F what is in column E?
        dfol =  ' This is the destination folder.  Same concept as above. _
'I need it to reference the full path minus the last folder.  And then have _
'it create a folder in that path.  The naming of the new folder should be _
'simple enough: cell.offset(0, 2) & "_ZIPPED".  It's just a matter placing _  'it in the correct folder.

        Set fso = CreateObject("Scripting.FileSystemObject")
        If Not fso.FileExists(sfol & file) Then
            MsgBox sfol & file & " does not exist!", vbExclamation, "Source File Missing"
        ElseIf Not fso.FileExists(dfol & file) Then
            fso.MoveFile (sfol & file), dfol
        Else
            MsgBox dfol & file & " already exists!", vbExclamation, "Destination File Exists"
        End If

Thanks!
Sam

 

[Peter_SSs]

Sam

Without getting into the specifics of your code, see if this helps

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> myPath()<br>    <SPAN style="color:#00007F">Dim</SPAN> FPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, SPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    FPath = Range("F7").Value<br>    SPath = Left(FPath, InStrRev(FPath, "\"))<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>