[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Saving a File Under a New Name

Posted on 2013-12-04
2
Medium Priority
?
393 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 2000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

649 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