• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 86
  • Last Modified:

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
0
RichardAtk
Asked:
RichardAtk
1 Solution
 
NorieVBA ExpertCommented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now