Application.GetSaveAsFilename Help

  • Morning All,


    I have a macro that modifies a standard template.


    At the end of this macro I want a save as dialogue that opens up in a default folder with a default filename.


    The user can then navigate to the appropriate subfolder and change the name accordingly before saving.


    The code below runs fine, BUT, it isn't actually saving the file.


    I've tried this on a local and a network drive.


    Any ideas?


    Code
    Dim Relocate As Variant
    
    
    Relocate = Application.GetSaveAsFilename(FileFilter:= _
    "Excel Files (*.xlsx)," & "*.xlsx, Macro Enabled" & _
    "Workbook (*.xlsm), *xlsm", InitialFileName:="H:\My Folder\My Subfolder\My New Project.xlsx")
  • Using the GetSaveAsFilename dialog box, is not a save function, it is used to get the string you want to save as, you will still have to use a saveas line to complete the action.


    Use the macro recorder to see a saveas code, this example should save as and .xlsm wb


    Code
    Dim Relocate As Variant
    
    
        Relocate = Application.GetSaveAsFilename(FileFilter:= _
                                                 "Excel Files (*.xlsx)," & "*.xlsx, Macro Enabled" & _
                                                 "Workbook (*.xlsm), *xlsm", InitialFileName:="H:\My Folder\My Subfolder\My New Project.xlsx")
    
    
        ActiveWorkbook.SaveAs Filename:=Relocate, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
  • Ok I've kind of got somewhere with this. I get a debug error on the last line of this code:



    If I change the file name and file filter to xlsm it works. But once this macro has formatted the template I want to save it as a macro free file for each project once the initial formatting is done.

  • Hi Davesexcel, think I was typing as you posted. thank you for the help, much appreciated.


    I've got this to work now, but I get the dialogue box asking if I'm sure I want to save as a macro free workbook. Is there a way to disable this?


  • Application.displayalerts=false will get rid of those msges.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!