VBScript help

[WhytheQ]

Hello All,

The following vbs file runs ok:


Set XL=CreateObject("Excel.Application")
XL.Visible=True
XL.Workbooks.Open "\\blahblah\xxx.xlsm"
blUpdateAll = True
blUpdateFormatting = True
XL.Run "'xxx.xlsm'!UpdateAllWorkbooks"
XL.Run "'xxx.xlsm'!SaveJCfolder"
XL.Workbooks("xxx.xlsm").close false
XL.quit
Set XL=nothing

.....problem is that the two variables blUpdateAll & blUpdateFormatting
are used in the programs UpdateAllWorkbooks & SaveJCfolder but that
doesn't seem to be the case. It's like the variables are being stored
but they aren't available to the programs

Any help much appreciated

Jason.

 

[Dave Peterson]

I don't use VBS very often, but it looks like you're creating a new instance of
excel. Then opening xxx.xlsm. So that's the only file/workbook open in that
instance of excel.

Maybe you have to open the other workbooks in that same instance of excel -- or
maybe you want to open xxx.xlsm in the (already) running instance of excel.

 

[Clif McIrvin]

I also use VBS very little ... my observation is that the OP is creating
two variables within the script processor; he needs to pass those values
to the called macros somehow.

Perhaps:

would work? Of course, the two called macros would need to be looking
for parameters.

Clif

I don't use VBS very often, but it looks like you're creating a new
instance of excel. Then opening xxx.xlsm. So that's the only
file/workbook open in that instance of excel.

Maybe you have to open the other workbooks in that same instance of
excel -- or maybe you want to open xxx.xlsm in the (already) running
instance of excel.

--
Clif McIrvin

Change nomail.afraid.org to gmail.com to reply by email.
(nomail.afraid.org has been set up specifically for
use in usenet. Feel free to use it yourself.)

 

[Clif McIrvin]

Looks like word wrap munched my suggestion ... I'll try again:

blUpdateAll = True

blUpdateFormatting = True

XL.Run "'xxx.xlsm'!UpdateAllWorkbooks" blUpdateAll , blUpdateFormatting

XL.Run "'xxx.xlsm'!SaveJCfolder" blUpdateAll , blUpdateFormatting

I also use VBS very little ... my observation is that the OP is
creating two variables within the script processor; he needs to pass
those values to the called macros somehow.

Perhaps:


would work? Of course, the two called macros would need to be looking
for parameters.

Clif





--
Clif McIrvin

Change nomail.afraid.org to gmail.com to reply by email.
(nomail.afraid.org has been set up specifically for
use in usenet. Feel free to use it yourself.)

--
Clif McIrvin

Change nomail.afraid.org to gmail.com to reply by email.
(nomail.afraid.org has been set up specifically for
use in usenet. Feel free to use it yourself.)

 

[WhytheQ]

Thanks for all the help folks.

I just ended up creating an extra little subroutine "Sub
RunRemotely()" in the excel file which gives the specified values to
the two variables - the vbs file now just looks like:

Set XL=CreateObject("Excel.Application")
XL.Visible=True
XL.Workbooks.Open "\\imsfileserve\xxx.xlsm"
XL.Run "'xxx.xlsm'!RunRemotely"
XL.Workbooks("xxx.xlsm").close false
XL.quit
Set XL=nothing


....ONE FURTHER QUESTION:
I'm using these .vbs files, run using Scheduled Tasks, to run various
macros on a server dedicated to churning out Excel reports - what
alternative methods would you use? (bearing in mind the routines can
take upwards of 20minutes to run using lots of aPCs resources while
running ?

All help appreciated
Jason.

 

[Dave Peterson]

I don't have an alternate suggestion. But there are lots of scheduling programs
available (search google) if you're not happy with what's built into windows.