VBA code to save workbook with no prompt

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: VBA code to save workbook with no prompt

  1. #1
    Board Regular
    Join Date
    Sep 2002
    Location
    Eugene, Oregon
    Posts
    94
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

  3. #3
    Board Regular
    Join Date
    Sep 2002
    Location
    Eugene, Oregon
    Posts
    94
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

    Tracy's new best bud
    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    Blackboys, East Sussex, UK
    Posts
    10,597
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    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 | FAQs
    English is a weird language. It can be understood through tough thorough thought, though!

  7. #7
    New Member
    Join Date
    Oct 2011
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    New Member
    Join Date
    May 2012
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to save workbook with no prompt

    Try this...

    Sub SaveWbWithoutPrompt()

    Activeworkbook.Saved=True

    End Sub

  9. #9
    Board Regular
    Join Date
    Mar 2011
    Posts
    69
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

User Tag List

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