Can VBA Export a Sheet to a Non-Macro Enabled File?

[dhunton]

I did some research on this, and I thought for sure that this would work, but it still saves as a macro-enabled file...

Sub ExportData()

    Dim FileDate As String
    Dim FileName As String
    Dim ReportDate as Date
    Dim FilePath as String
    Dim FinalBook as String
        
    Application.DisplayAlerts = False
    ThisWorkbook.CheckCompatibility = False

    ReportDate = (Date - 1)
    FilePath = ThisWorkbook.Path & "\"
    FinalBook = "Report - "
    
    'Set up file name
    FileDate = Format(ReportDate, "mm-dd-yy")
    FileName = FilePath & FinalBook & FileDate
    
    'Select only the sheet with the final data and save as separate non-macro-enabled file
    ThisWorkbook.Worksheets(1).Copy
    ActiveWorkbook.SaveAs FileName:=FileName, FileFormat:=xlOpenXMLWorkbook
    ActiveWorkbook.Close
        
    Application.DisplayAlerts = True

End Sub 'ExportData

The only thing I've done is to put a "1" in place of a string which is set as a constant at the beginning of my entire project, and I've moved a few of the variables and their declarations into this subroutine (so, changed from "Global" to "Dim").

I'm working in Excel 2016, but this file may also be used by others working with Excel 2010, so it needs to work for both.

[spitfireblue]

Hi,

Try changing:

FileName = FilePath & FinalBook & FileDate

to:

FileName = FilePath & FinalBook & FileDate & ".xlsx"

[dhunton]

spitfireblue,

I was hopeful... but nope, still saves with the macros in place.

[dhunton]

Drat, seems like there really isn't a way to do this.

[spitfireblue]

Can you upload the file, because it was working for me.

[spitfireblue]

In fact, try running the macro in this workbook.

Example.xlsm

[dhunton]

OK, it took me a while to clear out all the confidential data. I hope I've left enough information for you to test with. Let me know if you need more/different info.

[spitfireblue]

I ran it and this is what came out.
All the macros have been removed!
Report - 05-18-16.xlsx

[dhunton]

OK, this makes NO sense to me whatsoever. I did not change the Export subroutine at all in that file, but when I open my original file and use the button to run that subroutine, the macros are still in the file.

[spitfireblue]

It makes no sense to me either!?! Are you sure you are looking at the right file?
Run this one Report Automated Draft.xlsm and then post the result file that you will find in C:/Temp/

[dhunton]

When I open the file I get from running my version, I get a message "SECURITY WARNING Automatic update of links has been disabled" with an "Enable Content" button. The link, when checked, is to my version of the report which created it. Breaking the link seems to fix the issue, but I don't want the end user to have to do that.

When I save your version to the local Downloads folder and press the button, I get a Run-time error that Excel cannot access the file 'C:\Temp\11AAA111' (numbers replaced by 1, letters replaced by A). I will try on another computer that is not on the company network just for testing purposes, but won't be able to do that until tomorrow.

[dhunton]

Works fine on another computer. Differences between them: test computer has Excel 2010 and is NOT on the company network; final computer has Excel 2013 and IS on the company network.

I have tried running it in a directory on the computer as well as one on the network, and it makes no difference.

Any other ideas?

[dhunton]

The export shows as a .xlsx file. I have buttons the end user will use to activate the VBA programming. When one of those is pressed in the exported file, it opens the file that was used to export it (i.e. the original .xlsm file).

Is there any way to avoid this?

[spitfireblue]

Remove the buttons..... if they are shapes you can do that like this:

Sub DelShp()
Dim sh As Shape
For Each sh In ActiveSheet.Shapes
    sh.Delete
Next sh
End Sub

[bakerman2]

Or

Application.CopyObjectsWithCells = False
Sheets(1).Copy
Application.CopyObjectsWithCells = True

[dhunton]

Used bakerman2's suggested solution - worked perfectly to cut the link between the non-VBA worksheet and the one that created it!

YOU GUYS ROCK!