Solved

Saving a File Under a New Name

Posted on 2013-12-04
2
384 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
populate Excel dropdown via ADO and VBA 6 17
Excel VBA 4 26
formatting - number format 2 12
How best to represent data in Excel? 13 24
This article will show you how to use shortcut menus in the Access run-time environment.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

776 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