Posted on 2007-07-26
Last Modified: 2011-04-14

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.


Question by:f19l
    LVL 26

    Expert Comment

    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

    Author Comment

    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.
    LVL 65

    Accepted Solution

    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:
    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 :=



    Author Comment

    Rob, after using your suggestions it works now thanks.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
    Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now