• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2745
  • Last Modified:

Excel 2010 VBA SaveAs No dialog/Prompt

I'm trying to my workbook using a macro, I don't want any prompts to show up.

I'm using the command...        
ActiveWorkbook.SaveAs Filename:=<FILEPATH>

Open in new window

However the save as dialog box is shown each time.

I've read about adding DisplayAlerts command before and after the saveas command
Application.DisplayAlerts = False
Application.DisplayAlerts = True

Open in new window

But the save as dialogue still keeps popping up!
1 Solution
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.EnableEvents = False

  Dim vFile
  Dim strDirectoryPath As String
      If SaveAsUI Then
vFile = Application.GetSaveAsFilename("C:\temp\Sample.xls", "Excel files (*.xls),*.xls")
   If TypeName(vFile) = "Boolean" Then
    Cancel = True
    Application.EnableEvents = True

       Exit Sub  ' user cancelled
End If
   strDirectoryPath = "C:\temp\"
   If Dir(strDirectoryPath, vbDirectory) = "" Then MkDir strDirectoryPath

   ActiveWorkbook.SaveAs vFile, FileFormat:=56
   Application.EnableEvents = True
   saveProcessStarted = False
Cancel = True
     Exit Sub
   End If
Application.EnableEvents = True
 End Sub
Rory ArchibaldCommented:
There is nothing in your code that would trigger a dialog unless your filename is missing or you are using a function to provide the file name.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now