Solved

Saving a File Under a New Name

Posted on 2013-12-04
2
386 Views
Last Modified: 2013-12-11
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
Comment
Question by:jmac001
2 Comments
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39697000
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
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39697387
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question