An executable file to run a VB script?

[Guest]

Is it possible to construct an executable file that will run from Windows XP
that will launch a VB script in Excel?

I would like to be able to assign this .exe program to Windows Task
Scheduler in order to send data to other programs at regular intervals.

 

[Bob Phillips]

Why not just create a VBScript (.vbs, not VBA in Excel) that creates an
Excel instance and does some stuff, or a workbook with a workbook open event
that does the stuff and is started regularly via scheduler.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Bob,

Thanks for that - but could you give me an example of the script that would
do this as I have no idea how to start a .vbs script that creates an Excel
instance?

 

[Guest]

Bob,

I've found an example of an vbs script that would run a macro and have
adapted it to my workbook/macro on:

http://krgreenlee.blogspot.com/2006/04/excel-running-excel-on-windows-task.html

But I'm not clear how to use a cscript.exe program rather than a wscript.exe
as referred to below in this blog - what's the diference and how do I write
these scripts to run my VBS Excel controller?:

"3) Launch the VBS Excel Controller using cscript.exe NOT wscript.exe. So
let’s say you named the script RunExcel.vbs, then you would execute it using
‘cscript.exe RunExcel.vbs’. (If you make 'YourWorkbook' and 'YourMacro' input
parameters then the RunExcel.vbs script can run any macro in any workbook
because you can just pass that information in on the command-line.)"

 

[Steve Yandl]

Both wscript.exe and cscript.exe are typically located in the
C:\Windows\System32 folder. CScript.exe is used to run vbs files in console
mode and wscript.exe will run vbs files with the windows user interface that
Windows users are more familiar with. Many vbs files will behave exactly
the same whether you choose wscript.exe or cscript.exe. The differences are
largely in the way output is presented to users and how user input is
obtained. Most system administrators who are wanting a script to be
launched by scheduler will opt for cscript because they're not expecting the
computer user to be involved in the actual functioning of the script when it
runs.


Steve

 

[Guest]

Steve,

Thanks - I've tried running the script but get various error messages that
the (Excel) file is 'Read only' and is being modified by 'another user' - in
fact it is on my desktop and closed. When I try to open it, it does indeed
open as 'Read only' and I need to log off from the network in order to access
it again. My vbs tries to run but doesn't - could it be network (Novell)
security poreventing me from running it on my company network?

It's very frustrating as I'm 'almost there' - can you suggest a way forward?

many thanks

Tony

 

[Steve Yandl]

If you have been experimenting with the script, press Ctrl-Alt-Del and
review the process list to locate instances of Excel.exe that are running
hidden. If so, they might be causing the message and you can try ending
each Excel process rather than logging off the network and back on. I
suspect that you are dealing with network security issues though. As I
understand your scenario, you're invoking a macro that belongs to an xls
file saved on your PC. Is that macro operating on files elsewhere on your
network?

Steve

 

[Guest]

Steve,

Many thanks- I'll try the CTRL-ALT-DEL method as you suggest.

However, yes, the macro is operating on programs stored in other locations
on my
network and I think 'll probably have to abandon the idea of using task
scheduler as the IT people won't cooperate with any automation which might
breach security, and there's no other way round I suppose, is there?

 

[Steve Yandl]

I suspect there is a solution but it may require a different approach.

I went back to your original question to better understand the specifics of
what you want to do. What capability does vbScript offer that you couldn't
do directly from VBA and what does Excel offer that you couldn't do entirely
with a vbs script? You can access the scripting runtimes from within VBA
and you can also manipulate the Excel application and specific Excel files
from vbScript so it's fairly rare to need to operate both a vbs file and a
VBA routine in an Excel file in tandem. The only time I've found it the
best approach is when I wanted to take advantage of the arguments property
of the script host.

If you're able to post some or all of the VBA you're starting with it might
help.


Steve

 

[Guest]

Steve,

My intention is to create an .exe file that I can assign to to the Windows
Task Scheduler but I think I'll have to give up on it as network security
will probably log me off after 10 minutes or so - the script I've written is
below.

However, if I could access the scripting runtimes from within VBA as you
say, how would I create an .exe file to assign this script to run through
Task Scheduler?

Many thanks,

Tony

Application.ScreenUpdating = False

Sheets("NEWTSordercopy").Select
Windows("NEWTS online pro forma example.xls").Activate
Application.Goto Reference:="R1C1"
Range("A130").Select
Selection.Copy
Windows("Vantage Interactive Advanced tm.xls").Activate
Application.Goto Reference:="R1C1"
ActiveSheet.Paste


Sheets("NEWTSordercopy").Select
Range("B11").Select
Selection.NumberFormat = "m/d/yyyy"
Selection.Copy
Range("B12").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False



'add in training and gb dates

'input trng dates

Application.Run "'Vantage Interactive Advanced tm.xls'!inputmidnewttrng"


'input gb dates

Sheets("NEWTSordercopy").Select
Range("B11").Select
Selection.Copy
Sheets("Datainput").Select
Range("L5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("L5:L17"), Type:=xlFillDefault
Range("L5:L17").Select
Range("L5:L9").Select
Selection.Copy
Range("S2").Select
ActiveSheet.Paste
Range("L12:L16").Select
Application.CutCopyMode = False
Selection.Copy
Range("S7").Select
ActiveSheet.Paste
Range("L1:L17").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("M8").Select
Selection.Copy
Range("L1:L18").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("M23").Select

'adjust

Range("S3").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Range("S3").Select
Selection.AutoFill Destination:=Range("S3:S6"), Type:=xlFillDefault
Range("S3:S6").Select
Range("S7").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+3"
Range("S7").Select
Selection.AutoFill Destination:=Range("S7:S11"), Type:=xlFillDefault
Range("S7:S11").Select
Range("S8").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Range("S8").Select
Selection.AutoFill Destination:=Range("S8:S11"), Type:=xlFillDefault
Range("S8:S11").Select





'paste first Sat working date to U1 on Datainput:

Application.ScreenUpdating = False

Sheets("Datainput").Select


Range("U2").Select
Selection.Copy
Range("U1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False





'SCORE and input SMI to newtsrderform BY EVALUATING NEWTSFORM SCORES:


Sheets("NEWTSform").Select

Select Case Range("H19").Value

Case 4
Call inputBBSMIDtonewtsorder

End Select

Select Case Range("H19").Value

Case 3
Call inputBBSMIEtonewtsorder

End Select

'etc. ...

' ...


'check scoring of GB start time and cycle hours and input to Grad Bay:



Application.Run "'Vantage Interactive Advanced
tm.xls'!Calloptionscore25or36GBintervals"

Application.Run "'Vantage Interactive Advanced tm.xls'!cyclehrsscoreandrun"








'now return to NEWTSsteps

DoEvents



Application.Run "'Vantage Interactive Advanced
tm.xls'!SCOREANDRUNSTAFFGROUPANDMN"

Application.Run "'Vantage Interactive Advanced tm.xls'!backtoNEWTSsteps"

 

[Steve Yandl]

Tony,

Have you tried

excel.exe "C:\Test\myBook.xls"

???
You might have to supply the full path for excel.exe to use the command line
for task scheduler but I think the above will work. Personally, I think I'd
put the VBA in an empty workbook that I'd save as an Excel template and have
the data changes made in workbooks held in known locations.


Steve

 

[Guest]

Steve,

Great idea - thank you for the advice - sounds so simple I'll give it a
try!!!

But how do I initiate the macro within it?

Would 'Test' in the path given below be the macro name within the 'blank'
workbook 'myBook.xls' containing the script) I included in my previous
reply? And would this workbook ideally be copied to the same location as the
real-time daabase .exe file on which it operates?

If so I'll give it a try on my laptop at home and, although the data export
part won't run, for obvious reasons, I can at least check whether it will run
under task scheduler.

Best wishes,

Tony

 

[Steve Yandl]

Tony,

You could create an auto_open subroutine that would get the sequence started
from your workbook.

Since this is an Excel programming group, I suggested the possibility of
running scripts from within VBA. However, after looking a bit more at the
VBA you shared, I think I'd do everything from a single vbs file and just
translate what you currently have in VBA to vbScript. It may be a bit time
consuming if macros in the other workbooks being worked on are extensive but
you avoid a host of security roadblocks and will probably have a much faster
process. A script can easily open multiple workbooks, move data between
them and generally duplicate the actions of any functions or subroutines in
those workbooks.


Steve

 

[Guest]

Steve,

Thanks again. Not sure I have the skills to do this - but I'll research the
methods and give it a try - can you suggest any links to help me create a vbs
file for the script I want to run? - also would I need to 'auto-enable'
macros in this script or would it circumvent this Excel dialogue box?

 

[Steve Yandl]

You can't auto-enable macros from a script or from a separate VBA routine.
That's one of the main advantages of translating all the VBA to a script.
The script (if vbs files are permitted to run) will cause the same actions
but won't trigger multiple macro warnings.

Steve