VBScript help

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

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

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

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.



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

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


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



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

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top