Save an Excel.xlsm as Excel.xlsx (1 Viewer)

[mveijndh]

I've been working on a large database. I have to export some data as an Excel file . so far I've used the Excel.xlsm format as their where Macro's on the file. Recently I've had some issues with some clients not receiving my files (due to their Outlook settings I presume, deleting the e-mail as potential hazourdus) I'm now looking to save the file as a Excel.xlsx file, but the Macro does not let me do this, not even when I disable the warmings. If I save this manually as an Excel.xlsx file it does work and it also works if I save the file as an Excel.xls file, but that file is also deleted.
Any idea's??

 

[bentheimmigrant]

Any code on how you're trying to save it as xlsx? It's hard to guess what's wrong without that.

What happens when the macro doesn't let you save as?

 

[sneuberg]

I set up a macro in my Access 2013 that looks like:

and it works. What does yours look like and what version of Access do you have?

Also if it's deleting .xls files don't you thing it will delete .xlsx files? Maybe you are going to have to find someway of zipping the file.

 

[mveijndh]

Well Ofcourse, this is what I've used in the past that worked:

 excelApp.ActiveWorkbook.SaveAs strTargetFileName
On Error GoTo Errorhandler
DoCmd.SetWarnings False
strTarget2File = Left(strTargetFileName, Len(strTargetFileName) - 1)
Rem excelApp.ActiveWorkbook.SaveAs strTarget2File
DoCmd.SetWarnings True

when I changed it to

 excelApp.ActiveWorkbook.SaveAs strTargetFileName
On Error GoTo Errorhandler
DoCmd.SetWarnings False
strTarget2File = Left(strTargetFileName, Len(strTargetFileName) - 1) &"x"
Rem excelApp.ActiveWorkbook.SaveAs strTarget2File
DoCmd.SetWarnings True

It failed.

This may have to do with an additional pop up in Excel as you delete the internal macro's on the file, but that's why I disabled the warnings.
When it failes it goes to the error handling module displaying the message "Property or methode not supported"

 

[mveijndh]

Hi Steve,

I can't use a Macro as I'm doing all kinds of things with the data. The Excel.xlsx does not contain any macro's where the Excel.xls and Excel.xlsm can. So I think the fire wall on the application is blocking all potential harmfull files.

 

[bentheimmigrant]

I think you may need to specify the file type explicitly when calling saveas.
https://msdn.microsoft.com/en-us/library/office/ff198017.aspx

You might need to chop off the existing file extension, then use:

excelApp.ActiveWorkbook.SaveAs strTarget2File, 51

 

[mveijndh]

That worked perfectly!!! Thanks