3

TLDR:

How do I save a Excel-File-with-Macros as an Excel-File-without-Macros using VBA suppressing the prompt that the target format does not support macros?

I have a xlsb file (Excel 2007+ with Macros), inside is the content and a button that should save that file as an xlsx (Excel 2007 WITHOUT Macros). So far, so good. I'll use ActiveWorkbook.SaveAs FileName:=name, FileFormat=xlOpenXMLWorkbook, Password="xyz"

But that always generates a prompt that tells the user that saving data in the target format means loosing the macro data and if he/she wants to continue...

I've tried Application.DisplayAlerts = False and ThisWorkbook.CheckCompatibility = False. I've also recorded the same action with the MacroRecorder that also seems unable to skip the upcoming prompt.

My last try was a copy of the content using ThisWorkbook.Sheets.Copy and trying to remove the VBProject data. Tried various ways but it seems that the vb-project data can't be really removed. After executing remove and stuff like that the debugger shows always three items of vb-project-data.

I'll check this tomorrow, but it looks very much like the code I've tried... Macro to save active Sheet as new workbook, ask user for location and remove macros from the new workbook

Michael
  • 1,931
  • 2
  • 8
  • 22
  • `Application.SaveAs` ?? Are you not using `Workbook.SaveAs`? – CLR Jun 24 '19 at 16:01
  • You're right. I think it was `ActiveWorkbook.SaveAs`. Don't remember the code exactly and can't access it from home – Michael Jun 24 '19 at 16:03
  • With `Application.DisplayAlerts = False` I don't have the confirmation dialog. (Excel 2010 here) –  Jun 24 '19 at 16:13
  • For some reason my code from yesterday was gone this morning. I've recreated it with `Application.DisplayAlerts = False` and `ActiveWorkbook.SaveAs` and now it works. Very strange :/ Thx for your help :) – Michael Jun 25 '19 at 06:01

1 Answers1

-1

You can use

Thisworkbook.SaveCopyAs ([filename.xlsx]).

This creates a copy of the workbook without the macros in it and without the alert. [filename.xlsx] should of course be replaced by your own file path. Use the xlsx extension to make sure you don't copy the macros.

Alex de Jong
  • 1,257
  • 1
  • 11
  • 23
  • This works too, but since I need to password-protect the new file I'll have to use `.SaveAs` combined with `.DisplayAlerts = False` – Michael Jun 25 '19 at 06:03
  • This doesn't work. You can't use `SaveCopyAs` and change the extension. https://stackoverflow.com/a/46432972/9245853 – BigBen Nov 16 '21 at 14:19