DisplayAlerts & ScreenUpdating Properties Changing Unexpectedly

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!

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

--
Regards,
Tom Ogilvy

"William Horton" <WilliamHorton@discussions.microsoft.com> wrote in message
news:022FFC70-57B1-48B5-8FD2-C2720D3D8BE8@microsoft.com...
> 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!

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" wrote:

> In earlier versions of excel, this is known behavior. What version are you
> using?
>
> --
> Regards,
> Tom Ogilvy
>
> "William Horton" <WilliamHorton@discussions.microsoft.com> wrote in message
> news:022FFC70-57B1-48B5-8FD2-C2720D3D8BE8@microsoft.com...
> > 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!
>
>
>

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...b;en-us;153043
Controlling Alerts and Updating in MS Excel OLE Server

--
Regards,
Tom Ogilvy

"William Horton" <WilliamHorton@discussions.microsoft.com> wrote in message
news:0B5557C4-C4EC-4379-BF57-30FD43EF5292@microsoft.com...
> 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" wrote:
>
> > In earlier versions of excel, this is known behavior. What version are
you
> > using?
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "William Horton" <WilliamHorton@discussions.microsoft.com> wrote in
message
> > news:022FFC70-57B1-48B5-8FD2-C2720D3D8BE8@microsoft.com...
> > > 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!
> >
> >
> >

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.

"Tom Ogilvy" wrote:

> 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...b;en-us;153043
> Controlling Alerts and Updating in MS Excel OLE Server
>
> --
> Regards,
> Tom Ogilvy
>
> "William Horton" <WilliamHorton@discussions.microsoft.com> wrote in message
> news:0B5557C4-C4EC-4379-BF57-30FD43EF5292@microsoft.com...
> > 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" wrote:
> >
> > > In earlier versions of excel, this is known behavior. What version are
> you
> > > using?
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "William Horton" <WilliamHorton@discussions.microsoft.com> wrote in
> message
> > > news:022FFC70-57B1-48B5-8FD2-C2720D3D8BE8@microsoft.com...
> > > > 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!
> > >
> > >
> > >
>
>
>