Help with VBScript

[RunTime91]

Greetings All

I'm not new to VBA - But I am very new to VBScript - So please forgive if I'm not asking for help the right way

SetUp:
I'm simply trying to automate a run reports task through MS Task Scheduler
I've created both .vbs and .bat files - in fact I've created several.

Here are a .bat & .vbs that work -

Run.bat
CScript Script2.Vbs "C:\Users\FS115436\Documents\Test"

Script2.vbs
StrPath = "C:\Users\FS115436\Documents\Test\Excel VBS TestBook1.xlsm"
Set xlApp=CreateObject("Excel.Application")
Set xlWb=xlApp.WorkBooks.Open(StrPath)

xlApp.Workbooks.Open(StrPath)
xlApp.Visible = True
xlApp.Run "VBSTest1"

Set xlWb = Nothing
Set xlApp = Nothing

So those work as do a couple others moving between local and shared folders

These, however, I can't get to work for the life of me.

Run.bat
CScript Script3.vbs "N:\Sales Management \Internal\Confidential\BusSysAnalyst\WIP"

Script3.vbs
StrPath = "‪N:\Sales Management \Internal\Confidential\BusSysAnalyst\WIP\Excel Master IW Reports BusSys.xlsm"
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.WorkBooks.Open(StrPath)

xlApp.Workbooks.Open(StrPath)
xlApp.Visible = True
xlApp.Run "AdHocRprts"

Set xlWb = Nothing
Set xlApp = Nothing

I tried moving everything to the Desktop and tried a little bit different code - Still nothing

Run.bat
CScript Script.vbs "C:\Users\FS115436\Desktop"

Script.vbs
Dim args, objExcel
Dim Path As String
Set Path = "‪C:\Users\FS115436\Desktop\Excel Master IW Reports BusSys.xlsm"

Set args = wScript.Arguments
Set objExcel = CreateObject("Excel.Application")

objExcel.Workbooks.Open(Path)
objExcel.Visible = True

objExcel.Run "AdhocRprts"
objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close(0)
objExcel.Quit

I'm venturing down a dark road so any light you can shine on this would be greatly appreciated

Thank You...

Rt91

 

[zweifuss]

Some questions:
1. Can the user, specified in the job, access C:\Users\FS115436\Desktop? By default, only FS115436 can access.
2. Has the user, specified in the job, opened Excel at least once?
3. Are macros enabled for the user?
4. Is "‪N:\Sales Management \Internal\Confidential\BusSysAnalyst\WIP\" in the list of macro safe destinations of the user and are networked paths enabled?
5. Is "Run whether user is logged on or not" turned on? Might be problematic, because the environment is different then.

Some remarks:

Set xlWb = xlApp.WorkBooks.Open(StrPath) ' Seems to be superflouous.
objExcel.Visible = True            ' True only for debugging. Set to False when using in a job.
objExcel.DisplayAlerts = True      ' Add this. True only for debugging. Alerts may hang the script when unattended.
objExcel.ActiveWorkbook.Close      ' Already saved, no argument needed. Also, no brackets for argument, when not using call.

 

[RunTime91]

zweifuss - Thank You so much for chiming in

Being new to Script I'm not sure how to frame the issues I'm experiencing...

Let me go down your list of questions and provide responses to your code samples

Questions:
1. Yes - The user can access the desktop and and in all instances only the user will need to access that folder
- If this code is shared with other users they will need to copy the target file(s) onto their desktop and mod the code to reflect their username
- If the target folder is a shared the only other possible users will access to that folder as well.
2 & 3 Yes & Yes
4. Yes & Yes
5. No - This setting requires the user to be logged in to run

Responses:
StrPath - I'm using StrPath variable because the only other code I found 'seemed' to want to Loop through the files (of which there can be 100's) and I need to target only 3 specific files
I'm not sure how to eliminate that variable.
.Visible - That was a hold over from my original tests where the macro I was running added values to the target workbook.
So I opened it to make sure the macro was run.
.DesplayAlerts - I use it in the fashion you described - True for TShooting and False when everything works.
*Comment on this - When the .bat file opens the DOS window I am able to see there is an error - "Not recognizing the file name..."
and even though I have .DisplayAlerts set to True - no error window appears - is there a way to pause the DOS prompts
I know if I click in the DOS window it will pause it but only for the first prompt - the second prompt (where the error is appearing) I can't seem to pause.
.ActiveWorkbook.Close - Got it - & - Changed it - Thanks

With all that said - I did try moving the target file to a folder on our shared drive and wouldn't you know it - it ran perfectly.

So this code now works - But I would like to eventually have everything housed in my My Documents Folder
which still errors like the Desktop.

Since the syntax and the names are all correct (seemingly, of course) I am just bewildered - obviously I'm missing something

.bat
CScript Script.vbs "C:\Users\FS115436\Desktop"

.vbs
StrPath = "N:\Sales Management\Internal\Confidential\BusSysAnalyst\WIP\Excel Master IW Reports BusSys.xlsm"
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.WorkBooks.Open(StrPath)
xlApp.DisplayAlerts = True
xlApp.Workbooks.Open(StrPath)
xlApp.Run "AdHocRprts"

Set xlWb = Nothing
Set xlApp = Nothing