VBA code to save workbook with no prompt

sagain2k

Board Regular
Joined
Sep 8, 2002
Messages
94
What's the syntax for saving an Excel workbook file so it saves over the existing same filename without prompting you? Can't seem to find it...thanks!

The code I'm using to save the workbook is:

ActiveWorkbook.SaveAs Filename:="C:\Data\testfile.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
Hi, save this macro in the ThisWorkbook object rather than in a module.

Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
End Sub
 

sagain2k

Board Regular
Joined
Sep 8, 2002
Messages
94
Thanks for the suggestion; I couldn't get that to work, but I did find a solution that did work for me to disable the prompt; here's an example:

Application.DisplayAlerts = False 'IT WORKS TO DISABLE ALERT PROMPT

'SAVES FILE USING THE VARIABLE BOOKNAME AS FILENAME
ActiveWorkbook.SaveAs Filename:="C:\Data\" & BookName, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

Application.DisplayAlerts = True 'RESETS DISPLAY ALERTS
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
Theres always more than one way. :)

The code I posted should be saved in ThisWorkbook rather than Module1 (or whatever you have called your module). I was presuming the file your saving is the same one as where the code is run from. Sorry if I misinterpreted you.

The before_close event runs automatically as opposed to a normal macro where you have to do an action.

You can also have done this which saves and closes the workbook...

Code:
Sub CloseandSave()
ActiveWorkbook.Close SaveChanges:=True
End Sub

hth
 

swathis.swathis

New Member
Joined
Jun 2, 2011
Messages
1
Hi ppl ,
i need help in macros of excel
The requirement is:
Coulmn1 Column2
ASD-op null
wer-it null
wert-yu null
dft-op null


When a user is trying to save, i want an macro to run were it ill have a search functionality in column 1 wic has -op suffixed and if corresponding column2 is null user shld get an alert asking to fill the column two
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,798
Office Version
365
Platform
Windows
Hi swathis

Can you please start your own new thread? This one relates to a different discussion altogether.
 

rocketman002

New Member
Joined
Oct 6, 2011
Messages
1
Thanks for the suggestion; I couldn't get that to work, but I did find a solution that did work for me to disable the prompt; here's an example:

Application.DisplayAlerts = False 'IT WORKS TO DISABLE ALERT PROMPT

'SAVES FILE USING THE VARIABLE BOOKNAME AS FILENAME
ActiveWorkbook.SaveAs Filename:="C:\Data\" & BookName, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

Application.DisplayAlerts = True 'RESETS DISPLAY ALERTS

Your solution used to work for me, but now I get another alert:
"Privacy warning: This document contains macros, ActiveX controls, XML expansion pack information, or Web components. These may include personal information that cannot be removed by the Document Inspector."
Any ideas?

PS. I use Windows XP, Excel 2007.
 

svb

New Member
Joined
May 19, 2012
Messages
1
Try this...

Sub SaveWbWithoutPrompt()

Activeworkbook.Saved=True

End Sub
 

keerthna

New Member
Joined
Nov 22, 2013
Messages
5
I have written the macro for the save as functionality but once the routine iis over the normal save as box comes up. can someone help.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Val(Application.Version) < 12 Then
restart1:
vFile = Application.GetSaveAsFilename("", "Excel files (*.xls),*.xls")

If TypeName(vFile) = "Boolean" Then

iRet = MsgBox("Quit without saving file?", vbYesNo)
If iRet = vbNo Then
GoTo restart1
Else
Exit Sub
End If
'Exit Sub ' user cancelled
Else
ActiveWorkbook.SaveAs Filename:=vFile, FileFormat:= _
-4143
iRet = MsgBox("Your file has been saved in " & FPath, vbOK)
End If

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,102,623
Messages
5,487,923
Members
407,614
Latest member
fuslela

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top