Getting output from excel back to vbscript..

VB Scripts >> Getting output from excel back to vbscript.. I have a vb basic script that automates an excel spreadsheet, it opens the
spreadsheet and then runs an macro. This macro takes a long time to run and
I want to be able to output status information to the vbscript that lauched
the command.

Is there anyway to do this? is there any kind of stdout stream that my macro
can write to that I can read in my script? I'm looking for a clean and
"correct" solution. I don't want to write to a file in the macro and then
read the file in my script.

Basically when my user runs the script the screen should read something like
this:

c:\cscript thescript.vbs
The script has been started!
Opening excel...
Starting the excel macro... <-wscript.echo in script
10 rows processed <- This would come from the macro, somehow
100 rows processed <- from the macro
Macro complete. <-wscript.echo in script
Script complete.
c:\

Is this possible?

Visual Studio311

VB Scripts >> Getting output from excel back to vbscript.. J S wrote:

> I have a vb basic script that automates an excel spreadsheet, it opens the
> spreadsheet and then runs an macro. This macro takes a long time to run
and
> I want to be able to output status information to the vbscript that
lauched
> the command.
>
> Is there anyway to do this? is there any kind of stdout stream that my
macro
> can write to that I can read in my script? I'm looking for a clean and
> "correct" solution. I don't want to write to a file in the macro and then
> read the file in my script.
>
> Basically when my user runs the script the screen should read something
like
> this:
>
> c:\cscript thescript.vbs
> The script has been started!
> Opening excel...
> Starting the excel macro... <-wscript.echo in script
> 10 rows processed <- This would come from the macro, somehow
> 100 rows processed <- from the macro
> Macro complete. <-wscript.echo in script
> Script complete.
> c:\
>
>
> Is this possible?

Hi,

I wouldn't run a macro, but use the Excel object to manipulate the
spreadsheet in the VBScript program directly. Then, you have control of what
happens and can output messages when you like. I have a sample VBScript
program to read a Microsoft Excel spreadsheet linked on this page:

http://www.rlmueller.net/Read%20from%20Excel.htm

You don't say what your macro does, but it seems like it could be coded in
VBScript instead. If you have to write to the spreadsheet, the example
program linked below might help:

http://www.rlmueller.net/Write%20to%20Excel.htm

--
Richard
Microsoft MVP Scripting and ADSI
HilltopLab web site - http://www.rlmueller.net
--

VB Scripts >> Getting output from excel back to vbscript.. Thats a good point and that would solve my problem if I could port all the
macro code to vbscript. Unfortunately, I can not port the VBA code to
vbscript (For various non-technical reasons). I was wondering do you have
any other ideas? I was hoping for some kind of stdout or stderr streams that
I could access from both the vbscript and the excel spreadsheet. If one
doesn't exist by default is there any way I could create a buffer or
textstream object in vbscript that I could then access in excel vba code?

"Richard Mueller [MVP]" <EMail@HideDomain.com> wrote in
message news:u$EMail@HideDomain.com...
> J S wrote:
>
> > I have a vb basic script that automates an excel spreadsheet, it opens
the
> > spreadsheet and then runs an macro. This macro takes a long time to run
> and
> > I want to be able to output status information to the vbscript that
> lauched
> > the command.
> >
> > Is there anyway to do this? is there any kind of stdout stream that my
> macro
> > can write to that I can read in my script? I'm looking for a clean and
> > "correct" solution. I don't want to write to a file in the macro and
then
> > read the file in my script.
> >
> > Basically when my user runs the script the screen should read something
> like
> > this:
> >
> > c:\cscript thescript.vbs
> > The script has been started!
> > Opening excel...
> > Starting the excel macro... <-wscript.echo in script
> > 10 rows processed <- This would come from the macro, somehow
> > 100 rows processed <- from the macro
> > Macro complete. <-wscript.echo in script
> > Script complete.
> > c:\
> >
> >
> > Is this possible?
>
> Hi,
>
> I wouldn't run a macro, but use the Excel object to manipulate the
> spreadsheet in the VBScript program directly. Then, you have control of
what
> happens and can output messages when you like. I have a sample VBScript
> program to read a Microsoft Excel spreadsheet linked on this page:
>
> http://www.rlmueller.net/Read%20from%20Excel.htm
>
> You don't say what your macro does, but it seems like it could be coded in
> VBScript instead. If you have to write to the spreadsheet, the example
> program linked below might help:
>
> http://www.rlmueller.net/Write%20to%20Excel.htm
>
> --
> Richard
> Microsoft MVP Scripting and ADSI
> HilltopLab web site - http://www.rlmueller.net
> --
>
>

VB Scripts >> Getting output from excel back to vbscript..
"J S" <EMail@HideDomain.com> wrote in message news:EMail@HideDomain.com...
: Thats a good point and that would solve my problem if I could port all the
: macro code to vbscript. Unfortunately, I can not port the VBA code to
: vbscript (For various non-technical reasons). I was wondering do you have
: any other ideas? I was hoping for some kind of stdout or stderr streams that
: I could access from both the vbscript and the excel spreadsheet. If one
: doesn't exist by default is there any way I could create a buffer or
: textstream object in vbscript that I could then access in excel vba code?
:

This is not an area I do much in, so I don't know if this has pitfalls
(recommend testing on a backup copy)

in excel, add a reference to Microsoft Internet Controls

add this to the macro

Dim ie As InternetExplorer
Set ie = New InternetExplorer
ie.Height = 50
ie.Width = 100
ie.Visible = True

'macro starts
ie.StatusText = "100" 'update messages go here, in a loop, or just palced at various points in the macro

'at end of macro
ie.quit
Set ie = Nothing