Announcement

Collapse
No announcement yet.

Application.GetSaveAsFilename Help

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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")

  • #2
    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
    Dave
    XLorate

    Comment


    • #3
      Ok I've kind of got somewhere with this. I get a debug error on the last line of this code:

      Code:
      Dim Relocate As Variant
      
      Relocate = Application.GetSaveAsFilename(InitialFileName:="H:\My Folder\My Subfolder\My New Project.xlsx", _
          FileFilter:="Excel Files (*.xlsx)," & "*.xlsx", _
          Title:="Please save request template")
          
          If Relocate = False Then
          
          Exit Sub
          
          End If
      
      ActiveWorkbook.SaveAs filename:=Relocate
      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.

      Comment


      • #4
        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?

        Code:
        Dim Relocate as Variant    
        
        Relocate = Application.GetSaveAsFilename(InitialFileName:="H:\My Folder\My Subfolder\My New Project.xlsx", _
            FileFilter:="Excel Files (*.xlsx)," & "*.xlsx", _
            Title:="Please save request template")
            
            If Relocate = False Then
            
            Exit Sub
            
            End If
        
        ActiveWorkbook.SaveAs filename:=Relocate, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

        Comment


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

          Code:
              Dim Relocate As Variant
          
              Relocate = Application.GetSaveAsFilename(InitialFileName:="H:\My Folder\My Subfolder\My New Project.xlsx", _
                                                       FileFilter:="Excel Files (*.xlsx)," & "*.xlsx", _
                                                       Title:="Please save request template")
          
              If Relocate = False Then
          
                  Exit Sub
          
              End If
              Application.DisplayAlerts = False    '<=================
              ActiveWorkbook.SaveAs Filename:=Relocate, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
          Dave
          XLorate

          Comment


          • #6


            Thanks again Dave. Much appreciated.

            Comment

            Working...
            X