none
Argument not optional RRS feed

  • Question

  • this is my code for file save as,  when I press the "save" button I get an error message "Argument Not Optional"

    Sub FileSaveAs(NewFileName As String)
        Dim ActSheet As Worksheet
        Dim ActBook As Workbook
        Dim CurrentFile As String
        Dim NewFileType As String
        Dim NewFile As String

        Application.ScreenUpdating = False

        CurrentFile = ThisWorkbook.FullName

        NewFileType = "Excel Files 1997-2003 (*.xls), *.xls," & _
        "Excel Files 2007 (*.xlsx), *.xlsx," & _
        "All files (*.*), *.*"

        NewFileName = Application.GetSaveAsFilename(InitialFileName:=NewFileName, fileFilter:=NewFileType)

        If NewFile <> "" And NewFile <> "False" Then
        ActiveWorkbook.SaveAs Filename:=NewFile, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadyOnlyRecommended:=False, CreateBackup:=False

        Set ActBook = ActiveWorkbook
        Workbooks.Open CurrentFile
        ActBook.Close
        End If

        Application.ScreenUpdating = True
        End Sub

                               
    Monday, July 28, 2014 11:00 PM

Answers

  • You are only saving the file with default values for the optional parameters so you can eliminate most of the optional parameters and allow Excel to allocate the default save options. The parameter that is required is the file type.

    The code below is a simple test to call the save routine and pass a file name that has been extracted from a worksheet. The file name in the example is created by concatenating 2 cells and adding a space between them.

    Don't use a File Extension in the initial file name. Excel will add a default extension as the first of the available FileType in the FileFilter. The user can change this by selecting the type of file (File Extension) from the Save As dialog box. Note my comment in the code re the FileFilter; you should only include the file types that you want to allow the user to select. The FileFilter only tells Excel what type of files to make available in the "Save as type" dropdown field of the Save as dialog box. The user can select any on of the available options. There is no point including *.* when you are restricting to *.xlsx and *.xls

    Feel free to get back to me again if still having problems.

    Picture of Worksheet with the File Name values. (Note: No File extension.)

    Example Code:

    Sub TestCallFileSaveAs()
        'Using Variant for NewFileName permits saving the boolean False if user cancels
        Dim FileName As Variant
       
        With ActiveWorkbook.Worksheets("Sheet1")    'ActiveWorkbook is the workbook being saved.
            'Note leading dots in front of Range which ties the _
             ranges to the Workbook and worksheet in the With Statement
            FileName = .Range("A2").Value & " " & .Range("B2")   'Don't use a file extension
        End With
       
        Call FileSaveAs(FileName)    'This file is open and is Active Workbook
    End Sub


    'Using Variant for NewFileName permits saving the boolean False if user cancels
    Sub FileSaveAs(NewFileName As Variant)
            Dim ActSheet As Worksheet
            Dim ActBook As Workbook
            Dim CurrentFile As String
            Dim NewFileType As String
            Dim lngFileFormat As Long
           
            Application.ScreenUpdating = False
           
            'Only include the file format types that the user is permitted to choose.
            NewFileType = "Excel 97-2003 Workbook (*.xls), *.xls," & _
                        "Excel Workbook (*.xlsx), *.xlsx"
           
            NewFileName = Application.GetSaveAsFilename(InitialFileName:=NewFileName, FileFilter:=NewFileType)
           
            If NewFileName = False Then
                MsgBox "User cancelled without selecting file." & vbCrLf & _
                        "File not saved. Processing terminated."
                Exit Sub
          
            Else
                'Test the file format that the user selected.
                If Mid(NewFileName, InStr(1, NewFileName, ".")) = ".xlsx" Then
                    lngFileFormat = 51      'xlsx file type (Excel 2007 or later)
                Else
                    lngFileFormat = 56      '.xls file type (Excel 97-2003)
                End If
               
                ActiveWorkbook.SaveAs FileName:=NewFileName, FileFormat:=lngFileFormat
                MsgBox "File saved as " & NewFileName
             End If
     End Sub


    Regards, OssieMac

    Saturday, August 2, 2014 12:16 AM

All replies

  • I see 2 problems.

    The one returning the error is you have misspelt "ReadOnlyRecommended" you have a "y" inserted between "Read" and "Only" to produce "ReadyOnlyRecommended"

    The other problem is you are using NewFile as the filename in both the If - Then test line and in the SaveAs line of code but I cannot see where you have assigned a name to that variable. NewFileName appears to be the variable for the file name.


    Regards, OssieMac

    Tuesday, July 29, 2014 4:41 AM
  • Still receiving the "Argument not Optional"

    updated code:

    Sub FileSaveAs(NewFileName As String)
        Dim ActSheet As Worksheet
        Dim ActBook As Workbook
        Dim CurrentFile As String
        Dim NewFileType As String
        Dim NewFile As String
       
        Application.ScreenUpdating = False
       
        CurrentFile = ThisWorkbook.FullName
       
        NewFileType = "Excel Files 1997-2003 (*.xls), *.xls," & "Excel Files 2007 (*.xlsx), *.xlsx," & "All files (*.*), *.*"
       
        NewFileName = Application.GetSaveAsFilename(InitialFileName:=NewFileName, fileFilter:=NewFileType)
       
        If NewFile < CustomerLocationDate > "" And NewFile < CustomerLocationDate > "False" Then
        ActiveWorkbook.SaveAs Filename:=CustomerLocationDate, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
       
        Set ActBook = ActiveWorkbook
        Workbooks.Open CurrentFile
        ActBook.Close
        End If
       
        Application.ScreenUpdating = True
        End Sub


       Application.Goto Reference:="FileSaveAs"
        ActiveWorkbook.save
        Selection.Characters.Text = "Save"
        With Selection.Characters(Start:=1, Length:=11).Font
            .Name = "Arial Black"
            .FontStyle = "Regular"
            .Size = 16
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = 3
        End With
        Range("R12").Select
        ActiveSheet.Shapes.Range(Array("Button 2")).Select
        Selection.Characters.Text = "Save"
        With Selection.Characters(Start:=1, Length:=11).Font
            .Name = "Calibri"
            .FontStyle = "Bold"
            .Size = 14
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = 3
        End With
        Range("R14").Select
        ActiveWorkbook.save
        Application.WindowState = xlNormal
        ActiveWorkbook.save
        ActiveWindow.Zoom = 90
        ActiveWindow.Zoom = 100
        ActiveWindow.SmallScroll Down:=-12
        Application.WindowState = xlNormal
        Workbooks.Open Filename:="Electronic Work Ticket.xlsm"
        ActiveWindow.SmallScroll Down:=9
        Range("C23:C25").Select
        Selection.Copy
        Application.CutCopyMode = False
        ActiveWorkbook.save
        Range("C29").Select
        Windows("Electronic Work Ticket.xlsm").Activate
        ActiveWorkbook.save
    End Sub

    Thursday, July 31, 2014 7:40 PM
  • I need a bit more information so I can test the code. I don't understand what the code is supposed to do. You get NewFileName and then test NewFile against CustomerLocationDate and NewFile value has not been set anywhere and then save as CustomerLocationDate

    To get the filename you are specifying the filter as *.xls or *.xlsx and *.* file type. Are you saving the workbook containing the code or saving another workbook and what type of file do you want to save as? You cannot save the workbook with the code as xlsx; it must be xlsm (Macro enabled).

    Also can you give me sample values for NewFile and CustomerLocationDate to use in the following line that will evaluate to True.

    If NewFile < CustomerLocationDate > "" And NewFile < CustomerLocationDate > "False" Then

     


    Regards, OssieMac

    Thursday, July 31, 2014 10:10 PM
  • I would like to 'file save as' regular xls format not xlsm, the file customer location date is a reminder to my employees to name the file with that info or we may change that to be just a ticket #. Also I am using office 365 excel. Or can I have the file named after a specific cell ( want employees to give each ticket a ticket number, can I have it save as that ticket number ? Which would be a specific 2 cells on the form! I am a newbie to Visual Basic code. Sorry :(
    Friday, August 1, 2014 5:43 AM
  • I have deleted my reply here so please discard the email info you would have received. I realized that if the file is currently saved as xls in compatibility mode then saving as xlsx will make the file unusable.

    I will re-write the code to handle this problem.


    Regards, OssieMac

    Friday, August 1, 2014 7:41 AM
  • You are only saving the file with default values for the optional parameters so you can eliminate most of the optional parameters and allow Excel to allocate the default save options. The parameter that is required is the file type.

    The code below is a simple test to call the save routine and pass a file name that has been extracted from a worksheet. The file name in the example is created by concatenating 2 cells and adding a space between them.

    Don't use a File Extension in the initial file name. Excel will add a default extension as the first of the available FileType in the FileFilter. The user can change this by selecting the type of file (File Extension) from the Save As dialog box. Note my comment in the code re the FileFilter; you should only include the file types that you want to allow the user to select. The FileFilter only tells Excel what type of files to make available in the "Save as type" dropdown field of the Save as dialog box. The user can select any on of the available options. There is no point including *.* when you are restricting to *.xlsx and *.xls

    Feel free to get back to me again if still having problems.

    Picture of Worksheet with the File Name values. (Note: No File extension.)

    Example Code:

    Sub TestCallFileSaveAs()
        'Using Variant for NewFileName permits saving the boolean False if user cancels
        Dim FileName As Variant
       
        With ActiveWorkbook.Worksheets("Sheet1")    'ActiveWorkbook is the workbook being saved.
            'Note leading dots in front of Range which ties the _
             ranges to the Workbook and worksheet in the With Statement
            FileName = .Range("A2").Value & " " & .Range("B2")   'Don't use a file extension
        End With
       
        Call FileSaveAs(FileName)    'This file is open and is Active Workbook
    End Sub


    'Using Variant for NewFileName permits saving the boolean False if user cancels
    Sub FileSaveAs(NewFileName As Variant)
            Dim ActSheet As Worksheet
            Dim ActBook As Workbook
            Dim CurrentFile As String
            Dim NewFileType As String
            Dim lngFileFormat As Long
           
            Application.ScreenUpdating = False
           
            'Only include the file format types that the user is permitted to choose.
            NewFileType = "Excel 97-2003 Workbook (*.xls), *.xls," & _
                        "Excel Workbook (*.xlsx), *.xlsx"
           
            NewFileName = Application.GetSaveAsFilename(InitialFileName:=NewFileName, FileFilter:=NewFileType)
           
            If NewFileName = False Then
                MsgBox "User cancelled without selecting file." & vbCrLf & _
                        "File not saved. Processing terminated."
                Exit Sub
          
            Else
                'Test the file format that the user selected.
                If Mid(NewFileName, InStr(1, NewFileName, ".")) = ".xlsx" Then
                    lngFileFormat = 51      'xlsx file type (Excel 2007 or later)
                Else
                    lngFileFormat = 56      '.xls file type (Excel 97-2003)
                End If
               
                ActiveWorkbook.SaveAs FileName:=NewFileName, FileFormat:=lngFileFormat
                MsgBox "File saved as " & NewFileName
             End If
     End Sub


    Regards, OssieMac

    Saturday, August 2, 2014 12:16 AM