| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL03: Avoid "workbook contains links" msg when opening
'Open workbook
Application.DisplayAlerts = False Set twb = Workbooks.Open(FileName:=MyFullFilePath, ReadOnly:=True) Application.DisplayAlerts = True When my target workbook contains links, I still get a warning pop-up asking if links should be updated, even with displayalerts = False. So I changed the open statement to: Application.DisplayAlerts = False Set twb = Workbooks.Open(FileName:=MyFullFilePath, UpdateLinks:=False, ReadOnly:=True) Application.DisplayAlerts = True It bypasses that initial warning, but then pops up the filesearch wizard/userform with the title bar "Update Values: HsTbar.xla" which I'm guessing is an add-in used by the person providing me the data sheet. This is not what I'd expect, since I set UpdateLinks = False I'm just trying to copy the data out of one sheet (format and values, not formulas or links) but I need to do so in a way that is fully automated (no user prompts) so that my code can run overnight. How can I completely bypass the links alerts and just open the file via VBA? Thank you, Keith |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
XL03: Avoid "workbook contains links" msg when opening
If you have links in your workbook this (UpdateLinks:=0) will avoid the message
....from a Ron de Bruin post... -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ker_01" wrote: 'Open workbook Application.DisplayAlerts = False Set twb = Workbooks.Open(FileName:=MyFullFilePath, ReadOnly:=True) Application.DisplayAlerts = True When my target workbook contains links, I still get a warning pop-up asking if links should be updated, even with displayalerts = False. So I changed the open statement to: Application.DisplayAlerts = False Set twb = Workbooks.Open(FileName:=MyFullFilePath, UpdateLinks:=False, ReadOnly:=True) Application.DisplayAlerts = True It bypasses that initial warning, but then pops up the filesearch wizard/userform with the title bar "Update Values: HsTbar.xla" which I'm guessing is an add-in used by the person providing me the data sheet. This is not what I'd expect, since I set UpdateLinks = False I'm just trying to copy the data out of one sheet (format and values, not formulas or links) but I need to do so in a way that is fully automated (no user prompts) so that my code can run overnight. How can I completely bypass the links alerts and just open the file via VBA? Thank you, Keith |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
XL03: Avoid "workbook contains links" msg when opening
Correct, it is wrong in the help of a lot of Exel versions, i believe it is correct
in the good old Excel 2000 If you have links in your workbook this (UpdateLinks:=0) will avoid the message do you want to update the links or not "0 Doesn't update any references" Use 3 instead of 0 if you want to update the links. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "ryguy7272" wrote in message ... If you have links in your workbook this (UpdateLinks:=0) will avoid the message ...from a Ron de Bruin post... -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ker_01" wrote: 'Open workbook Application.DisplayAlerts = False Set twb = Workbooks.Open(FileName:=MyFullFilePath, ReadOnly:=True) Application.DisplayAlerts = True When my target workbook contains links, I still get a warning pop-up asking if links should be updated, even with displayalerts = False. So I changed the open statement to: Application.DisplayAlerts = False Set twb = Workbooks.Open(FileName:=MyFullFilePath, UpdateLinks:=False, ReadOnly:=True) Application.DisplayAlerts = True It bypasses that initial warning, but then pops up the filesearch wizard/userform with the title bar "Update Values: HsTbar.xla" which I'm guessing is an add-in used by the person providing me the data sheet. This is not what I'd expect, since I set UpdateLinks = False I'm just trying to copy the data out of one sheet (format and values, not formulas or links) but I need to do so in a way that is fully automated (no user prompts) so that my code can run overnight. How can I completely bypass the links alerts and just open the file via VBA? Thank you, Keith |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
XL03: Avoid "workbook contains links" msg when opening
Ron,
I've used UpdateLinks:=False. I'm guessing False equates to 0. Is there some reason I should use 0 instead of False? Barb Reinhardt "Ron de Bruin" wrote: Correct, it is wrong in the help of a lot of Exel versions, i believe it is correct in the good old Excel 2000 If you have links in your workbook this (UpdateLinks:=0) will avoid the message do you want to update the links or not "0 Doesn't update any references" Use 3 instead of 0 if you want to update the links. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "ryguy7272" wrote in message ... If you have links in your workbook this (UpdateLinks:=0) will avoid the message ...from a Ron de Bruin post... -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ker_01" wrote: 'Open workbook Application.DisplayAlerts = False Set twb = Workbooks.Open(FileName:=MyFullFilePath, ReadOnly:=True) Application.DisplayAlerts = True When my target workbook contains links, I still get a warning pop-up asking if links should be updated, even with displayalerts = False. So I changed the open statement to: Application.DisplayAlerts = False Set twb = Workbooks.Open(FileName:=MyFullFilePath, UpdateLinks:=False, ReadOnly:=True) Application.DisplayAlerts = True It bypasses that initial warning, but then pops up the filesearch wizard/userform with the title bar "Update Values: HsTbar.xla" which I'm guessing is an add-in used by the person providing me the data sheet. This is not what I'd expect, since I set UpdateLinks = False I'm just trying to copy the data out of one sheet (format and values, not formulas or links) but I need to do so in a way that is fully automated (no user prompts) so that my code can run overnight. How can I completely bypass the links alerts and just open the file via VBA? Thank you, Keith . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
XL03: Avoid "workbook contains links" msg when opening
And if they are equivalent, is there another reason why the second sample
code in my initial post would not eliminate the secondary Excel message? If Excel understood that I didn't want to update links, I would expect it to just open the worksheet without any additional prompts, but it is acting like I said that I did want to update links. Thanks to all, Keith "Barb Reinhardt" wrote: Ron, I've used UpdateLinks:=False. I'm guessing False equates to 0. Is there some reason I should use 0 instead of False? Barb Reinhardt "Ron de Bruin" wrote: Correct, it is wrong in the help of a lot of Exel versions, i believe it is correct in the good old Excel 2000 If you have links in your workbook this (UpdateLinks:=0) will avoid the message do you want to update the links or not "0 Doesn't update any references" Use 3 instead of 0 if you want to update the links. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "ryguy7272" wrote in message ... If you have links in your workbook this (UpdateLinks:=0) will avoid the message ...from a Ron de Bruin post... -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ker_01" wrote: 'Open workbook Application.DisplayAlerts = False Set twb = Workbooks.Open(FileName:=MyFullFilePath, ReadOnly:=True) Application.DisplayAlerts = True When my target workbook contains links, I still get a warning pop-up asking if links should be updated, even with displayalerts = False. So I changed the open statement to: Application.DisplayAlerts = False Set twb = Workbooks.Open(FileName:=MyFullFilePath, UpdateLinks:=False, ReadOnly:=True) Application.DisplayAlerts = True It bypasses that initial warning, but then pops up the filesearch wizard/userform with the title bar "Update Values: HsTbar.xla" which I'm guessing is an add-in used by the person providing me the data sheet. This is not what I'd expect, since I set UpdateLinks = False I'm just trying to copy the data out of one sheet (format and values, not formulas or links) but I need to do so in a way that is fully automated (no user prompts) so that my code can run overnight. How can I completely bypass the links alerts and just open the file via VBA? Thank you, Keith . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
XL03: Avoid "workbook contains links" msg when opening
Hi Barb
I always use the numbers (never test it with False) Excel 2003 and 2007 Help 1 User specifies how links will be updated 2 Never update links for this workbook on opening 3 Always update links for this workbook on opening If you use 2 or 3 both will update the links so the help is not correct Excel 2000 Help 0 Doesn't update any references 1 Updates external references but not remote references 2 Updates remote references but not external references 3 Updates both remote and external references I think 3 and 0 are the best options if you want to update or not and is working in all Excel versions as far as I know. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Barb Reinhardt" wrote in message ... Ron, I've used UpdateLinks:=False. I'm guessing False equates to 0. Is there some reason I should use 0 instead of False? Barb Reinhardt "Ron de Bruin" wrote: Correct, it is wrong in the help of a lot of Exel versions, i believe it is correct in the good old Excel 2000 If you have links in your workbook this (UpdateLinks:=0) will avoid the message do you want to update the links or not "0 Doesn't update any references" Use 3 instead of 0 if you want to update the links. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "ryguy7272" wrote in message ... If you have links in your workbook this (UpdateLinks:=0) will avoid the message ...from a Ron de Bruin post... -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ker_01" wrote: 'Open workbook Application.DisplayAlerts = False Set twb = Workbooks.Open(FileName:=MyFullFilePath, ReadOnly:=True) Application.DisplayAlerts = True When my target workbook contains links, I still get a warning pop-up asking if links should be updated, even with displayalerts = False. So I changed the open statement to: Application.DisplayAlerts = False Set twb = Workbooks.Open(FileName:=MyFullFilePath, UpdateLinks:=False, ReadOnly:=True) Application.DisplayAlerts = True It bypasses that initial warning, but then pops up the filesearch wizard/userform with the title bar "Update Values: HsTbar.xla" which I'm guessing is an add-in used by the person providing me the data sheet. This is not what I'd expect, since I set UpdateLinks = False I'm just trying to copy the data out of one sheet (format and values, not formulas or links) but I need to do so in a way that is fully automated (no user prompts) so that my code can run overnight. How can I completely bypass the links alerts and just open the file via VBA? Thank you, Keith . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
XL03: Avoid "workbook contains links" msg when opening
Thank you all for your continued assistance.
I replaced the False with 0, and still have the same symptom, which makes me think that this is not actually a links issue that I'm seeing. The formulas in the source workbook seem to refer to an XLA which I don't have (and am unlikely to get), based on the pop-up: It bypasses that initial warning, but then pops up the filesearch wizard/userform with the title bar "Update Values: HsTbar.xla" An example of the formula is: ='C:\Hyperion\SmartView\Bin\HsTbar.xla'!HsGetValue ("CorpHFM","Scenario#"&$C$1&";Year#"&E$12&";Period #"&E$13&";View#"&$C$2&";Entity#"&$A15&";Value#"&$C $4&";Account#"&$B15&";ICP#"&$C$3&";Custom1#"&$C$5& ";Custom2#"&$C$6&";Custom3#"&$C$7&";Custom4#"&$C$8 &"")*2204.6 I think what confused me was that when I open the file manually (without VBA) and decline to update links, I do not get this secondary pop-up asking for the file location of HsTbar.xla. So, my best deduction is that there is different behavior between UpdateLinks:=0 vs what happens when the file is manually opened and the user declines to update links. So I guess my question should be rephrased; what additional parameters or settings do I need to use to open a file with VBA without triggering any sheet updates at all, so that it won't ask for the xla location? Many thanks, Keith "Ron de Bruin" wrote: Hi Barb I always use the numbers (never test it with False) Excel 2003 and 2007 Help 1 User specifies how links will be updated 2 Never update links for this workbook on opening 3 Always update links for this workbook on opening If you use 2 or 3 both will update the links so the help is not correct Excel 2000 Help 0 Doesn't update any references 1 Updates external references but not remote references 2 Updates remote references but not external references 3 Updates both remote and external references I think 3 and 0 are the best options if you want to update or not and is working in all Excel versions as far as I know. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Barb Reinhardt" wrote in message ... Ron, I've used UpdateLinks:=False. I'm guessing False equates to 0. Is there some reason I should use 0 instead of False? Barb Reinhardt "Ron de Bruin" wrote: Correct, it is wrong in the help of a lot of Exel versions, i believe it is correct in the good old Excel 2000 If you have links in your workbook this (UpdateLinks:=0) will avoid the message do you want to update the links or not "0 Doesn't update any references" Use 3 instead of 0 if you want to update the links. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "ryguy7272" wrote in message ... If you have links in your workbook this (UpdateLinks:=0) will avoid the message ...from a Ron de Bruin post... -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "ker_01" wrote: 'Open workbook Application.DisplayAlerts = False Set twb = Workbooks.Open(FileName:=MyFullFilePath, ReadOnly:=True) Application.DisplayAlerts = True When my target workbook contains links, I still get a warning pop-up asking if links should be updated, even with displayalerts = False. So I changed the open statement to: Application.DisplayAlerts = False Set twb = Workbooks.Open(FileName:=MyFullFilePath, UpdateLinks:=False, ReadOnly:=True) Application.DisplayAlerts = True It bypasses that initial warning, but then pops up the filesearch wizard/userform with the title bar "Update Values: HsTbar.xla" which I'm guessing is an add-in used by the person providing me the data sheet. This is not what I'd expect, since I set UpdateLinks = False I'm just trying to copy the data out of one sheet (format and values, not formulas or links) but I need to do so in a way that is fully automated (no user prompts) so that my code can run overnight. How can I completely bypass the links alerts and just open the file via VBA? Thank you, Keith . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro issue in XL03 "Compatability Mode" | Excel Programming | |||
| Macro issue in XL03 "Compatability Mode" | Excel Programming | |||
| Avoid "update Links" promt in e-mailed pivot tables? | Excel Discussion (Misc queries) | |||
| How to avoid "update links" startup prompt? | Excel Discussion (Misc queries) | |||
| Avoid "...contains one or more links that cannot be updated" message. | Excel Programming | |||
Linear Mode
