Page 1 of 2 12 LastLast
Results 1 to 10 of 11
Like Tree1Likes

VBA code to save workbook with no prompt

This is a discussion on VBA code to save workbook with no prompt within the Excel Questions forums, part of the Question Forums category; What's the syntax for saving an Excel workbook file so it saves over the existing same filename without prompting you? ...

  1. #1
    Board Regular
    Join Date
    Sep 2002
    Location
    Eugene, Oregon
    Posts
    94

    Default VBA code to save workbook with no prompt

    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

  2. #2
    MrExcel MVP parry's Avatar
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    3,355

    Default Re: VBA code to save workbook with no prompt

    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
    scolty1985 likes this.

  3. #3
    Board Regular
    Join Date
    Sep 2002
    Location
    Eugene, Oregon
    Posts
    94

    Default Re: VBA code to save workbook with no prompt

    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

  4. #4
    MrExcel MVP parry's Avatar
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    3,355

    Default Re: VBA code to save workbook with no prompt

    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

  5. #5
    New Member
    Join Date
    Jun 2011
    Posts
    1

    Unhappy Re: VBA code to save workbook with no prompt

    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

  6. #6
    MrExcel MVP
    Moderator

    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    Blackboys, East Sussex, UK
    Posts
    9,859

    Default Re: VBA code to save workbook with no prompt

    Hi swathis

    Can you please start your own new thread? This one relates to a different discussion altogether.
    Regards,
    Jon von der Heyden

    - Posting guidelines, forum rules and terms of use

    - Post your sheet using HTML tables - why?

    - Read the FAQs

    - Always use CODE tags when posting code, e.g. [CODE]insert code here[/CODE]

    - My Excel Blog

  7. #7
    New Member
    Join Date
    Oct 2011
    Posts
    1

    Default Re: VBA code to save workbook with no prompt

    Quote Originally Posted by sagain2k View Post
    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.

  8. #8
    svb
    svb is offline
    New Member
    Join Date
    May 2012
    Posts
    1

    Default Re: VBA code to save workbook with no prompt

    Try this...

    Sub SaveWbWithoutPrompt()

    Activeworkbook.Saved=True

    End Sub

  9. #9
    New Member
    Join Date
    Mar 2011
    Posts
    37

    Default Re: VBA code to save workbook with no prompt

    Thank You for this too, it saved me some annoying work regularly~~~~

  10. #10
    New Member
    Join Date
    Nov 2013
    Posts
    5

    Default Re: VBA code to save workbook with no prompt

    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

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com