Stop calculation of workbooks opened with Workbooks.Open [VBA]

Wiggles

New Member
Joined
Apr 29, 2020
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Important context: my limited VBA skills have grown quite rusty over the past few years.

I have inhereted a spreadsheet (the main spreadsheet) that opens a number of other workbooks and copies and pastes the data found there into itself. These spreadsheets (the data spreadsheets) are updated every week. So far so simple.

Until relatively recently we believed that it just took a very long time to execute, until I stepped through the code manually and found that when the data spreadsheets open they calculate and this can take a very long time. The entire update used to take hours! (I'm not sure why - there's nothing beyond a csv full of cells - but that isn't a question for now). Stepping through the code manually allows you to short circuit the calculations and complete the process in a couple of minutes.

Obviously this is still a workaround and I'd rather do this at the click of a button.

Is there any way to set a sheet being opened with Workbook.Open to manual? Is there any other way to acheive the same?

I've googled this question extensively and I couldn't find anything (beyond the terms being completely smothered by people asking how to generally open a sheet with Manual calculations)

Thanks
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,939
Office Version
  1. 365
Platform
  1. Windows
Have you tried setting Application.Calculation = xlManual while the code runs?

Killing the screen refresh can also make a difference if you're not already doing so Application.ScreenUpdating = False

Remember to set the above back to xlAutomatic and True before your code ends.
 

Wiggles

New Member
Joined
Apr 29, 2020
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Jason, yes, both are in play, but the .calculation settings aren't carrying over to the sheets being opened.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,939
Office Version
  1. 365
Platform
  1. Windows
Beyond that, I would say there is no other way to prevent calculation.

Usually there is no difference to the way that the code is executed between running and stepping through manually unless you are dragging the run line to skip over commands.
 

Wiggles

New Member
Joined
Apr 29, 2020
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

True, it's more that when you're stepping through manually (with screen updating enabled) you can hit escape or click away from the data spreadsheet as it is opened which kills the calculation.

If there were someway to replicate this in the code, then it might help, but TTBOMK there's no way to interupt an operation...
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,939
Office Version
  1. 365
Platform
  1. Windows
Maybe look at re-writing the code to copy without opening the other workbooks?

Other than that, maybe you could try the sendkeys command to emulate pressing escape where needed, although I think that it will interrupt the code that is running rather than the workbook calculation. If you try this, I suspect that a wait command will also be needed to give the workbook enough time to open before attempting to cancel calculation.
 

Wiggles

New Member
Joined
Apr 29, 2020
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Maybe look at re-writing the code to copy without opening the other workbooks?

Wait, that's possible?

Other than that, maybe you could try the sendkeys command to emulate pressing escape where needed, although I think that it will interrupt the code that is running rather than the workbook calculation. If you try this, I suspect that a wait command will also be needed to give the workbook enough time to open before attempting to cancel calculation.

That sounds doable. Forgive my ignorance, but wouldn't the code wait until the Open operation is completed before processing the sendkeys? (also I read somewhere that sendkeys may be sent to the keyboard buffer which would then fire after the entire code is finished running; can you comment?)

Thanks!
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,939
Office Version
  1. 365
Platform
  1. Windows
There might be some limitations, but it is possible.

Sendkeys is not something that I've used enough to be able to give sound advice. It's the type of command that I would only look at as a last resort when all other possibilities have been exhausted. I don't have anything suitable for testing the theory, that would be best done with a copy of the proper workbook.

In theory I was thinking, DoEvents, open workbook, wait 5 seconds, escape with sendkeys, but without testing, I don't know if the events will process in the expected order or if control will be passed correctly, if indeed at all.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,530
Messages
5,596,699
Members
414,088
Latest member
rodriboraun

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top