Disable Screen Updating, Auto Calculation in Excel from Access VBA

Question

How do I update the .Application properties in an Excel workbook from Access VBA?

I'm using Access VBA to export data to Excel, open the file, and format the file (add formulas, etc.).

The code is slow. I want to disable screen updates and change Excel calculations to manual.

.ScreenUpdating = false doesn't produce run-time errors, but the screen updates as if the code is ignored.

.Application.Calculation produces a run-time error 1004.

Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook

Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject(Forms!Main!Final_filepath)
XlBook.Activate
XlBook.Windows(1).Visible = True

'Hide alerts to prevent interruptions when running code
Xl.Application.DisplayAlerts = False  '<---- No errors
Xl.Application.ScreenUpdating = False '<---- No errors, but screen updating isn't disabled. 
Xl.Application.Calculation = xlCalculationManual  '<---- Run-Time Error 1004

If Xl is an Excel.Application (and the code compiles), then Xl.Application is redundant. Does Xl.ScreenUpdating = False work any better? (not expecting it to, since Xl.Application should just point right back at the Xl object) — Mathieu Guindon
FWIW Set Xl = CreateObject("Excel.Application") is a rather roundabout way to do Set Xl = New Excel.Application (again, not the problem, just pointing it out) — Mathieu Guindon
Some of the code is missing, right? I can't wrap my head around the fact that no workbook is ever opened in the created Excel.Application instance, and yet XlBook is being assigned just fine... GetObject seems a weird way to do this. Set XlBook = Xl.Workbooks.Open(Forms!Main!Final_filepath) would be simpler, it seems. — Mathieu Guindon
I suspect the code that is missing, is holding the answer - most likely, there's an implicit Excel.Application instance being created by accident somewhere, somehow (it's very easy to do! simply forget a Xl. qualifier somewhere and boom, implicit app reference, and ghost EXCEL.EXE process lingering in Task Manager well after the macro completed). — Mathieu Guindon
Hi Mathieu, thanks for all of your wonderful suggestions. I agree the code I used is a bit clunky, and thanks for the suggestions for a cleaner syntax. I'm not a formally trained macro writer; I've learned everything in bits and pieces and many, many google searches over the years. The code I used has been shamelessly copied and Frankensteined together to accomplish the end goals. — Angie Hudson Gallegos

Angie Hudson Gallegos Angie Hudson Gallegos · Accepted Answer · 2019-11-07T23:42:41

I figured it out! My original code was actually opening two instances of Excel - "Xl" and "XlBook" Xl opens as Excel, but without any open workbooks. XlBook opens as Excel, with the workbook (from file path in the Access form) loaded.

The Xl.Application attempted to apply workbook properties to the Excel window with no workbook loaded. Hence the run time error for .Calculation. With no open workbook, the .DisplayAlerts and .ScreenUpdating are meaningless, but didn't create a run-time error - simply the code can't actually apply it to a workbook since no workbook is actually open. I suppose if I then subsequently opened a workbook after the fact, that the properties would be applied?

So, instead of referencing Xl, I referenced XlBook, so the properties could be applied to the open workbook. I removed all references to Xl since I realized it was a redundant Excel window and not needed anyway.

Dim XlBook As Excel.Workbook
'Open Excel workbook, and set reference to worksheet
    Set XlBook = GetObject(Forms!main!Final_filepath)
    XlBook.Activate
    XlBook.Windows(1).Visible = True

'Disable calcs and screen updating to increase speed
    XlBook.Application.Calculation = xlCalculationManual
    XlBook.Application.ScreenUpdating = False

Disable Screen Updating, Auto Calculation in Excel from Access VBA

2 Answers