Nelp with basic file sort algorithm in VBA?

Question

I have 2000+ files to sort. The naming convention is a # sign plus 5 digits, then a space, then a variable length string, then " - " followed by a three character month and four digit year w/ no spaces in between the month and year.
e.g. "#12345 Filename Arbitrary Word Selection - mar2005.pdf"
I need to be able to sort by the first 6 characters, and the last 7 characters, minus the ".pdf" or other extention. What would be an easy way to do this in VBA? I figured I could just read the filename with a string function that returns the character at a certain position in the string, but I cant find the function anywhere, and im getting frustrated. If anyone can help me out, id appreciate it.

Answer 1

so basically, you want to scrap everything in the filename except the #number and the date. what you would want to do is to concatenate using the Left() and Right() functions for strings.

the syntax for Left() is:

stringSink = Left(stringName, numChars)

where stringName is the name of the string which we want to process (in this case, the filename) and numChars is the number of characters which we want to take from the start of stringName. this function returns stringSink, but you can name it anything you want of course.

Right() works the same way, except that it starts from the END of stringName.

so in your case, we would want to first, shave off the extension of the file, then use Left() and Right() to get the characters we want to sort from.

if you're sure that all the extensions of your filenames are a dot followed by 3 characters, then this would be easy. you would just utilize the Len() function to count the number of characters in the filename, then adjust accordingly.

since we want to shave off exactly 4 characters from the end of the filename (this would be the extension), we could use something like:

modifiedFilename = Left(originalFilename, Len(originalFilename) - 4)

this would give us a string, minus the 4-character-long extension. if the extensions are not necessarily 4 characters long (like with .gz or something), you would have to search within the string first for the start of the extension. you could do this with a Do....Loop statement.

the idea would be to look at the characters of the filename one at a time and find the dot (which marks the start of the extension).

you could try something like:

counter = 1
Do
if Mid(originalFilename, counter, 1) = "." then exit Do
counter = counter+1
Loop

the Mid() function works much like Left() and Right(), except that you get to specify where in the string it starts to get characters (the second argument) and how many characters to return afterwards (the third). to illustrate, Mid("Application", 3, 4) would return "plic".

right after the above Do...Loop block, we could then scrap the extension accordingly, using the value of (counter):

modifiedFilename = Left(originalFilename, counter - 1)

remember that when the program exits the Do...Loop block, (counter) holds the value as to where the dot in the filename is. we add a "-1" to (counter) because we don't want to include the dot in our modified filename.

having scrapped the extension using whatever method above, you can now proceed to using Left() and Right() accordingly.

finalModifiedFilename = Left(modifiedFilename, 6) & Right(modifiedFilename, 7)

from here on, it should be easy to sort it out then. do your stuff. ^_^

Answer 2

Just use for the first 6 Characters the Left Function and for the last 7 Characters the Mid Function e.g

mySortOrder = Left(filename, 6) &
Mid(filename, Len(filename -11), 7)

To make it a little better
You could say
Dim myMonth as Variant 'Vb6'
Dim myMonth as String() 'VB.Net'
Dim myMonthNun as Integer

myMonth = Split(filename, "-")
Select Case Left(myMonth(1), 3)
Case "jan"
myMonthNum = 1
Case "feb"
myMonthNum = 2
....
End Select


mySortOrder = Left(filename, 6) & myMonthNum & Mid(filename, Len(filename -8), 4)


Then you can Order it by mySortOrder

Helmut