• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 620
  • Last Modified:

VBS FILE LINK UPDATING.

Hello,

I have a vbs file that is scheduled to open up an excel spreadsheet as follows:

Dim objShell:Set objShell=CreateObject("Wscript.Shell")
sRun="cmd /k Start excel " & chr(34) & "M:\Bloomberg\NBF LDN\NBF-BBG-DOWNLOADS" & chr(34)
objShell.Run sRun
wscript.sleep 5000
objShell.AppActivate "Window title" 'of the program's popup
objShell.SendKeys "~" 'send Enter key
Set objShell=Nothing

What I would like is to have an addition to the code that allows for the spreadsheet to be opened up but without establishing any links.

Regards,

f19l
0
f19l
Asked:
f19l
  • 2
1 Solution
 
EDDYKTCommented:
dim XlApp

Set XlApp = CreateObject("Excel.Application")
XlApp.Visible = True
XlApp.Interactive = False
XlApp.Application.DisplayAlerts = False
XlApp.Calculation = -4135   'xlManual
XlApp.CalculateBeforeSave = True
XlApp.Workbooks.Open FileName:=chr(34) & "M:\Bloomberg\NBF LDN\NBF-BBG-DOWNLOADS" & chr(34), UpdateLinks:=0
...
0
 
f19lAuthor Commented:
I have used the exact same coding as suggested but it does not work. There was is a problem at line 9, character 31. Ideally, though I would like to use my existing code.
0
 
RobSampsonCommented:
Hi, in VBScript, you cannot use the value constants like FileName:= or UpdateLinks:= because VBScript does not know what those mean, and it tries to interpret those before getting to Excel.
If you have a look here:
http://msdn2.microsoft.com/en-us/library/Aa195811(office.11).aspx
in the Open Method As it Applies to the WorkBooks Object, you see that the syntax is:
expression.Open(FileName, UpdateLinks, ReadOnly......etc, etc

Therefore, as long as you stick to that exact paramater (leaving blank spaces between commas if required for optional parameters) you can pass the required values.

So, just use:
XlApp.Workbooks.Open chr(34) & "M:\Bloomberg\NBF LDN\NBF-BBG-DOWNLOADS" & chr(34), False, False
To open the FileName, set UpdateLinks to False, and ReadOnly to False.

If you have a situation where an Excel macro gives you an Excel constant like "xlAutoOpen" or something, you need to MsgBox xlAutoOpen within an Excel module, so that you know the numeric value of that.  Then, in VBScript, add a constant (assume xlAutoOpen returned 1):
Const xlAutoOpen = 1

and then you can use that constant throughout the code, like
ActiveWorkbook.RunAutoMacros xlAutoOpen

Just remember, you can never use the parameter marker that ends with :=

Regards,

Rob.
0
 
f19lAuthor Commented:
Rob, after using your suggestions it works now thanks.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now