Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel vba xlsm to xlsx format 51, not removing macros

Status
Not open for further replies.
JasonEnsor

JasonEnsor

Programmer
Joined
Sep 14, 2010
Messages
193
Location
GB
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.
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
>51

Are you sure?

<edit>Oops ... mea culpa. Of course you are trying to eliminate macros, not retain them. Oops.</edit>
 
Upvote 0 Downvote
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: 
ActiveWorkbook.SaveCopyAs baseFilePath & "2.xlsx"

When i generate using
Code: 
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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Question Question
Ecel Macros not working with V 2505
Replies
4
Views
228
remeng
remeng
rj771972
  • Question Question
Powerpoint VBA - Assign manipulate object
Replies
1
Views
189
rj771972
rj771972
remeng
  • Locked
  • Question Question
Excel VBA to Pass Var between file macros after var changes
Replies
13
Views
1K
mikrom
mikrom
remeng
  • Locked
  • Solved
Combine two working macros into one for MS Word
Replies
7
Views
2K
strongm
strongm
remeng
  • Locked
  • Question Question
Run PowerShell Commands from Excel VBA
Replies
14
Views
5K
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top