CODEWITHSUNDEEP
excelvba
Geographos
Geographos

Reputation: 1466

VBA Excel - invalid procedure call or argument when saving the file

Good morning,

I am facing the following issue. When I try to save my file under the new version, then I am getting the error from time to time, which states about: Invalid procedure call or argument - sChangeVersiojn enter image description here

See my code below:

 Sub Version_save()
 ' Saving file under the newer version wothout changing its name
 On Error GoTo E_Handle
 Dim strFileName  As String
 Dim strFileExt As String
 strFileName = ThisWorkbook.Name
 strFileExt = Mid(strFileName, InStrRev(strFileName, "."))
 strFileName = Left(strFileName, InStrRev(strFileName, ".") - 1) '   get rid of the file extension (it should be .xlsm)
 strFileName = Left(strFileName, InStrRev(strFileName, "V") - 1) '   now go back to the last "V" in the file name
 strFileName = strFileName & "V" & ThisWorkbook.Worksheets("Frontsheet").Range("D38") & ".0" & strFileExt
 ThisWorkbook.SaveAs ThisWorkbook.Path & "\" & strFileName
 Debug.Print strFileName
 Debug.Print strFileExt
 sExit:
     On Error Resume Next
     Exit Sub
 E_Handle:
     MsgBox Err.Description & vbCrLf & vbCrLf & "sChangeVersiojn", vbOKOnly + vbCritical, "Error: " & 
     Err.Number
     Resume sExit
 End Sub

According to thread below:

Run Time Error 5 - Invalid Procedure Call or Argument

I should change the quotes, but I don't really know where should I do it.

Is there any way to get rid of this error?

Upvotes: 0

Views: 747

Answers (1)

chris neilsen
chris neilsen

Reputation: 53126

Your code assumes your file name matches a certain pattern, then goes on to provide an unhelpful error message if it does not.

You could test the file name matches a pattern, then take appropriate action if it does not. If it does, continue and do the name change and SaveAs.

Add this to the top of your Sub, and change the pattern to suit your needs.

'Test if file name contains a "V", and not as first or last character in the name.  Also require a .xlsm or .xlsx extension
If Not ThisWorkbook.Name Like "*?V?*.xls[mx]" Then
    MsgBox "File Name """ & ThisWorkbook.Name & """ does not match required pattern", vbOKOnly, "Error"
    ' File name does not conform.  Now What?
    GoTo sExit
End If

Upvotes: 1

Related Questions