Excel vba xlsm to xlsx format 51, not removing macros

Question

Excel vba xlsm to xlsx format 51, not removing macros

JasonEnsor (Programmer)

(OP)

14 Jul 20 23:23

Hi Guys,

I am working on a project where the user fills in some data on an excel document, clicks a button and it should generate a report which is practically a copy of the ActiveWorkbook. The issue I am facing is when I use ActiveWorkbook.SaveAs filename.xlsx,51 it’s not actually creating a version of the workbook without the Macros. It instead opens up the copy as xlsx with Macros still intact. I tried using ActiveWorkbook.SaveCopyAs filename.xlsx and that gives me the unable to open file due to incorrect extension.

Everything online seems to state that SaveAs method should work. Am I missing a trick here, I feel like it shouldn’t be this hard. Using office 2016 if that helps. There are too many sheets to copy them one by one in to a new workbook.

Any help is appreciated.

J.

Regards

J.

combo (TechnicalUser) · Answer 1 · 2020-07-15 09:30:58

When you save a workbook with macros (more precisely: with VBA project) in 'xlsx' format, you can see the 'xlsx' extension in excel window name and still execute macros in the workbook. Moreover, you can change the workbook, save it keeping 'xlsx' extension, finally close, without any extra warning. But when open the saved 'xlsx' workbook, it has no VBA project.
Is it the same in your case?

combo

strongm (MIS) · Answer 2 · 2020-07-15 10:42:10

>51

Are you sure?

<edit>Oops ... mea culpa. Of course you are trying to eliminate macros, not retain them. Oops.</edit>

JasonEnsor (Programmer) · Answer 3 · 2020-07-15 14:12:20

So to add some more clarity when i click my generate button using SaveCopyAs i get a new file generated with .xlsx extension. I try to open it and it tells me Excel cannot open the file because the format or file extension is not valid.
that uses:

CODE --> vba

ActiveWorkbook.SaveCopyAs baseFilePath & "2.xlsx"

When i generate using

CODE --> vba

ActiveWorkbook.SaveAs FileName:=baseFilePath & ".xlsx", FileFormat:=51

I get a new workbook created, however when i open it i have the security warning message saying macros have been disabled.I have just checked and the macro's have infact gone. It was the Macro warning that was throwing me. What i hadn't accounted for was on my work laptop there has been a policy update to automatically block macros. What i believed was an alert that the book contained macro's was just a red herring.

Sorry for wasting time.

Thanks for getting back to me though

Regards

J.

Come Join Us!

Posting Guidelines

Excel vba xlsm to xlsx format 51, not removing macros