VBScript help

  • Thread starter Thread starter WhytheQ
  • Start date Start date
W

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

Dave Peterson said:
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.
Click to expand...



--
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.)
 
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


Clif McIrvin said:
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.)
Click to expand...



--
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.)
 
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.
 
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.
 
Back
Top