Run-time error = '5' : invalid procedure call or argument

Question

I am importing a .txt file and reformatted the data to remove commas. When I try to save it, it give me the run time error. How can I fix this? Any help would greatly be appreciated!

Sub SaveNewFile()

ActiveCell.SpecialCells(xlLastCell).Select
endrow = Selection.Row
Cells(1, 1).Select

If TopSide = 1 Then
    BoardName = BoardName & "_TOP"
Else
    BoardName = BoardName & "_BOT"
End If
filesavename = Application.GetSaveAsFilename( _
    InitialFilename:=BoardName, FileFilter:="YTV CAD Files (*.ycd), *.ycd")
    If filesavename = "False" Then End

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile(filesavename, True)
    For i = 1 To 17
        For j = 1 To 2
            a.write (Cells(i, j) & "     ")
        Next j
        a.writeline
    Next i
    For i = 18 To endrow
        For j = 1 To 7
            a.write (Cells(i, j) & "     ")
        Next j
        a.writeline
    Next i
    a.Close

    Application.DisplayAlerts = False
    'ActiveWorkbook.SaveAs Filename:= _
    '    filesavename, _
    '    FileFormat:=xlTextPrinter, CreateBackup:=False
    jmessage = MsgBox(filesavename & " has been generated successfully!", vbOKOnly, "YCD Export")
    Call YCDFileLocationIni
    'Application.DisplayAlerts = True
    Workbooks.Open (CADFileName)
    Workbooks(ShortCADFileName).Activate
    ActiveWorkbook.Close
End Sub

score 1 · Answer 1 · edited May 23 '17 at 10:32

First things first

Change

filesavename = Application.GetSaveAsFilename( _
                                            InitialFileName:=BoardName, _
                                            FileFilter:="YTV CAD Files (*.ycd), *.ycd")
If filesavename = "False" Then End

to

Dim filesavename As Variant

filesavename = Application.GetSaveAsFilename( _
                                            InitialFileName:=BoardName, _
                                            FileFilter:="YTV CAD Files (*.ycd), *.ycd")
If filesavename = False Then Exit Sub

You should never use End. Reason is quite simple. It's like Switching Off your Computer using the POWER OFF button. The End statement stops code execution abruptly, without invoking the Unload, QueryUnload, or Terminate event, or any other Visual Basic code. Also the Object references held (if any) by other programs are invalidated.

Next, please avoid the use of .Select/.Activate INTERESTING READ

The other things that don't make sense (but will not generate an error unless you have Option Explicit On) is BoardName. Where are you setting it's previous value? Same goes with TopSide. Use Option Explicit and major part of your problem will go away.

This definitely doesn't make any sense to me

Workbooks.Open (CADFileName)
Workbooks(ShortCADFileName).Activate

What is the value of CADFileName or ShortCADFileName

Also did you step through the code of YCDFileLocationIni?

All in all, Once I made the above changes to your code plus few small changes like declaring CADFileName, ShortCADFileName, your code ran without any errors.

Run-time error = '5' : invalid procedure call or argument

1 Answers1