How to code in VBA to suppress requests "do I want to accept or not"

I need to know how to set command states within VBA to force the code to execute such as in this example:
I want the program to create a spreadsheet for me without my intervention by answering the yes or no block.

I also need to know how to turn off the Compatibility checker in routine when I am saving a file to an excel 97 - 2003 format.  

Somehow the compatibility mode got turned on and I cannot turn it off.
frank_guessAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

iandianCommented:
You can try to command "DoCmd.SetWarnings False"
Use "DoCmd.SetWarnings True" after the code that gives you the warning to make sure you don't miss any errors/wanings you do want to get.
0
Chris BottomleySoftware Quality Lead EngineerCommented:
Compatability:

Orange office button in the left top:

access options | Popular | Default Format | Select as appraopriate

Chris
0
frank_guessAuthor Commented:
I will give both a try.  Thanks
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

frank_guessAuthor Commented:
Chris, not the same for using excel tables (when I write to a table and do a save).
0
Chris BottomleySoftware Quality Lead EngineerCommented:
Ah, I did start to answer for excel (application) when I thought I was confused and reworded it for Access - I missed the point in the process so sorry.  I'll think a whiles pending any other progress.

Chris
0
frank_guessAuthor Commented:
Chris, let me explain my problem, below is my code.  I am creating multiple sets of excel files matching against a table with a list of bank numbers (number sites) and writing reports utilizing header.xlt files and stuffing the Access queried information into them.  When I hit the save step instead of saving it in the new worksheet or workbook and naming the document It hits the compatibility problem and pops up a screen that you have to click on for each report created.  I create 40 reports up to 193 reports using the small program I run behind a button.

 Dim strQueryName As String
    Dim strCriteria As String
    Dim stReportName As String
    Dim cblist As String
    ActivityDate = Now()
    Dim xlTmp As String
    Dim rs As DAO.Recordset
    Dim xlObj As Object
    Dim qd As DAO.QueryDef, sSql As String, sWhere As String
    Dim rs2 As DAO.Recordset
    DoCmd.SetWarnings False

    Set rs2 = currentDB.OpenRecordset("qryBanksNos")
    Do While Not rs2.EOF
        xlTmp = "c:\Databases\Report_Masters\MasterWiresInitiatorReport.xlt"
        strQueryName = "QryWiresInitiatorLists"
        stReportName = "Bank_No_" & rs2!COMPANY & "_Q1_" & Format(ActivityDate, "MMDDYYYY") & "_WiresInitiator_Sec_Rpt"
           
        Set qd = currentDB.QueryDefs(strQueryName)
        sSql = qd.sql
        sSql = Trim(Left(sSql, InStr(sSql, "Where") - 1))
        sWhere = " Where Tbl_WiresInitiatorListsBase.Company_No ='" & rs2!COMPANY & "'"
        qd.sql = sSql & sWhere
        Set rs = currentDB.OpenRecordset(strQueryName)
        Set xlObj = CreateObject("excel.application")
              xlObj.workbooks.Add (xlTmp)
        With xlObj
             .worksheets("WiresInitiatorLists").Select
             .Range("a2").copyfromrecordset rs
             .activeworkbook.SaveAs "C:\DB2_Reports\WiresInitiatorFiles\" & stReportName & ".xls", FileFormat:=56
       
        End With
 
        xlObj.Quit
       
        rs2.MoveNext
    Loop

So in the log run I first had Capricorn1 assist me by ", FileFormat:=56
and it worked great until I had to make some changes to my header files and then the program breaks.
So either I disable the compatibility mode or I have to rebuild my worksheets another way.  Got any ideals.
0
Rey Obrero (Capricorn1)Commented:
< it worked great until I had to make some changes to my header files and then the program breaks.>

if you have done this in A2007, this is probably the one causing the compatibility checker be turned on.

try

        With xlObj

             .worksheets("WiresInitiatorLists").Select
             .Range("a2").copyfromrecordset rs

              .application.displayalerts=false

             .activeworkbook.SaveAs "C:\DB2_Reports\WiresInitiatorFiles\" & stReportName & ".xls", FileFormat:=56

              .application.displayalerts=true        
        End With
 
        xlObj.Quit
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
or perhaps this one

xlApp.activeworkbook.checkcompatibility = false

 .activeworkbook.SaveAs "C:\DB2_Reports\WiresInitiatorFiles\" & stReportName & ".xls", FileFormat:=56

0
frank_guessAuthor Commented:
Capricorn1 - I do not know where you found the answer but you hit it dead on the nail head.  Thank you, thank you, thank you.  This works perfect now.
0
Rey Obrero (Capricorn1)Commented:
frank_guess,
just curious, did you try the second option i posted?
0
frank_guessAuthor Commented:
No But I will.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.