Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel vba xlsm to xlsx format 51, not removing macros

Excel vba xlsm to xlsx format 51, not removing macros

Excel vba xlsm to xlsx format 51, not removing macros

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.




RE: Excel vba xlsm to xlsx format 51, not removing macros

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?


RE: Excel vba xlsm to xlsx format 51, not removing macros


Are you sure?

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

RE: Excel vba xlsm to xlsx format 51, not removing macros

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 smile



Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close