VB macro works in Excel 2010 but not in Excel 2016

Excel / Macro VB

Have this code it works fine on Excel 2010    (opens runs saves and closes)   upgraded to Excel 2016 and it opens runs and closes but leaves a save dialogue behind which is annoying as it's a scheduled job.  Not sure what I need to add on the newer version of Excel.

Sub Macro1()
'

' Macro1 Macro
'
'Refresh Jet
    Application.Run "JetReports.xlam!JetMenu", "Refresh"
    Application.DisplayAlerts = False
    Columns("C:F").Select
    Range("C3").Activate
    Sheets("Sheet3").Select
    Columns("A:A").ColumnWidth = 4.14
    Rows("1:1").RowHeight = 25.5
    Range("A1").Select
    ActiveCell.FormulaR1C1 = " "
    Range("A1").Select
    Sheets("Report").Select
    Columns("C:F").Select
    Range("C3").Activate
    Selection.Copy
    Sheets("Sheet3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Rows("1:2").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Sheets("Report").Select
    ActiveWindow.SelectedSheets.Delete
        ActiveWorkbook.SaveAs Filename:="G:\IT\Trimit\Elastic\Feeds\Rab\inventory.csv", _
        FileFormat:=xlCSVMSDOS, CreateBackup:=False
    ActiveWindow.WindowState = xlMinimized
    Application.Quit
End Sub
RichardAtkAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

NorieAnalyst Assistant Commented:
Does it help if you close the workbook, with alerts disabled, after you've saved it?
Application.DisplayAlerts = False
ActiveWorkbook.Close SaveChanges:=False  ' the workbook has already been saved
Application.DispalyAlerts = True

Application.Quit

Open in new window

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
Roy CoxGroup Finance ManagerCommented:
I can't see why the code is not working in 2016 but the code can be  improved. Also, I've added code to check if the workbook has been saved at the end.

Option Explicit

Sub Macro1()
'

' Macro1 Macro
'
'Refresh Jet
    With Application
        .Run "JetReports.xlam!JetMenu", "Refresh"
        .DisplayAlerts = False

        With Sheets("Sheet3")
            .Columns("A:A").ColumnWidth = 4.14
            .Rows("1:1").RowHeight = 25.5
            .Range("A1").FormulaR1C1 = " "
            Sheets("Report").Range("C3").Copy

            .Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                                             :=False, Transpose:=False
            .Rows("1:2").Delete Shift:=xlUp
            .CutCopyMode = False
        End With

        .DisplayAlerts = False
        Sheets("Report").Delete
        .DisplayAlerts = True

        ThisWorkbook.SaveAs Filename:="G:\IT\Trimit\Elastic\Feeds\Rab\inventory.csv", _
                            FileFormat:=xlCSVMSDOS, CreateBackup:=False
        ActiveWindow.WindowState = xlMinimized
        ''/// check if workbook has been saved
        If ThisWorkbook.Saved = False Then
            ThisWorkbook.Save
        End If
        .Quit
    End With
End Sub

Open in new window


Are you sure that the addin is compatible.
0
Bharat BhushanSolution ManagerCommented:
Application.DisplayAlerts = False
this command line will ignore dialog box from pops-up
move up
above the code
Application.Run "JetReports.xlam!JetMenu",
this code line: Application.DisplayAlerts = False should be come first
0
Roy CoxGroup Finance ManagerCommented:
Application.DisplayAlerts does not prevent all  Alerts, e.g. the save alert. It makes no difference if it is before the line that calls the addin code.
0
RichardAtkAuthor Commented:
Hi

I pasted this onto the bottom of the VB and it worked no idea why Excel 2016 is different but this works thanks Richard
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
VB Script

From novice to tech pro — start learning today.