RUNTIME 13: TYPE MISMATCH ERROR

[DeanP]

I'm using the code below to version control an excel file:

Sub SaveNewVersionExcel()
Dim fileName As String
Dim index As Long
Dim ext As String
Dim folderPath As String

    arr = Split(ActiveWorkbook.Name, ".")
    ext = arr(UBound(arr))

    fileName = ActiveWorkbook.FullName

        If InStr(ActiveWorkbook.Name, "v") = 0 Then
            fileName = ActiveWorkbook.Path & "\" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1) & "v" & ext
                End If
        
        Do Until Len(Dir(fileName)) = 0
            index = index + 1
                index = CInt(Split(Right(fileName, Len(fileName) - InStr(fileName, "v") - 1), ".")(0))
                    Loop
    
    ActiveWorkbook.SaveAs (fileName)
    
End Sub

However, I get a 'Type Mismatch' error which I am not able to resolve here:

index = CInt(Split(Right(fileName, Len(fileName) - InStr(fileName, "v") - 1), ".")(0))

Any advice?

[Uncle Gizmo]

My guess is that this part of the code:-

Split(Right(fileName, Len(fileName) - InStr(fileName, "v") - 1), ".")(0)

Is returning text, so let's say it returns the text:- "MyFile"


Then you perform a conversion to try and convert that to a number (integer):- index = CInt("MyFile") because you are passing text to be converted instead of numbers, then it fails with the error message RUNTIME 13: TYPE MISMATCH

[p45cal]

You're probably wanting to do something like:

Sub SaveNewVersionExcel()
Dim fileName As String
Dim index As Long
Dim ext As String
Dim folderPath As String

arr = Split(ActiveWorkbook.Name, ".")
ext = arr(UBound(arr))

fileName = ActiveWorkbook.FullName

If InStr(ActiveWorkbook.Name, "v") = 0 Then
  fileName = ActiveWorkbook.Path & "\" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1) & "v1." & ext
Else
  Do Until Len(Dir(fileName)) = 0
    dot = InStrRev(fileName, ".")
    vee = InStrRev(fileName, "v") + 1
    index = Mid(fileName, vee, dot - vee)
    index = index + 1
    fileName = Left(fileName, vee - 1) & idx & "." & ext
  Loop
End If
ActiveWorkbook.SaveAs (fileName)
End Sub

but this is not robust since the line If InStr(ActiveWorkbook.Name, "v") = 0 Then will find the letter v anywhere in the filename, so this code depends on the only v in the name being the one just before the version number. A file name like myvelocipedeisthebestv23.xlsx will not work well.

[DeanP]

Thank you all for your help.

@p45cal - good advice re "v" - I will make that a bit more robust.