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.