asked on

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

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roy Cox

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.

Bharat Bhushan

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

Roy Cox

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.

RichardAtk

ASKER

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