I have an Excel file that I need to export without the VBA buttons. Every time I export it as a Xlsm file, the macros and button export too.

I want to export the excel file as my final report. However, every time I export it, buttons and macros export too.

CodePudding user response：

I'm assuming you're exporting to PDF here as @TimWilliams has the "Export as Excel file" route covered.
If I I try to export this report:

You can see it still keeps my export button.

If I set the button to "Do Not print":

Then when I try to export:

Button is not included in export.

CodePudding user response：

If you want to delete all control buttons on your report before deleting, you can use the following Sub:

Option Explicit
Sub Main()
    
    ' Example Sub to demonstrate usage

    Dim ReportWS As Worksheet
    Dim ShapeToKeep As String
    
    Set ReportWS = Worksheets("Report")
    ShapeToKeep = "Ctrl2"
    
    DeleteControls ReportWS, ShapeToKeep

    'Export Workbook
    
End Sub
Sub DeleteControls(WS As Worksheet, Optional Keep As String)
    
    ' Delete all shapes (except excluded) on sheet 

    Dim Shp As Shape
    
    For Each Shp In WS.Shapes
        If Not IsMissing(Keep) Then
            If Shp.Name <> Keep Then
                Shp.Delete
            End If
        Else
            Shp.Delete
        End If
    Next Shp
    
End Sub

This includes the option to keep specific Shape by name.
If you want to exclude multiple shapes, change Keep to a dictionary.
This is specific to "Shapes", you could change it to delete other objects.

Example:

Before:

After: (with optional excluded shape)

After: (no excluded shape)