ScreenUpdating & DisplayAlerts

In my current code I've set both the above attributes to false, but when I
try to open a workbook someone else is in using the code I still get the
dialog box asking if I want to open it Read Only, Notify when it's read-write
or Cancel.

I want to try and automate this as much as possible because I need to open
10 spreadsheet and take one tab from each into a master sheet and I don't
want to sit there clicking Read Only each time.

Any ideas??

Hi unknown,

please post your real name...


>-----Original Message-----
>In my current code I've set both the above attributes to
false, but when I
>try to open a workbook someone else is in using the code
I still get the
>dialog box asking if I want to open it Read Only, Notify
when it's read-write
>or Cancel.

The thing is that you have to give something back to that
window - otherwise the workbook wouldnt open - so excel
asks you even if you turned the window.updating to false...

but with the open dialog you can also give the command to
open it read-only by:

Workbooks.Open FileName:="abcd.xls", ReadOnly:=True

for Read-Only:

Optional Variant. True to open the workbook in read-only
mode.


This is from the Excel help in Windows...

Best Markus



>
>I want to try and automate this as much as possible
because I need to open
>10 spreadsheet and take one tab from each into a master
sheet and I don't
>want to sit there clicking Read Only each time.
>
>Any ideas??
>.
>

why?

"Markus Scheible" wrote:

> Hi unknown,
>
> please post your real name...
>
>
> >-----Original Message-----
> >In my current code I've set both the above attributes to
> false, but when I
> >try to open a workbook someone else is in using the code
> I still get the
> >dialog box asking if I want to open it Read Only, Notify
> when it's read-write
> >or Cancel.
>
> The thing is that you have to give something back to that
> window - otherwise the workbook wouldnt open - so excel
> asks you even if you turned the window.updating to false...
>
> but with the open dialog you can also give the command to
> open it read-only by:
>
> Workbooks.Open FileName:="abcd.xls", ReadOnly:=True
>
> for Read-Only:
>
> Optional Variant. True to open the workbook in read-only
> mode.
>
>
> This is from the Excel help in Windows...
>
> Best Markus
>
>
>
> >
> >I want to try and automate this as much as possible
> because I need to open
> >10 spreadsheet and take one tab from each into a master
> sheet and I don't
> >want to sit there clicking Read Only each time.
> >
> >Any ideas??
> >.
> >
>

The problem is, in workbook A I have code that opens workbook B -> K. In each
of the workbooks B -> K they then open a further number of workbooks that the
users input to to update themselves with the most current info. The code I
have in workbooks B -> K has diplayalerts and screenupdating as false and if
run seperately I have no problems.

The messages appear when trying to run it all through workbook A to get an
overall view of what's been input.

I know this is a bad set-up, but I've been asked to set this up at varying
stages and now to go back to the start and modify it all to run this big
overview is a very big task! I just wondered if there was a reason I was
getting the Read Only etc messages??

"Markus Scheible" wrote:
>
> The thing is that you have to give something back to that
> window - otherwise the workbook wouldnt open - so excel
> asks you even if you turned the window.updating to false...
>
> but with the open dialog you can also give the command to
> open it read-only by:
>
> Workbooks.Open FileName:="abcd.xls", ReadOnly:=True
>
> for Read-Only:
>
> Optional Variant. True to open the workbook in read-only
> mode.
>
>

Hi unknown,


>-----Original Message-----
>The problem is, in workbook A I have code that opens
workbook B -> K. In each
>of the workbooks B -> K they then open a further number
of workbooks that the
>users input to to update themselves with the most current
info. The code I

that looks like a real problem, because every workbook
that opens another workbook will ask you to open it read-
only...


>have in workbooks B -> K has diplayalerts and
screenupdating as false and if
>run seperately I have no problems.

Are you sure that if you run these workbooks they will not
ask you how to open the linked workbooks? I hardly can
imagine that, because if a workbook is already open by
another user and you try to get data from it excel will
show you this message not caring about the screenupdating
(because without knowing if excel should open it readonly
excel will not open it)


>
>The messages appear when trying to run it all through
workbook A to get an
>overall view of what's been input.
>

Nevertheless, I think you can just use the UpdateLinks
statement to change that situation... and I think you need
to include this in every of the worksheets B-K (in every
worksheet that needs data from another worksheet which may
be opened already by another user)

PS: why posting with your real name? Because its usual and
more personal, I think...


Best

Markus


>I know this is a bad set-up, but I've been asked to set
this up at varying
>stages and now to go back to the start and modify it all
to run this big
>overview is a very big task! I just wondered if there was
a reason I was
>getting the Read Only etc messages??




>
>"Markus Scheible" wrote:
>>
>> The thing is that you have to give something back to
that
>> window - otherwise the workbook wouldnt open - so excel
>> asks you even if you turned the window.updating to
false...
>>
>> but with the open dialog you can also give the command
to
>> open it read-only by:
>>
>> Workbooks.Open FileName:="abcd.xls", ReadOnly:=True
>>
>> for Read-Only:
>>
>> Optional Variant. True to open the workbook in read-
only
>> mode.
>>
>>
>.
>

UpdateLinks won't work, because I'm not actually updating links. What I'm
doing is opening up spreadsheets and copying the sheets within them into the
current workbook (links wouldn't work because the data shifts around daily so
I'd always have a problem making sure the links are pointing where they're
meant to).

So (going back to workbook A & B etc) I have workbook A open. I then run my
macro that opens workbook B. This then needs updating so it runs a macro that
opens B1, B2, B3 etc and copies a certain sheet into workbook B. Once this is
done, workbook A then imports that sheet and moves onto workbook C where the
process is repeated.

I use the same code in workbook A to update as in workbook B (except I have
added in a line to kick off the update macro), and if I run it from workbook
B it updates fine. If I run it from workbook A, it opens up B, C, D etc fine,
but when it gets to opening up B1, B2 is where the message comes up. Do I
need an extra line of code somewhere to make sure these open without the
alerts too?

Hope this helps explain my problem more??

Chris

"Markus Scheible" wrote:

> Hi unknown,
>
>
> >-----Original Message-----
> >The problem is, in workbook A I have code that opens
> workbook B -> K. In each
> >of the workbooks B -> K they then open a further number
> of workbooks that the
> >users input to to update themselves with the most current
> info. The code I
>
> that looks like a real problem, because every workbook
> that opens another workbook will ask you to open it read-
> only...
>
>
> >have in workbooks B -> K has diplayalerts and
> screenupdating as false and if
> >run seperately I have no problems.
>
> Are you sure that if you run these workbooks they will not
> ask you how to open the linked workbooks? I hardly can
> imagine that, because if a workbook is already open by
> another user and you try to get data from it excel will
> show you this message not caring about the screenupdating
> (because without knowing if excel should open it readonly
> excel will not open it)
>
>
> >
> >The messages appear when trying to run it all through
> workbook A to get an
> >overall view of what's been input.
> >
>
> Nevertheless, I think you can just use the UpdateLinks
> statement to change that situation... and I think you need
> to include this in every of the worksheets B-K (in every
> worksheet that needs data from another worksheet which may
> be opened already by another user)
>
> PS: why posting with your real name? Because its usual and
> more personal, I think...
>
>
> Best
>
> Markus
>
>
> >I know this is a bad set-up, but I've been asked to set
> this up at varying
> >stages and now to go back to the start and modify it all
> to run this big
> >overview is a very big task! I just wondered if there was
> a reason I was
> >getting the Read Only etc messages??
>
>
>
>
> >
> >"Markus Scheible" wrote:
> >>
> >> The thing is that you have to give something back to
> that
> >> window - otherwise the workbook wouldnt open - so excel
> >> asks you even if you turned the window.updating to
> false...
> >>
> >> but with the open dialog you can also give the command
> to
> >> open it read-only by:
> >>
> >> Workbooks.Open FileName:="abcd.xls", ReadOnly:=True
> >>
> >> for Read-Only:
> >>
> >> Optional Variant. True to open the workbook in read-
> only
> >> mode.
> >>
> >>
> >.
> >
>