DisplayAlerts & ScreenUpdating Properties Changing Unexpectedly

[Guest]

I have an Excel workbook that I am controlling via VBA through a module in
Microsoft Access. I have set the Excel DisplayAlerts and ScreenUpdating
properties to FALSE. However, whenever I use the select method
(.Range(A1).Select) the DisplayAlerts & ScreenUpdating properties
auotmatically change themselves back to TRUE. Does anybody know why this is
happening and how I can prevent it?

Dim appXL As Excel.Application
Set appXL = GetObject(, "Excel.Application")
appXL.Workbooks.Open ("D:\My Documents\My Safe
Documents\WorkingATB\horton.xls")
appXL.Application.DisplayAlerts = False
appXL.Application.ScreenUpdating = False

'If I use a watch expression both properties are still indicating FALSE at
this point.

appXL.Range("C2").Select
'If I use a watch expression both properties have just changed back to TRUE
after the select method is carried out.

Help please!

 

[Tom Ogilvy]

In earlier versions of excel, this is known behavior. What version are you
using?

 

[Guest]

I am using Excel 2000. However, when the file I am working with is sent to
Excel from Access (OutputTo action on a query) it defaults to an Excel 5.0/95
Workbook. Does this have something to do with it? If so any workarounds
that you know of. I appreciate the help.

 

[Tom Ogilvy]

Here is the only article I could find on it (I know there are others). This
article doesn't cite anything past xl95, but I know it was a problem through
xl97 and possibly through xl2000 (at least for one of the settings). The
article does suggest some workarounds I believe (or has links to some).
One workaround is to create a macro in the automated workbook. That macro
includes code to make the settings. Once created, it is then executed.
All this is done with the automation client.

http://support.microsoft.com/default.aspx?scid=kb;en-us;153043
Controlling Alerts and Updating in MS Excel OLE Server

 

[Guest]

Thank you. I'll create the Excel macro in a new Excel workbook and have
Access call the macro in that workbook each time. Your research is much
appreciated.