CODEWITHSUNDEEP
excelsavevba
Bernat
Bernat

Reputation: 101

Too long saving time in Excel via VBA

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:

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!

Upvotes: 2

Views: 5635

Answers (3)

MrDominikku
MrDominikku

Reputation: 96

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

Upvotes: 0

Herry Markowitz
Herry Markowitz

Reputation: 308

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

Upvotes: 0

Bernat
Bernat

Reputation: 101

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

Upvotes: 1

Related Questions