Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 17
Default Update Links on file open

Hi
Can anyone help please, I need to automatically update links as a file opens
without any user input.

--
Kind Regards

Mick


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,824
Default Update Links on file open

You can toggle the setting (user by user, though) via:

Tools|Options|Edit Tab.
There's a checkmark for "ask to update automatic links"

But this means that you suppress the question--the links still get updated.

This setting is for the individual user--and affects all their workbooks.

If you want more control:
Try creating a dummy workbook whose only purpose is to open the original
workbook with links updated:

Kind of like:

Option Explicit
Sub auto_open()
Workbooks.Open Filename:="c:\my documents\excel\book2.xls", UpdateLinks:=1
ThisWorkbook.Close savechanges:=False
End Sub

Then you open the dummy workbook and the links will be refreshed.

And xl2002 added an option that allows you to have more control:
Edit|links|startup prompt button.
check the "don't display the alert and update links"




Mick Southam wrote:

Hi
Can anyone help please, I need to automatically update links as a file opens
without any user input.

--
Kind Regards

Mick


--

Dave Peterson

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default Update Links on file open

Just to add, any solution involving changing the user's settings using code,
will not work for the workbook being opened - at least not the first time it
is opened. The prompt appears before any macros are run.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
You can toggle the setting (user by user, though) via:

Tools|Options|Edit Tab.
There's a checkmark for "ask to update automatic links"

But this means that you suppress the question--the links still get

updated.

This setting is for the individual user--and affects all their workbooks.

If you want more control:
Try creating a dummy workbook whose only purpose is to open the original
workbook with links updated:

Kind of like:

Option Explicit
Sub auto_open()
Workbooks.Open Filename:="c:\my documents\excel\book2.xls",

UpdateLinks:=1
ThisWorkbook.Close savechanges:=False
End Sub

Then you open the dummy workbook and the links will be refreshed.

And xl2002 added an option that allows you to have more control:
Edit|links|startup prompt button.
check the "don't display the alert and update links"




Mick Southam wrote:

Hi
Can anyone help please, I need to automatically update links as a file

opens
without any user input.

--
Kind Regards

Mick


--

Dave Peterson



  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 17
Default Update Links on file open

Dave

I have done as you suggested and it works the first time I use it, but not
if I open it again from within another macro.

Rather than use a normal file I am using a template as I need a blank file
open each time, it will open the template but does not run the Sub
Auto_Open() or at least I think that's what happens. Does this mean it need
to be written differently for a template. I have tried using Workbooks.Add
Template:= instead but when I do UpdateLinks will not work.

Regards

Mick

"Dave Peterson" wrote in message
...
You can toggle the setting (user by user, though) via:

Tools|Options|Edit Tab.
There's a checkmark for "ask to update automatic links"

But this means that you suppress the question--the links still get

updated.

This setting is for the individual user--and affects all their workbooks.

If you want more control:
Try creating a dummy workbook whose only purpose is to open the original
workbook with links updated:

Kind of like:

Option Explicit
Sub auto_open()
Workbooks.Open Filename:="c:\my documents\excel\book2.xls",

UpdateLinks:=1
ThisWorkbook.Close savechanges:=False
End Sub

Then you open the dummy workbook and the links will be refreshed.

And xl2002 added an option that allows you to have more control:
Edit|links|startup prompt button.
check the "don't display the alert and update links"




Mick Southam wrote:

Hi
Can anyone help please, I need to automatically update links as a file

opens
without any user input.

--
Kind Regards

Mick


--

Dave Peterson



  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default Update Links on file open

Put your code in the Workbook_Open event found in the ThisWorkbook module.

--
Regards,
Tom Ogilvy

"Mick Southam" wrote in message
...
Dave

I have done as you suggested and it works the first time I use it, but not
if I open it again from within another macro.

Rather than use a normal file I am using a template as I need a blank file
open each time, it will open the template but does not run the Sub
Auto_Open() or at least I think that's what happens. Does this mean it

need
to be written differently for a template. I have tried using

Workbooks.Add
Template:= instead but when I do UpdateLinks will not work.

Regards

Mick

"Dave Peterson" wrote in message
...
You can toggle the setting (user by user, though) via:

Tools|Options|Edit Tab.
There's a checkmark for "ask to update automatic links"

But this means that you suppress the question--the links still get

updated.

This setting is for the individual user--and affects all their

workbooks.

If you want more control:
Try creating a dummy workbook whose only purpose is to open the original
workbook with links updated:

Kind of like:

Option Explicit
Sub auto_open()
Workbooks.Open Filename:="c:\my documents\excel\book2.xls",

UpdateLinks:=1
ThisWorkbook.Close savechanges:=False
End Sub

Then you open the dummy workbook and the links will be refreshed.

And xl2002 added an option that allows you to have more control:
Edit|links|startup prompt button.
check the "don't display the alert and update links"




Mick Southam wrote:

Hi
Can anyone help please, I need to automatically update links as a file

opens
without any user input.

--
Kind Regards

Mick


--

Dave Peterson







  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 17
Default Update Links on file open

Dave

Just to add, if I step through the Sub Auto_Open() it works perfectly how
can I make this happen automatically?

As always Thanks
Mick


"Dave Peterson" wrote in message
...
You can toggle the setting (user by user, though) via:

Tools|Options|Edit Tab.
There's a checkmark for "ask to update automatic links"

But this means that you suppress the question--the links still get

updated.

This setting is for the individual user--and affects all their workbooks.

If you want more control:
Try creating a dummy workbook whose only purpose is to open the original
workbook with links updated:

Kind of like:

Option Explicit
Sub auto_open()
Workbooks.Open Filename:="c:\my documents\excel\book2.xls",

UpdateLinks:=1
ThisWorkbook.Close savechanges:=False
End Sub

Then you open the dummy workbook and the links will be refreshed.

And xl2002 added an option that allows you to have more control:
Edit|links|startup prompt button.
check the "don't display the alert and update links"




Mick Southam wrote:

Hi
Can anyone help please, I need to automatically update links as a file

opens
without any user input.

--
Kind Regards

Mick


--

Dave Peterson



  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 17
Default Update Links on file open

Dave/Tom

Guys I'm sorry about this but I must be missing something here.

I have now put the following in the ThisWorkbook module but still have the
same problem, but again if I step through it works:

Sub Auto_Open
Workbooks.Open Filename:=ThisWorkbook.Path & ("\PS27a(manual).xlt"),
UpdateLinks:=3
'ThisWorkbook.Close savechanges:=False ' rem'ed out for now
End Sub

Regards

Mick

"Tom Ogilvy" wrote in message
...
Put your code in the Workbook_Open event found in the ThisWorkbook module.

--
Regards,
Tom Ogilvy

"Mick Southam" wrote in message
...
Dave

I have done as you suggested and it works the first time I use it, but

not
if I open it again from within another macro.

Rather than use a normal file I am using a template as I need a blank

file
open each time, it will open the template but does not run the Sub
Auto_Open() or at least I think that's what happens. Does this mean it

need
to be written differently for a template. I have tried using

Workbooks.Add
Template:= instead but when I do UpdateLinks will not work.

Regards

Mick

"Dave Peterson" wrote in message
...
You can toggle the setting (user by user, though) via:

Tools|Options|Edit Tab.
There's a checkmark for "ask to update automatic links"

But this means that you suppress the question--the links still get

updated.

This setting is for the individual user--and affects all their

workbooks.

If you want more control:
Try creating a dummy workbook whose only purpose is to open the

original
workbook with links updated:

Kind of like:

Option Explicit
Sub auto_open()
Workbooks.Open Filename:="c:\my documents\excel\book2.xls",

UpdateLinks:=1
ThisWorkbook.Close savechanges:=False
End Sub

Then you open the dummy workbook and the links will be refreshed.

And xl2002 added an option that allows you to have more control:
Edit|links|startup prompt button.
check the "don't display the alert and update links"




Mick Southam wrote:

Hi
Can anyone help please, I need to automatically update links as a

file
opens
without any user input.

--
Kind Regards

Mick

--

Dave Peterson







  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default Update Links on file open

I said in the Workbook_Open Event.

In the thisworkbook module, in the left dropdown at the top, select Workbook
from the left dropdown and Open from the right dropdown. It will put in
code like this

Private Sub Workbook_Open()


End Sub

put your code in there


Private Sub Workbook_Open()
Workbooks.Open Filename:= _
ThisWorkbook.Path & ("\PS27a(manual).xlt"), _
UpdateLinks:=3
'ThisWorkbook.Close savechanges:=False ' rem'ed out for now
End Sub

--
Regards,
Tom Ogilvy


End Sub


"Mick Southam" wrote in message
...
Dave/Tom

Guys I'm sorry about this but I must be missing something here.

I have now put the following in the ThisWorkbook module but still have the
same problem, but again if I step through it works:

Sub Auto_Open
Workbooks.Open Filename:=ThisWorkbook.Path &

("\PS27a(manual).xlt"),
UpdateLinks:=3
'ThisWorkbook.Close savechanges:=False ' rem'ed out for now
End Sub

Regards

Mick

"Tom Ogilvy" wrote in message
...
Put your code in the Workbook_Open event found in the ThisWorkbook

module.

--
Regards,
Tom Ogilvy

"Mick Southam" wrote in message
...
Dave

I have done as you suggested and it works the first time I use it, but

not
if I open it again from within another macro.

Rather than use a normal file I am using a template as I need a blank

file
open each time, it will open the template but does not run the Sub
Auto_Open() or at least I think that's what happens. Does this mean it

need
to be written differently for a template. I have tried using

Workbooks.Add
Template:= instead but when I do UpdateLinks will not work.

Regards

Mick

"Dave Peterson" wrote in message
...
You can toggle the setting (user by user, though) via:

Tools|Options|Edit Tab.
There's a checkmark for "ask to update automatic links"

But this means that you suppress the question--the links still get
updated.

This setting is for the individual user--and affects all their

workbooks.

If you want more control:
Try creating a dummy workbook whose only purpose is to open the

original
workbook with links updated:

Kind of like:

Option Explicit
Sub auto_open()
Workbooks.Open Filename:="c:\my documents\excel\book2.xls",
UpdateLinks:=1
ThisWorkbook.Close savechanges:=False
End Sub

Then you open the dummy workbook and the links will be refreshed.

And xl2002 added an option that allows you to have more control:
Edit|links|startup prompt button.
check the "don't display the alert and update links"




Mick Southam wrote:

Hi
Can anyone help please, I need to automatically update links as a

file
opens
without any user input.

--
Kind Regards

Mick

--

Dave Peterson









  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,824
Default Update Links on file open

Ah, but Tom suggested that you put the code into the Workbook_open
procedure--not just in the ThisWorkbook module.

But since it's a template, you could use another argument in the workbooks.open
statement:

Workbooks.Open Filename:=ThisWorkbook.Path & "\book1.xlt", UpdateLinks:=3, _
editable:=False

The editable:=false works nicely with the template.

And this worked in the Auto_open procedure for me, too.

====
and just for no good reason at all...

since you're in another macro, you can change the setting, open the workbook,
and change it back. (I wouldn't use this!):

Dim ExistingAskToUpdateLinks As Boolean
With Application
ExistingAskToUpdateLinks = .AskToUpdateLinks
.AskToUpdateLinks = False
Workbooks.Add template:=ThisWorkbook.Path & "\book1.xlt"
.AskToUpdateLinks = ExistingAskToUpdateLinks
End With

'ThisWorkbook.Close savechanges:=false

But something like this could come in handy(???) later on (probably not,
though!).



Mick Southam wrote:

Dave/Tom

Guys I'm sorry about this but I must be missing something here.

I have now put the following in the ThisWorkbook module but still have the
same problem, but again if I step through it works:

Sub Auto_Open
Workbooks.Open Filename:=ThisWorkbook.Path & ("\PS27a(manual).xlt"),
UpdateLinks:=3
'ThisWorkbook.Close savechanges:=False ' rem'ed out for now
End Sub

Regards

Mick

"Tom Ogilvy" wrote in message
...
Put your code in the Workbook_Open event found in the ThisWorkbook module.

--
Regards,
Tom Ogilvy

"Mick Southam" wrote in message
...
Dave

I have done as you suggested and it works the first time I use it, but

not
if I open it again from within another macro.

Rather than use a normal file I am using a template as I need a blank

file
open each time, it will open the template but does not run the Sub
Auto_Open() or at least I think that's what happens. Does this mean it

need
to be written differently for a template. I have tried using

Workbooks.Add
Template:= instead but when I do UpdateLinks will not work.

Regards

Mick

"Dave Peterson" wrote in message
...
You can toggle the setting (user by user, though) via:

Tools|Options|Edit Tab.
There's a checkmark for "ask to update automatic links"

But this means that you suppress the question--the links still get
updated.

This setting is for the individual user--and affects all their

workbooks.

If you want more control:
Try creating a dummy workbook whose only purpose is to open the

original
workbook with links updated:

Kind of like:

Option Explicit
Sub auto_open()
Workbooks.Open Filename:="c:\my documents\excel\book2.xls",
UpdateLinks:=1
ThisWorkbook.Close savechanges:=False
End Sub

Then you open the dummy workbook and the links will be refreshed.

And xl2002 added an option that allows you to have more control:
Edit|links|startup prompt button.
check the "don't display the alert and update links"




Mick Southam wrote:

Hi
Can anyone help please, I need to automatically update links as a

file
opens
without any user input.

--
Kind Regards

Mick

--

Dave Peterson






--

Dave Peterson

  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 17
Default Update Links on file open

Thanks Guys for all your help I'm now sorted.
Regards
Mick


"Dave Peterson" wrote in message
...
Ah, but Tom suggested that you put the code into the Workbook_open
procedure--not just in the ThisWorkbook module.

But since it's a template, you could use another argument in the

workbooks.open
statement:

Workbooks.Open Filename:=ThisWorkbook.Path & "\book1.xlt",

UpdateLinks:=3, _
editable:=False

The editable:=false works nicely with the template.

And this worked in the Auto_open procedure for me, too.

====
and just for no good reason at all...

since you're in another macro, you can change the setting, open the

workbook,
and change it back. (I wouldn't use this!):

Dim ExistingAskToUpdateLinks As Boolean
With Application
ExistingAskToUpdateLinks = .AskToUpdateLinks
.AskToUpdateLinks = False
Workbooks.Add template:=ThisWorkbook.Path & "\book1.xlt"
.AskToUpdateLinks = ExistingAskToUpdateLinks
End With

'ThisWorkbook.Close savechanges:=false

But something like this could come in handy(???) later on (probably not,
though!).



Mick Southam wrote:

Dave/Tom

Guys I'm sorry about this but I must be missing something here.

I have now put the following in the ThisWorkbook module but still have

the
same problem, but again if I step through it works:

Sub Auto_Open
Workbooks.Open Filename:=ThisWorkbook.Path &

("\PS27a(manual).xlt"),
UpdateLinks:=3
'ThisWorkbook.Close savechanges:=False ' rem'ed out for now
End Sub

Regards

Mick

"Tom Ogilvy" wrote in message
...
Put your code in the Workbook_Open event found in the ThisWorkbook

module.

--
Regards,
Tom Ogilvy

"Mick Southam" wrote in message
...
Dave

I have done as you suggested and it works the first time I use it,

but
not
if I open it again from within another macro.

Rather than use a normal file I am using a template as I need a

blank
file
open each time, it will open the template but does not run the Sub
Auto_Open() or at least I think that's what happens. Does this mean

it
need
to be written differently for a template. I have tried using
Workbooks.Add
Template:= instead but when I do UpdateLinks will not work.

Regards

Mick

"Dave Peterson" wrote in message
...
You can toggle the setting (user by user, though) via:

Tools|Options|Edit Tab.
There's a checkmark for "ask to update automatic links"

But this means that you suppress the question--the links still get
updated.

This setting is for the individual user--and affects all their
workbooks.

If you want more control:
Try creating a dummy workbook whose only purpose is to open the

original
workbook with links updated:

Kind of like:

Option Explicit
Sub auto_open()
Workbooks.Open Filename:="c:\my documents\excel\book2.xls",
UpdateLinks:=1
ThisWorkbook.Close savechanges:=False
End Sub

Then you open the dummy workbook and the links will be refreshed.

And xl2002 added an option that allows you to have more control:
Edit|links|startup prompt button.
check the "don't display the alert and update links"




Mick Southam wrote:

Hi
Can anyone help please, I need to automatically update links as

a
file
opens
without any user input.

--
Kind Regards

Mick

--

Dave Peterson






--

Dave Peterson



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
** Links don't update UNLESS source file is open Jack Links and Linking in Excel 1 July 27th 05 02:02 PM
** Links don't update UNLESS source file is open Jack Links and Linking in Excel 0 July 14th 05 05:55 PM
update links without other worksheet open? Mary C Excel Discussion (Misc queries) 3 June 9th 05 08:00 PM
Can you update links between workbooks without them both open? Nicolle K. Excel Discussion (Misc queries) 1 January 7th 05 07:39 PM
Automate open file, update links, run macro, close and save file Geoff[_7_] Excel Programming 2 August 26th 03 10:13 PM


All times are GMT +1. The time now is 09:03 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"