Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Hi
Can anyone help please, I need to automatically update links as a file opens without any user input. -- Kind Regards Mick |
#2
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
** Links don't update UNLESS source file is open | Links and Linking in Excel | |||
** Links don't update UNLESS source file is open | Links and Linking in Excel | |||
update links without other worksheet open? | Excel Discussion (Misc queries) | |||
Can you update links between workbooks without them both open? | Excel Discussion (Misc queries) | |||
Automate open file, update links, run macro, close and save file | Excel Programming |