Consulting

Results 1 to 4 of 4

Thread: RUNTIME 13: TYPE MISMATCH ERROR

  1. #1
    VBAX Regular
    Joined
    Nov 2018
    Location
    London, U.K.
    Posts
    99
    Location

    RUNTIME 13: TYPE MISMATCH ERROR

    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?

  2. #2
    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

  3. #3
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,668
    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.
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #4
    VBAX Regular
    Joined
    Nov 2018
    Location
    London, U.K.
    Posts
    99
    Location
    Thank you all for your help.

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •