Hi, I have three layers of linked workbooks. The initial
data sheets are linked to group summary workbooks. These
group summary workbooks are then linked to a management
summary workbook.

The user problem is that the group summary workbooks must
be opened to update links, then saved, before the
management summary workbook is opened and updated.

The following code seems to work except the the user is
prompted to save changes before closing linked books. I
want this process to occur in the background without user
intervention. I had thought that application.displayalerts
and having the method save would avoid this...

*** code start ***
aLinks = xlMainBook.LinkSources(xlExcelLinks)
Application.ScreenUpdating = False

If Not IsEmpty(aLinks) Then
For intIndex = 1 To UBound(aLinks)
Application.DisplayAlerts = False

strBook = aLinks(intIndex)
Set xlBook = Workbooks.Open(Filename:=strBook,
UpdateLinks:=True)

With xlBook
.SaveLinkValues = True
.Save
.Close savechanges:=False
End With

Next intIndex
End If

***code ends ***

Any suggestions or ideas appreciated :-)

Thanks
Jonathan

.Close savechanges:=False

has always worked for me to suppress any prompt about saving. Especially
after just doing a save. You can examine the Saved Property of the
workbook and see if it is true. If so, and it should be, you shouldn't get
a prompt.

You might skip the .Save and do

..Close SaveChanges:=True

so it is being saved in the same command as the close.

--
Regards,
Tom Ogilvy



"Jonathan Parminter" wrote in message
...
Hi, I have three layers of linked workbooks. The initial
data sheets are linked to group summary workbooks. These
group summary workbooks are then linked to a management
summary workbook.

The user problem is that the group summary workbooks must
be opened to update links, then saved, before the
management summary workbook is opened and updated.

The following code seems to work except the the user is
prompted to save changes before closing linked books. I
want this process to occur in the background without user
intervention. I had thought that application.displayalerts
and having the method save would avoid this...

*** code start ***
aLinks = xlMainBook.LinkSources(xlExcelLinks)
Application.ScreenUpdating = False

If Not IsEmpty(aLinks) Then
For intIndex = 1 To UBound(aLinks)
Application.DisplayAlerts = False

strBook = aLinks(intIndex)
Set xlBook = Workbooks.Open(Filename:=strBook,
UpdateLinks:=True)

With xlBook
.SaveLinkValues = True
.Save
.Close savechanges:=False
End With

Next intIndex
End If

***code ends ***

Any suggestions or ideas appreciated :-)

Thanks
Jonathan

-----Original Message-----
.Close savechanges:=False

has always worked for me to suppress any prompt about
saving. Especially
after just doing a save. You can examine the Saved
Property of the
workbook and see if it is true. If so, and it should be,
you shouldn't get
a prompt.

You might skip the .Save and do

..Close SaveChanges:=True

so it is being saved in the same command as the close.

--
Regards,
Tom Ogilvy


Thanks Tom,

yeah, tried that. Still get the prompt. Strange really.

I'll check the saved property as you suggest, thanks for
the idea.

However, I think I'll just redesign things to have two,
not three layers...

Cheers
Jonathan

"Jonathan Parminter"
wrote in message
...
Hi, I have three layers of linked workbooks. The initial
data sheets are linked to group summary workbooks. These
group summary workbooks are then linked to a management
summary workbook.

The user problem is that the group summary workbooks
must
be opened to update links, then saved, before the
management summary workbook is opened and updated.

The following code seems to work except the the user is
prompted to save changes before closing linked books. I
want this process to occur in the background without
user
intervention. I had thought that
application.displayalerts
and having the method save would avoid this...

*** code start ***
aLinks = xlMainBook.LinkSources(xlExcelLinks)
Application.ScreenUpdating = False

If Not IsEmpty(aLinks) Then
For intIndex = 1 To UBound(aLinks)
Application.DisplayAlerts = False

strBook = aLinks(intIndex)
Set xlBook = Workbooks.Open
(Filename:=strBook,
UpdateLinks:=True)

With xlBook
.SaveLinkValues = True
.Save
.Close savechanges:=False
End With

Next intIndex
End If

***code ends ***

Any suggestions or ideas appreciated :-)

Thanks
Jonathan


.

If you tried

.Close SaveChanges:=True

And you say you still get the prompt, then you are not getting it from the
workbook being closed by that command (Unless perhaps you have beforeclose
or beforesave events firing in these workbooks).

--
Regards,
Tom Ogilvy


"Jonathan Parminter" wrote in message
...

-----Original Message-----
.Close savechanges:=False

has always worked for me to suppress any prompt about
saving. Especially
after just doing a save. You can examine the Saved
Property of the
workbook and see if it is true. If so, and it should be,
you shouldn't get
a prompt.

You might skip the .Save and do

..Close SaveChanges:=True

so it is being saved in the same command as the close.

--
Regards,
Tom Ogilvy


Thanks Tom,

yeah, tried that. Still get the prompt. Strange really.

I'll check the saved property as you suggest, thanks for
the idea.

However, I think I'll just redesign things to have two,
not three layers...

Cheers
Jonathan

"Jonathan Parminter"
wrote in message
...
Hi, I have three layers of linked workbooks. The initial
data sheets are linked to group summary workbooks. These
group summary workbooks are then linked to a management
summary workbook.

The user problem is that the group summary workbooks
must
be opened to update links, then saved, before the
management summary workbook is opened and updated.

The following code seems to work except the the user is
prompted to save changes before closing linked books. I
want this process to occur in the background without
user
intervention. I had thought that
application.displayalerts
and having the method save would avoid this...

*** code start ***
aLinks = xlMainBook.LinkSources(xlExcelLinks)
Application.ScreenUpdating = False

If Not IsEmpty(aLinks) Then
For intIndex = 1 To UBound(aLinks)
Application.DisplayAlerts = False

strBook = aLinks(intIndex)
Set xlBook = Workbooks.Open
(Filename:=strBook,
UpdateLinks:=True)

With xlBook
.SaveLinkValues = True
.Save
.Close savechanges:=False
End With

Next intIndex
End If

***code ends ***

Any suggestions or ideas appreciated :-)

Thanks
Jonathan


.

Tom,

I followed your suggestion to test xlBook.saved property
immediately after xlBook.save and found that this =False.
This is most perplexing as it is very basic for this test.
With the DisplayAlerts line commented out there is no
error alert.

I have a workbook (Book1) with raw data. Book2 is linked
to Book1. Book3 is linked to Book2. The code snippet is in
Book3. No other code exists in any Book and the only
calculations are =Sum() in Books 2 & 3. In the code xlBook
is Book2.

Thanks
Jonathan

-----Original Message-----
If you tried

.Close SaveChanges:=True

And you say you still get the prompt, then you are not
getting it from the
workbook being closed by that command (Unless perhaps you
have beforeclose
or beforesave events firing in these workbooks).

--
Regards,
Tom Ogilvy


"Jonathan Parminter"
wrote in message
...

-----Original Message-----
.Close savechanges:=False

has always worked for me to suppress any prompt about
saving. Especially
after just doing a save. You can examine the Saved
Property of the
workbook and see if it is true. If so, and it should
be,
you shouldn't get
a prompt.

You might skip the .Save and do

..Close SaveChanges:=True

so it is being saved in the same command as the close.

--
Regards,
Tom Ogilvy


Thanks Tom,

yeah, tried that. Still get the prompt. Strange really.

I'll check the saved property as you suggest, thanks for
the idea.

However, I think I'll just redesign things to have two,
not three layers...

Cheers
Jonathan

"Jonathan Parminter"
wrote in message
...
Hi, I have three layers of linked workbooks. The
initial
data sheets are linked to group summary workbooks.
These
group summary workbooks are then linked to a
management
summary workbook.

The user problem is that the group summary workbooks
must
be opened to update links, then saved, before the
management summary workbook is opened and updated.

The following code seems to work except the the user
is
prompted to save changes before closing linked
books. I
want this process to occur in the background without
user
intervention. I had thought that
application.displayalerts
and having the method save would avoid this...

*** code start ***
aLinks = xlMainBook.LinkSources(xlExcelLinks)
Application.ScreenUpdating = False

If Not IsEmpty(aLinks) Then
For intIndex = 1 To UBound(aLinks)
Application.DisplayAlerts = False

strBook = aLinks(intIndex)
Set xlBook = Workbooks.Open
(Filename:=strBook,
UpdateLinks:=True)

With xlBook
.SaveLinkValues = True
.Save
.Close savechanges:=False
End With

Next intIndex
End If

***code ends ***

Any suggestions or ideas appreciated :-)

Thanks
Jonathan


.



.

I can only speculate that the links are causing the saved property to be set
back to false. You could try setting it to true just before the close

.Saved = True
.Close Savechanges:=False

But I don't know that that would make a difference.

But you say this does not work? (no separate .Save command - done as part
of closing)

With xlBook
.SaveLinkValues = True
.Close savechanges:=True
End With

I just don't see where the above would allow for the prompt.

--
Regards,
Tom Ogilvy





"Jonathan Parminter" wrote in message
...
Tom,

I followed your suggestion to test xlBook.saved property
immediately after xlBook.save and found that this =False.
This is most perplexing as it is very basic for this test.
With the DisplayAlerts line commented out there is no
error alert.

I have a workbook (Book1) with raw data. Book2 is linked
to Book1. Book3 is linked to Book2. The code snippet is in
Book3. No other code exists in any Book and the only
calculations are =Sum() in Books 2 & 3. In the code xlBook
is Book2.

Thanks
Jonathan

-----Original Message-----
If you tried

.Close SaveChanges:=True

And you say you still get the prompt, then you are not
getting it from the
workbook being closed by that command (Unless perhaps you
have beforeclose
or beforesave events firing in these workbooks).

--
Regards,
Tom Ogilvy


"Jonathan Parminter"
wrote in message
...

-----Original Message-----
.Close savechanges:=False

has always worked for me to suppress any prompt about
saving. Especially
after just doing a save. You can examine the Saved
Property of the
workbook and see if it is true. If so, and it should
be,
you shouldn't get
a prompt.

You might skip the .Save and do

..Close SaveChanges:=True

so it is being saved in the same command as the close.

--
Regards,
Tom Ogilvy


Thanks Tom,

yeah, tried that. Still get the prompt. Strange really.

I'll check the saved property as you suggest, thanks for
the idea.

However, I think I'll just redesign things to have two,
not three layers...

Cheers
Jonathan

"Jonathan Parminter"
wrote in message
...
Hi, I have three layers of linked workbooks. The
initial
data sheets are linked to group summary workbooks.
These
group summary workbooks are then linked to a
management
summary workbook.

The user problem is that the group summary workbooks
must
be opened to update links, then saved, before the
management summary workbook is opened and updated.

The following code seems to work except the the user
is
prompted to save changes before closing linked
books. I
want this process to occur in the background without
user
intervention. I had thought that
application.displayalerts
and having the method save would avoid this...

*** code start ***
aLinks = xlMainBook.LinkSources(xlExcelLinks)
Application.ScreenUpdating = False

If Not IsEmpty(aLinks) Then
For intIndex = 1 To UBound(aLinks)
Application.DisplayAlerts = False

strBook = aLinks(intIndex)
Set xlBook = Workbooks.Open
(Filename:=strBook,
UpdateLinks:=True)

With xlBook
.SaveLinkValues = True
.Save
.Close savechanges:=False
End With

Next intIndex
End If

***code ends ***

Any suggestions or ideas appreciated :-)

Thanks
Jonathan


.



.

Tom, excellent suggestion. It works :-)

Cheers
Jonathan

-----Original Message-----
I can only speculate that the links are causing the saved
property to be set
back to false. You could try setting it to true just
before the close

.Saved = True
.Close Savechanges:=False

But I don't know that that would make a difference.

But you say this does not work? (no separate .Save
command - done as part
of closing)

With xlBook
.SaveLinkValues = True
.Close savechanges:=True
End With

I just don't see where the above would allow for the
prompt.

--
Regards,
Tom Ogilvy





"Jonathan Parminter"
wrote in message
...
Tom,

I followed your suggestion to test xlBook.saved property
immediately after xlBook.save and found that this
=False.
This is most perplexing as it is very basic for this
test.
With the DisplayAlerts line commented out there is no
error alert.

I have a workbook (Book1) with raw data. Book2 is linked
to Book1. Book3 is linked to Book2. The code snippet is
in
Book3. No other code exists in any Book and the only
calculations are =Sum() in Books 2 & 3. In the code
xlBook
is Book2.

Thanks
Jonathan

-----Original Message-----
If you tried

.Close SaveChanges:=True

And you say you still get the prompt, then you are not
getting it from the
workbook being closed by that command (Unless perhaps
you
have beforeclose
or beforesave events firing in these workbooks).

--
Regards,
Tom Ogilvy


"Jonathan Parminter"
wrote in message
...

-----Original Message-----
.Close savechanges:=False

has always worked for me to suppress any prompt
about
saving. Especially
after just doing a save. You can examine the Saved
Property of the
workbook and see if it is true. If so, and it
should
be,
you shouldn't get
a prompt.

You might skip the .Save and do

..Close SaveChanges:=True

so it is being saved in the same command as the
close.

--
Regards,
Tom Ogilvy


Thanks Tom,

yeah, tried that. Still get the prompt. Strange
really.

I'll check the saved property as you suggest, thanks
for
the idea.

However, I think I'll just redesign things to have
two,
not three layers...

Cheers
Jonathan

"Jonathan Parminter"
wrote in
message
...
Hi, I have three layers of linked workbooks. The
initial
data sheets are linked to group summary workbooks.
These
group summary workbooks are then linked to a
management
summary workbook.

The user problem is that the group summary
workbooks
must
be opened to update links, then saved, before the
management summary workbook is opened and updated.

The following code seems to work except the the
user
is
prompted to save changes before closing linked
books. I
want this process to occur in the background
without
user
intervention. I had thought that
application.displayalerts
and having the method save would avoid this...

*** code start ***
aLinks = xlMainBook.LinkSources(xlExcelLinks)
Application.ScreenUpdating = False

If Not IsEmpty(aLinks) Then
For intIndex = 1 To UBound(aLinks)
Application.DisplayAlerts = False

strBook = aLinks(intIndex)
Set xlBook = Workbooks.Open
(Filename:=strBook,
UpdateLinks:=True)

With xlBook
.SaveLinkValues = True
.Save
.Close savechanges:=False
End With

Next intIndex
End If

***code ends ***

Any suggestions or ideas appreciated :-)

Thanks
Jonathan


.



.



.