ScreenUpdating & DisplayAlerts

[Guest]

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??

 

[Markus Scheible]

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

 

[Guest]

why?

Hi unknown,

please post your real name...



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

 

[Guest]

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]

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

 

[Guest]

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