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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Hi swathis

Can you please start your own new thread? This one relates to a different discussion altogether.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top