DeanP
06-17-2019, 06:30 AM
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?
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?