Solved

Saving a File Under a New Name

Posted on 2013-12-04
2
388 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

My experience with Windows 10 over a one year period and suggestions for smooth operation
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…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

740 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