To get specific part of filename from a folder

[akshay2007]

Hi,

I have a query where I need to search for a specific part of a filename by going through all the filenames within a folder specified.

It can be like this -
consider path as E:\VBA\MyTestFiles
Also consider number of files here at this folder path or subfolders within this path.

e.g
we can have one File name as testfile-1.xls
If I had more data & named that file as testfile-2.xls

Now in case user had opened a file testfile-1.xls ...I need to check the name for this workbook and check if file with more data entry is present or not....in short testfile-2.xls is present or not.

I want user to always use updated file with latest data entry done....& its number may go ahead ...e.g testfile-5.xls

As specified I need to make the check for the number -1 & if I have -2 I need to convey this to user.

I need this check to be done for files within the specified folder....can anyone guide me here?

Thanks in advance!
Aky

[gowthron]

Aky,

Below is a basic bit of code which will read through the files in a specified directory and:

1. Strip of the Extension
2. Check for a "-" in the file name
3. Conver file number to int
4. Store the highest number.

Hope this helps.

G.



Sub Check_File()
Dim lsFileName As String
Dim lsMyPath As String
Dim lintMax_File As Integer
Dim lstrTemp As String
lsMyPath = "E:\VBA\MyTestFiles\"
lsMyPath = "C:\temp\"
lsFileName = Dir(lsMyPath)
If lsFileName = "" Then
Exit Sub
End If
lintMax_File = 0
Do While lsFileName <> "" ' Start the loop.
lstrTemp = Left(lsFileName, InStr(1, lsFileName, ".") - 1)
If InStr(1, lstrTemp, "-") Then
If CInt(Right(lstrTemp, Len(lstrTemp) - InStr(1, lstrTemp, "-"))) > lintmaxfile Then
lintMax_File = CInt(Right(lstrTemp, Len(lstrTemp) - InStr(1, lstrTemp, "-")))
End If
End If
lsFileName = Dir ' Get next entry.
Loop
MsgBox "The File number you should be using is: " & lintMax_File, vbInformation + vbOKOnly
End Sub

[mdmackillop]

This will error if your file names do not contain "-" or more than one "."
It will not show the correct name if you use a format such as Testfile-08.xls



Option Explicit

Sub MaxFile()
Dim fName As String
Dim fPath As String
Dim Num As Long
Dim t
Dim MaxNum As Long
fPath = "C:\AA\"
fName = Dir(fPath)
Do While fName <> ""
t = Split(fName, ".")(0)
Num = Split(t, "-")(1)
If Num > MaxNum Then MaxNum = Num
fName = Dir
Loop
MsgBox Split(t, "-")(0) & "-" & MaxNum, vbInformation + vbOKOnly
End Sub



Hi Gowthron
Welcome to VBAX
You can format your code as shown using the green VBA button.

I tried your code but it failed with TestFile-11, showing 8 as greater

Regards
MD

[Kenneth Hobs]

For sequential names, I used Window's convention of Filename (2).xls in this kb entry. http://www.vbaexpress.com/kb/getarticle.php?kb_id=1041 (http://www.vbaexpress.com/forum/../kb/getarticle.php?kb_id=1041)

[gowthron]

Thanks MD. For the Tip and spotting the error. (Variable lintmaxfile was missing an underscore). Once this was fixed it resolved the issue.



Sub Check_File()
Dim lsFileName As String
Dim lsMyPath As String
Dim lintMax_File As Integer
Dim lstrTemp As String
lsMyPath = "E:\VBA\MyTestFiles\"
lsMyPath = "C:\temp\"
lsFileName = Dir(lsMyPath)
If lsFileName = "" Then
Exit Sub
End If
lintMax_File = 0
Do While lsFileName <> "" ' Start the loop.
lstrTemp = Left(lsFileName, InStr(1, lsFileName, ".") - 1)
If InStr(1, lstrTemp, "-") Then
If CInt(Right(lstrTemp, Len(lstrTemp) - InStr(1, lstrTemp, "-"))) > lintMax_File Then lintMax_File = CInt(Right(lstrTemp, Len(lstrTemp) - InStr(1, lstrTemp, "-")))
End If
lsFileName = Dir ' Get next entry.
Loop
If lintMax_File = 0 Then
MsgBox "There are no files with the expected naming convention!", vbInformation + vbOKOnly
Else
MsgBox "The File number you should be using is: " & lintMax_File, vbInformation + vbOKOnly
End If
End Sub


Cheers,
G.

[mdmackillop]

Another tip.
Always use Option Explicit; that would have uncovered the error.