.xlsm Export freezing up

[supportsvc]

Hello,
I can't figure out why, but the script to refresh, save then export and save as a .xlsx from the .xlsm with multiple worksheets keeps freezing, I believe on processing the Export function.

This hasn't happened before so I am not sure why it started.

I compared the script with other workbooks that do the same and even copied and pasted and edited the worksheets names.

When I open the .xlsm without launching the code and RefreshAll, that all works. So I know it's not that part. When I select the export section, it worked but then when attempted again, it froze.

This is the only thing that's available as to the freezing up issue. Anyone know what it could be?

Problem signature:
  Problem Event Name:	BEX
  Application Name:	EXCEL.EXE
  Application Version:	16.0.10325.20118
  Application Timestamp:	5b68edfd
  Fault Module Name:	mso20win32client.dll
  Fault Module Version:	0.0.0.0
  Fault Module Timestamp:	5b523906
  Exception Offset:	001cc5e4
  Exception Code:	c0000409
  Exception Data:	00000000
  OS Version:	6.3.9600.2.0.0.272.7
  Locale ID:	1033
  Additional Information 1:	d8a0
  Additional Information 2:	d8a003d775674474d9dbe93763039399
  Additional Information 3:	2019
  Additional Information 4:	2019e8e39e89e6d242ffd268a36c7e98

Read our privacy statement online:
  [URL unfurl="true"]http://go.microsoft.com/fwlink/?linkid=280262[/URL]

If the online privacy statement is not available, please read our privacy statement offline:
  C:\Windows\system32\en-US\erofflps.txt

Here's the script:

Private Sub Workbook_Open()
    Dim myPath As String
    
    Application.DisplayAlerts = False
    ThisWorkbook.RefreshAll
    DoEvents
    Application.DisplayAlerts = False
    ThisWorkbook.Save

    myPath = Application.ActiveWorkbook.Path
    Worksheets(Array("DailyOps", "Consolidated", "Bookings", "Shipped", "OpenOrders", "Backlog")).Copy
    ActiveWorkbook.SaveAs Filename:="C:\Users\Operations\DailysOps-" & Format(Date, "yyyy-mm-dd") & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    DoEvents
    Application.Quit
End Sub

 

[SkipVought]

Hi,

Put a Stop before the first executable line of code.

SAVE.

Then reopen and Step thru your code to determine exactly where it goes south.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

...and exactly what does this do for you?

'
    myPath = Application.ActiveWorkbook.Path
    Worksheets(Array("DailyOps", "Consolidated", "Bookings", "Shipped", "OpenOrders", "Backlog")).Copy

NOTHING! You never use either

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[supportsvc]

Thought it was To save the workbook for each worksheet in the workbook

 

[SkipVought]

Not so!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mintjulep]

You are making a copy of a workbook that obviously has a macro in it and trying to save it as .xlsx that cannot have macros.

What do the two "DoEvents" statements do for you?

 

[supportsvc]

The script is derived from googling.
It worked until recently and there are over 12 workbooks using the same.

Only differences are:
1) the worksheet names
2) the file name of the workbook being saved as

I believe the problem is occuring at the export line of the script.

 

[Andrzejek]

save it as .xlsx that cannot have macros"
I guess that's why there are rwo [tt]Application.DisplayAlerts = False[/tt] to eliminate any warning messages...


---- Andy

There is a great need for a sarcasm font.

 

[supportsvc]

Can't remember what the DoEvents fixed but there was an issue and adding that fixed it.

It appears to be at the Worksheets(Array("DailyOps", "Consolidated", "Bookings", "Shipped", "OpenOrders", "Backlog")).Copy line
removed that and it seems to be working.

That is strange, as mentioned, the other 11 workbooks has this line since not all worksheets are being "copied" and saved as a .xlsx file.

 

[SkipVought]

Maybe that’s a feature that I’m ignorant of. Never saw that before.

If that is indeed saving only the sheets in the saved array in other cases, then check that the names in the array are correct and that you have no leading/trailing spaces, for instance.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

Interesting...
Just created a new Workbook with sheets "Andy", "Barb", and "Chad", provided some data in all 3 sheets, and executed:
[tt]
Worksheets(Array("Andy", "Chad")).Copy[/tt]

That created another Excel file with just 2 sheets: "Andy" and "Chad", ready to be saved.

Who knew...


---- Andy

There is a great need for a sarcasm font.

 

[supportsvc]

Yes, doubled checked the spelling and such of the worksheets in the .copy line

Andrzejek, yes, when saving it as .xslx, need to remove the macro.
and yes, that's why the .copy is needed if there are worksheets not wanting to be saved

 

[SkipVought]

Yes, who knew! I learned something!

I have no other suggestions in mind.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]