Extract string between values

[mflancour]

I have a For....Next statement that goes through folders on a drive looking for bmp files. I want to record each folder name, and only the folder name, as it is accessed.

Example
searches:
g:\stuff\folder1
finds:
file1.bmp , records folder1
file234.bmp , records folder1
reaches end of folder1
searches:
g:\stuff\folder23
finds:
file23324.bmp , records folder23
file2.bmp , records folder23
reaches end of folder23.......

not sure if that's clear or not basically I have a string
that can change and I want to be able to use something like a mid(string,length,start) type of function without knowing exactly where to "start" or what the "length" is.

 

[beetee]

In what do you want to record the folder name?

Do you expect to have lots of folder names, or just a few?

Assuming just a few folder names stored in an array

Dim FolderNameArray() as string
dim FolderNameArrayEls as integer
dim FolderNameArraySize as integer

sub AddFolderName(TheName as string)
dim counter as integer
for counter = 0 to FolderNameArrayEls - 1
if FolderNameArray(counter) = TheName then
exit sub
endif
next counter
if FolderNameArrayEls >= FolderNameArraySize then
FolderNameArraySize = FolderNameArraySize + 16
redim preserve FolderNameArray(FolderNameArraySize)
endif
FolderNameArray(FolderNameArrayEls) = TheName
FolderNameArrayEls = FolderNameArrayEls + 1
end sub

Just call AddFolder name whenever you hit a .bmp file.

An optimization would be to store the folder names in sorted order, and binary search the list.

Another way to handle this is to create a table with a unique index, and try to add a folder name each time.

Based on my understanding of this problem, I don't see why you need any string manipulation functions.

 

[mflancour]

I don't think this is quite what I am looking for. Then again I'm not sure, I'm not entirly sure what this code is doing. When I run it I get a null string.

Let me take another shot at explaining this just in case. The reason I need folder names is because these are users directories. The name of the folder is the users id. What I really want is to record the user id every time a bmp is found. This would meen that no mater what sub folder I am in at the time it would always record the the name of the folder that represents the user ID. My initial idea was to use a mid() statement that started at the end of the folder above the user name folder and ended at the first "\" but I couldn't figure out how to specify that.

Thanks for the help hopefully I'm clearer about what I'm looking for this time. If the above code is still viable please help me understand what it is doing so I can try to figure out why it is not working.

 

[beetee]

OK, I think I got it.

The code I posted will be useful to help you find a unique set of folder names.

However, to find just the last name in a full path, you do need to go with instr.

following is some very rough code that should get you started. It is not guaranteed to work:

' find the last slash
dim SlashPos as integer
slashpos = 1
dim LastSlash as integer
do while true
slashpos = instr(slashpos, PathName, "\&quot
if slashpos = 0 then exit do
lastslash = slashpos
slashpos = slashpos + 1
loop
' extract the lastname
dim UserName as string
UserName = right(PathName, len(PathName) - slashpos)


then use the array code posted eariler to construct an array of user names. Or add the user names to a table with a primary key of user names.

HTH

 

[mflancour]

Will this work if I have something like the following:

g:\users\mlancour\thisfolder\thatfolder\fil1.bmp
g:\users\mlancour\anotherfolder\picture.bmp
g:\users\jdoe\picture2.bmp

from these paths I would want to record

mlancour file1.bmp
mlancour picture.bmp
jdoe picture2.bmp

 

[beetee]

I was under the impression you wanted to just record folder names (aka user directories). Do you want to record file names (images, e.g. .bmp) as well?

In any case, where do you want to store this information? or, perhaps more aptly, what do you want to do with the user directories? Combo box?

 

[mflancour]

Here is my code will likly be best way to show what I am tryig to do. I actually did find a way to do what I was looking for too it's in the "GetUserID:" section. Thank you for the help, and if you can think of a more efficient way to do this that would be great too.

Public Function FindPics()
Dim FS 'As FileSearch
Dim i As Integer, j As Integer, char As String
Dim dbsStats As Database
Dim rstUDrive As Recordset
'Dim fldFileName As Field, fldFileSize As Field, fldDateLast As Field, fld
Dim strFileName As String, strFileSize As String, DateLast As Date, strUser As String, strExtension As String
Dim strPath

Set dbsStats = CurrentDb
Set rstUDrive = dbsStats.OpenRecordset("tempUDriveStats", dbOpenDynaset)
'Set fldFileName = rstUDrive.Fields("FilePath&quot
'Set fldFileSize = rstUDrive.Fields("FileSize&quot
'Set fldDateLast = rstUDrive.Fields("LastModified&quot
Set FS = Application.FileSearch

With Application.FileSearch
.NewSearch
.LookIn = "u:\BACKUPDATA\"
.SearchSubFolders = True
.FILENAME = "*.*"
.Execute

FindPicks:
For i = 1 To .FoundFiles.Count
strFileName = .FoundFiles.Item(i)
strExtension = ""
If strFileName Like "*.bmp" Then
strExtension = "bmp"
Else
If strFileName Like "*.jpg" Then
strExtension = "jpg"
Else
If strFileName Like "*.jpeg" Then
strExtension = "jpeg"
Else
If strFileName Like "*.gif" Then
strExtension = "gif"
Else
If strFileName Like "*.tif" Then
strExtension = "tif"
Else
If strFileName Like "*.tiff" Then
strExtension = "tiff"
Else
GoTo GETNEXTFILE
End If
End If
End If
End If
End If
End If

GetUserID:
strUser = ""
j = 15 'starting path (.lookin) length +1
char = ""
Do While Not char = "\" 'retrieves name of folder after the starting path one letter at a time
char = VBA.Mid(strFileName, j, 1)
j = j + 1
If char <> &quot;\&quot; Then
strUser = strUser & char
End If
Loop

RecordToTable:
strFileSize = FileLen(.FoundFiles.Item(i))
DateLast = FileDateTime(.FoundFiles.Item(i))
rstUDrive.AddNew
rstUDrive!FILEPATH = strFileName
rstUDrive!FileSize = Val(strFileSize)
rstUDrive!LastModified = DateLast
rstUDrive!UserName = strUser
rstUDrive!FileExtension = strExtension
rstUDrive!WorkRelated = True
rstUDrive.Update

GETNEXTFILE:
Next i
MsgBox &quot;all done&quot;
End With
End Function