2

Some unusal phenomenon started affecting my macro. I have a relatively big (13MB) excel workbook. ThisWorkbook.Save worked just fine. It took like 10 seconds to save the workbook (without any data changes). Now it takes for 2 minutes. Same goes for ThisWorkbook.Close SaveChanges:=True. I am using Excel 2010 on Windows 7.

The strange thing is I can save the workbook manually without any issues, like CTRL+S or using the Save icon in the Excel menu. This way the saving procedure takes less than 10 seconds just as my code used to do it.

The occurence of this unnecessary delay is really bugging me. Do you have any experience with an issue like this? I'm looking for tips to get an approach on the problem.

EDIT:

I've changed to xlsb format as was advised and went a little further with testing. There are some cases when the saving time looks appropiate:

  • Significant data changes in lots of sheets (48s)
  • The Save command is triggered from a newly created module without any other changes (5s)
  • All my charts (150pcs) are deleted (1s). Apparently each chart gives about 1 extra second to the saving time. I know, i should use one dynamic but the damn label bugs... And it was totally fine before!

EDIT 2:

The issue has vanished today. Save command works properly again, saving takes for only a few seconds. Dunno what caused the problem, but I am affraid it will be back and will affect my macro some day again. Excel is definitely quirky!

Community
  • 1
  • 1
Bernat
  • 101
  • 1
  • 10
  • 2
    I strongly advice xlsb format if you have big excel file. Look at this; http://stackoverflow.com/questions/7821632/when-should-the-xlsm-or-xlsb-formats-be-used – Herry Markowitz Jan 24 '16 at 11:54
  • I gave it a try and it has decreased the file size impressively (from 13.3 to 10.7MB, -20%). Although it didn't change the lenght of the saving time. It takes for about 130 seconds, just as the `xlsm` while the manual `CTRL+S` works like a charm. But I'm sure from now on I will use the `xlsb`. Thanks for the tip! – Bernat Jan 24 '16 at 12:57
  • Maybe: ActiveWorkbook.Save – Herry Markowitz Jan 24 '16 at 13:03
  • No luck with that either. – Bernat Jan 24 '16 at 13:30

3 Answers3

1

Since the manual CTRL+S works, I go with the SendKey method until there are better solutions. It does a much faster job than the Save command. Bugs like this are just killing me ...

ThisWorkbook.Activate
SendKeys "^s"
DoEvents
ThisWorkbook.Close
Bernat
  • 101
  • 1
  • 10
0

How about this?

Sub Macro1()

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

ThisWorkbook.Save

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

End Sub
Herry Markowitz
  • 208
  • 3
  • 15
0

I had similar issue and been struggling with it for a while. Even for every small files 0.5MB it took almost 1 min to save.

SendKeys doesn't work for me in 100% cases. Sometimes workbook was closed Thisworkbook.Close SaveChanges:=False before SendKeys was able to perform action ("^s"), even after adding Application.Wait Now() + TimeValue("0:00:05").

Somehow it's relating (in my case) to Application.CalculateBeforeSave property. When changed to False workbook saved in couple of seconds.

Dim oWB as Workbook

Set oWB = Workbooks.Open(main_path & file_name, False, False)

oWB.Sheets(1).Calculate

Application.CalculateBeforeSave = False
oWB.Save
Application.CalculateBeforeSave = True

oWB.Close savechanges:=False
set oWB = Nothing
MrDominikku
  • 76
  • 1
  • 4
  • 13