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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

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,260
Messages
5,485,719
Members
407,511
Latest member
Tryintouseexcel

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top