K
ker_01
This started in another thread, but that thread got orphaned and I've got a
clearer idea of what is happening now, so reposting in search of additional
help. Thank you all for your continued assistance.
I have a file that I open with UpdateLinks:=0 My goal is to automatically
open this file from a LAN, copy the data (pastespecial/values into my
workbook), then close this book. The intent is to have this fully automated.
However, when opening the file with VBA (see code below) I get a "find file"
type dialog window; it seems to want me to locate an XLA. I'm assuming this
because the formulas in the source workbook seem to refer to an XLA which I
don't
have (and am unlikely to get). The title bar of this dialog window is
"Update Values: HsTbar.xla"
An example of a formula from the sheet
='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
When I open the file *manually* and say "don't update links", I do not get
this dialog box that is looking for the XLA. When I open the file via VBA
with updatelinks:=0, I do get the dialog box (and it therefore interrupts the
automation until a user clicks cancel).
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
Code:
Application.DisplayAlerts = False
Set twb = Workbooks.Open(FileName:=MyFullFilePath, UpdateLinks:=0,
ReadOnly:=True)
Application.DisplayAlerts = True
clearer idea of what is happening now, so reposting in search of additional
help. Thank you all for your continued assistance.
I have a file that I open with UpdateLinks:=0 My goal is to automatically
open this file from a LAN, copy the data (pastespecial/values into my
workbook), then close this book. The intent is to have this fully automated.
However, when opening the file with VBA (see code below) I get a "find file"
type dialog window; it seems to want me to locate an XLA. I'm assuming this
because the formulas in the source workbook seem to refer to an XLA which I
don't
have (and am unlikely to get). The title bar of this dialog window is
"Update Values: HsTbar.xla"
An example of a formula from the sheet
='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
When I open the file *manually* and say "don't update links", I do not get
this dialog box that is looking for the XLA. When I open the file via VBA
with updatelinks:=0, I do get the dialog box (and it therefore interrupts the
automation until a user clicks cancel).
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
Code:
Application.DisplayAlerts = False
Set twb = Workbooks.Open(FileName:=MyFullFilePath, UpdateLinks:=0,
ReadOnly:=True)
Application.DisplayAlerts = True