VBA code for opening a file with a variable version number in the file name

I have a file that I save with a new version number each time I make major changes. The file name currently is: "Telephony Equipment Inventory v26 (Summary).xlsm". The "26" is the variable number. Can someone give me the vba code to ensure I open the file with the highest version number?

Trish

xladept, this failed at the "U = Dir()" line of code. I liked this because it was short, but if this won't work, I will give the longer code a try.

Hi Trish,

Try the following written and tested using Excel 2003:

Sub MainProgram()

  Dim sPath As String
  Dim sFileName As String
  
  'Generate the file path (it is assumed that all the files are in the same folder)
  'as the Excel file that is running this code
  'Change this as required for your specific needs
  sPath = ThisWorkbook.Path & "\"

  sFileName = FindHighestNumberedSoftwareVersion(sPath)
  If Len(sFileName) = 0 Then
    MsgBox "No Matching File was found"
    Exit Sub
  Else
    MsgBox "The highest version file is '" & sFileName & "'."
  End If

  'Your code here

End Sub



Public Function FindHighestNumberedSoftwareVersion(sPath As String) As String
  'This finds the highest numbered software version of a file.
  'It is assumed that all files reside in the same folder
  '
  'The file name with the highest version number is returned
  'If there is no match, then the NULL string "" is returned.
  '
  
  'It is assumed that the version numbers go from '01' to '99'
  '
  'I personally prefer 3 or 4 digit version numbers e.g. '0001' to '9999'.
  
  'Question marks mean match any character in that position
  Const sMasterFileName = "Telephony Equipment Inventory v* (Summary).xlsm"   'This works for 1 or 2 digit file versions
  Const sVersionMASK = "v* (Summary).xlsm"
  
  
  Dim iVersionStartPosition As Integer
  Dim iVersion As Integer
  Dim iVersionMax As Integer
  
  Dim s As String
  Dim sHighestVersionFileName As String
  Dim sOldVersion As String
  Dim sSearchSpec As String
  
  
  'Obtain the version start position in the file name
  'Both of the following lines do the same thing
  'The 2nd line is included so you will understand better what is going on
  iVersionStartPosition = InStr(sMasterFileName, sVersionMASK) + 1
  iVersionStartPosition = InStr(sMasterFileName, "v* (Summary).xlsm") + 1

  
  'Generate a search specification for the file
  'Both of the following lines do the same thing
  'The 2nd line is included so you will understand better what is going on
  sSearchSpec = sPath & sMasterFileName
  sSearchSpec = sPath & "Telephony Equipment Inventory v* (Summary).xlsm"
  
  'Loop through all the files that match the file specification
  's is the name of the next matching file (without the folder name)
  sHighestVersionFileName = ""
  s = Dir(sSearchSpec)
  Do While s <> vbNullString
    'Debug.Print s
    
    'Extract the old version of the file name as text (two characters in length)
    'The version number is two characters long (either '12' or ' 2')
    sOldVersion = Mid(s, iVersionStartPosition, 2)
      
    'Convert the text version number to numeric if possible
    If IsNumeric(sOldVersion) = False Then
      MsgBox "Bad file Version Number found - processing will continue." & vbCrLf & _
             "Folder: " & sPath & vbCrLf & _
             "File: " & s
    Else
      'We have a match - save the version number if it is greater than the previous max value
      iVersion = CInt(sOldVersion)
      If iVersion > iVersionMax Then
        iVersionMax = iVersion
        sHighestVersionFileName = s
      End If
    End If
      
    s = Dir 'Find the next matching file
  Loop
  
  'Return the highest version file name
  FindHighestNumberedSoftwareVersion = sHighestVersionFileName
  
End Function

Lewis

I was unable to get your code to work xladept, so I used Lewis' code. Worked perfectly. Thank you, this will come in very handy again.

Trish