>> Updating Links

[Jonathan Parminter]

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 Ogilvy]

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

 

[Jonathan Parminter]

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

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

 

[Tom Ogilvy]

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).

 

[Jonathan Parminter]

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

 

[Tom Ogilvy]

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]

Tom, excellent suggestion. It works

Cheers
Jonathan