VB projects and XLM sheets cannot be saved in a macro-free workbook

[Jeffrey Mahoney]

I've had this macro running for a couple years with no issue. It basically creates a copy of a sheet in a new WB and then saves it as a .XLSX. With suppressing the alerts it has worked fine until yesterday. I did inspect my file name and is properly formed with .XLSX at the end.

This was posted with no response yet:
Redirecting

Is this a new bug in Excel?

Application.DisplayAlerts = False 'Don't display warning about changing file extension
NewWB.SaveAs Filename:=NewPathFile, FileFormat:=xlOpenXMLWorkbook 'Save to xlsx
Application.DisplayAlerts = True

 

[Domenic]

How are you copying your sheet to your workbook? Are you using the Copy method of the Worksheet object? If so, right-click the sheet tab for the sheet being copied, and select View Code. Does the code module for the worksheet containing any code/event handlers?

 

[ribs]

Yes, this is a new bug.

Excel recently started asking absurd questions about removing the xlsm features and, by the looks of it, not allowing them to be answered/overridden in VBA

If you set DisplayAlerts to True and run the code, you'll see the prompt.

Yet another update from Microsoft which breaks things for no good reason.

I do not know of a workaround at this time.

 

[Domenic]

Sorry Jeffrey, after re-reading your post, I see that I misunderstood.

Yeah, I get the same result.

I didn't realize it until now.

 

[Jeffrey Mahoney]

Thank you for the responses. I hope MS gets flooded with user's comments about this so they come out with a fix real fast. I have about 8 monthly reports that get saved as .XLSX on purpose to lose the VBA code. I'll allow the alerts to display for now.

 

[Fluff]

Are you on the beta channel, as I don't have any problems?

 

[Domenic]

As a workaround, maybe you could specifically delete any lines of code from the sheet's code module...

    With NewWB.VBProject.VBComponents(NewWB.Sheets(1).Name).CodeModule
        .DeleteLines 1, .CountOfLines
    End With
 
    NewWB.SaveAs Filename:=NewPathFile, FileFormat:=xlOpenXMLWorkbook 'Save to xlsx

And, of course, you'll need to allow access to the VBA project object model...

Developer tab >> Code group >> Macro Security >> Macro Settings >> Developer macro settings >> select/check Trust access to the VBA project object model

Hope this helps!

 

[Jeffrey Mahoney]

Not on the Beta channel.

Thanks Dominic, I'll give it a try

 

[Jeffrey Mahoney]

@Dominic

With NewWB.VBProject.VBComponents(NewWB.Sheets(1).Name).CodeModule
.DeleteLines 1, .CountOfLines
End With

Yup, that worked. Luckily this is not used by anyone besides myself at the moment.

@Fluff
So maybe the next version coming out will fix this issue. . . and at the same time maybe they can give us the new Checkboxes they promised

 

[Fluff]

So maybe the next version coming out will fix this issue

I checked yesterday that I'm fully up to date & still have no problems, so not sure what is going on.