Extracting out the main directory name

[ghudson]

I need some help extracting out the main directory name from a string that contains the full path name and file name. Using the Mid and Instr functions are giving me a headache for I can not get just the main directory name.

Here is how the strings will look in the FilePathName field of my tblFiles table...

L:\Data\Subs\test.xls
X:\Files\Testing\123\one.txt
C:\My Documents\Access\DBs\Division.mdb

And I need to update the DirectoryName field in the tblFiles table with just the main directory name like this for those records...

Data
Files
My Documents

Any suggestions on how I can do this? Thanks in advance for your help!

 

[Travis]

use Split

Dim ArrDir as Variant

ArrDir=Split( L:\Data\Subs\test.xls,"\")

'ArrDir will look as follows:
'ArrDir(0)="L:\"
'ArrDir(1)="Data"
'ArrDir(2)="Subs"
'ArrDir(3)="test.xls"

'The Main Directory will then be in ArrDir(1)

This will even work for UNC paths.

 

[pono1]

Function GetRootFolderName(Text as String) as String

	Dim strParts() As String
	strParts = Split(Text, "\")

	GetRootFolderName = strParts(1)

End Function

Regards,
Tim

 

[ghudson]

That looks interesting but is Split an Access function? I am using Access 97 and that is bugging on me.

I tried...

Dim ArrDir As Variant
ArrDir = Split("L:\Data\Subs\test.xls", "\")
MsgBox ArrDir(1)

...but Access 97 is hi-liting the Split function name

I will never know how many subdirectories the files will be within but I only need to return the name of the first main directory.

Thanks for any help and suggestions!

 

[pono1]

ghudson,

The Split function works OK in Access 2000. But since it doesn't work in A97, I guess you'll have to muscle through the string using a loop, grabbing the text in between the first two slashes from the left. Something like this should get you close:

Dim Text As String
Dim i As Integer
Dim slashCount As Integer
Dim rootfolder As String

text = "X:\Files\Testing\123\one.txt"

For i = 1 To Len(text)

        If SlashCount = 1 And Mid(text, i, 1) <> "\" Then
            rootfolder = rootfolder & Mid(text, i, 1)
        End If


        If Mid(text, i, 1) = "\" Then
          
           SlashCount = SlashCount + 1
              
               If SlashCount = 2 Then
                    Exit For
               End If
           End If
       
Next i

msgbox RootFolder

Regards,
Tim

 

[ghudson]

Thanks Tim. That works for Access 97!

 

[pono1]

Happy to hear it.

By the way, I should have said the Split function works OK on a PC that's running Access 2k and VB6 -- I wonder if VB6 didn't update an Access VB library on this PC with one of its own... Can be sneaky, this software.

Regards,
Tim