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

 

[johnnyL]

@MARK858, Does it work if you remove the 'DisplayAlert' lines & the SendKeys line, Then when the pop up message occurs, tab 3 times and hit Enter? I was under the impression that it would, but I can't test that.

 

[MARK858]

Yes, it works if you manually tab and press enter after the dialog appears

 

[johnnyL]

Well it sounds like maybe the 'DisplayAlerts' line might be messing it up.

Can you try it without the DisplayAlert lines of code, but leave the SendKeys line?

 

[Fluff]

I would have thought the send keys should go after the saveas so the dialogue box is visible.

 

[MARK858]

Can you try it without the DisplayAlert lines of code, but leave the SendKeys line?

That defeats the purpose of the thread to not display the dialog box, we know the displayalerts triggers the alert because it uses the Go Back option rather than the old Yes option.

I would have thought the send keys should go after the saveas so the dialogue box is visible.

Tried that and you get the error first

 

[johnnyL]

@Fluff, it is a timing issue, the SendKeys take some time to actually send, so as long as the SaveAs dialogue box shows up quicky, it will actually show up prior to the SendKeys being delivered.

 

[MARK858]

Can you try it without the DisplayAlert lines of code, but leave the SendKeys line?

Ok it does work without showing the dialog box on my version

 

[johnnyL]

Kewl !!!

 

[MARK858]

Kewl !!!

Please note that there are various temp files that run and cause hang until they have finished running which I don't get saving manually or without the Sendkeys

 

[johnnyL]

Can you elaborate on what you mean please?