none
DoCmd.SetWarnings False is ignored RRS feed

  • Question

  • I am writing a code in MS Access 2013 with VBA to automate the creation and filling of an EXcel workbook. In my code I have 2 instructions, one to save the new created workbook and the second to delete the unused worksheets. In order to avoid system messages I write at the top of the code the instruction DoCmd.SetWarnings False. THere is the piece of code:

    DoCmd.SetWarnings False

    Dim wbName as string, WB As Workbook,WS As Worksheet, mSHT As Worksheet

    Dim xApp As Excel.Application

    Set xApp = CreateObject("excel.Application")

     Set WB = xApp.Workbooks.add

    wbName = "D:\Projects\Gestion Scolaire\NotaBene\Etats\Bulletins_3.xlsx"

    WB.SaveAs wbName

    WB.Worksheets("Sheet1").Delete

    DoCmd.SetWarnings true

    But this code above still ask to confirm to overwrite existing file if any for the instruction WB.SaveAs wbName

    and also to confirm the deleting of the unused sheets for the instruction WB.Worksheets("Sheet1").Delete

    Please can anyone help to solve this.


    • Edited by Ekoue Wednesday, January 10, 2018 8:37 AM
    Wednesday, January 10, 2018 8:23 AM

Answers

  • DoCmd.SetWarnings False applies to Access, but not to Excel. You have to add the line

    xApp.DisplayAlerts = False

    above the WB.SaveAs line, and

    xApp.DisplayAlerts = True

    below it.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Ekoue Wednesday, January 10, 2018 12:56 PM
    Wednesday, January 10, 2018 11:33 AM

All replies

  • DoCmd.SetWarnings False applies to Access, but not to Excel. You have to add the line

    xApp.DisplayAlerts = False

    above the WB.SaveAs line, and

    xApp.DisplayAlerts = True

    below it.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Ekoue Wednesday, January 10, 2018 12:56 PM
    Wednesday, January 10, 2018 11:33 AM
  • DoCmd.SetWarnings False applies to Access, but not to Excel. You have to add the line

    xApp.DisplayAlerts = False

    above the WB.SaveAs line, and

    xApp.DisplayAlerts = True

    below it.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Many thanks. It works perfectly.

    Wednesday, January 10, 2018 12:57 PM