• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 399
  • Last Modified:

Saving a File Under a New Name

Hi, I'm writing a macro that will save a file under a new name. I will need it to give the user an option to choose the name and the location to save the file.

I have the following code: A Syntax Error message appears and I'm not sure how to fix this.


Sub Save_File()
'
' Save_File Macro
 
 
    Dim ActSheet As Worksheet
 
    Dim ActBook As Workbook
 
    Dim CurrentFile As String
 
    Dim NewFileType As String
 
    Dim NewFile As String
 
 
    Application.ScreenUpdating = False    ' Prevents screen refreshing.
 
 
    CurrentFile = ThisWorkbook.FullName
 
[b] 
    NewFileType = "Excel Files 1997-2003 (*.xls), *.xls," & _

               "Excel Files 2007 (*.xlsx), *.xlsx," & _

               "All files (*.*), *.*"[/b]
 
 
 
    [b]NewFile = Application.GetSaveAsFilename( _

        InitialFileName:=NewFileName, _

        fileFilter:=NewFileType)[/b]
 
 
 
    If NewFile <> "" And NewFile <> "False" Then
 
       [b] ActiveWorkbook.SaveAs Filename:= NewFile, _

            FileFormat:=xlOpenXMLWorkbookMacroEnabled, _

            ReadOnlyRecommended:=False, _

            CreateBackup:=False
     
[/b]
 
        Set ActBook = ActiveWorkbook
 
        Workbooks.Open CurrentFile
 
        ActBook.Close
 
    End If
 
 
 
    Application.ScreenUpdating = True
 
End Sub

Open in new window

0
jmac001
Asked:
jmac001
1 Solution
 
Steven HarrisPresidentCommented:
I would suggest using something a bit less complicated to start with:

Application.GetSaveAsFileName

Open in new window


You can save this to a variable:

Dim NewName As String
NewName = Application.GetSaveAsFilename

Open in new window


And then run whatever validation you want against "NewName"

Sub Save_File()
Dim NewFile As String
Dim CurrentFile As String
NewFile = Application.GetSaveAsFilename
CurrentFile = ThisWorkbook.FullName
    Application.ScreenUpdating = False

    If NewFile <> "" Then
        ActiveWorkbook.SaveAs NewFile & "xlsm"
        Set ActBook = ActiveWorkbook
        CurrentFile.Open
        ActBook.Close
    End If
    
    Application.ScreenUpdating = True

End Sub

Open in new window


Edited:  Fixed my script error
0
 
byundtCommented:
With Excel 2003 and earlier, you could save a file without specifying the file format parameter in VBA SaveAs. In Excel 2007 and later, you will get an error (either at runtime or when you open the file) if you save the file and rely on a changed file extension to determine the file format.

For this reason, I rewrote ThinkSpaceSolutions' macro to:
1.  Specify a file format when saving the file
2.  Avoid a runtime error on statement 11
3.  Offer the option of saving the file with the original filename

Sub Save_File()
Dim CurrentName As String, NewFile As String
Dim ActBook As Workbook
Dim iFormat As Integer
    NewFile = Application.GetSaveAsFilename(FileFilter:="Excel files (*.xlsm), *.xlsm")

    If NewFile <> "" Then
        NewFile = Left(NewFile, InStrRev(NewFile, ".")) & "xlsm"
        CurrentName = ActiveWorkbook.FullName
        iFormat = ActiveWorkbook.FileFormat
        Application.ScreenUpdating = False
        ActiveWorkbook.SaveAs NewFile, FileFormat:=xlOpenXMLWorkbookMacroEnabled
        ActiveWorkbook.SaveAs CurrentName, iFormat      'Restore the original filename & extension
    End If

End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now