Help with VBScript

RunTime91

Active Member
Joined
Aug 30, 2012
Messages
262
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 -
Code:
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.
Code:
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
Code:
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

zweifuss

New Member
Joined
Dec 6, 2020
Messages
31
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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:
VBA Code:
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

Active Member
Joined
Aug 30, 2012
Messages
262
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
Code:
.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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,194
Messages
5,768,778
Members
425,494
Latest member
Ragamacam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top