Output a string to the console?

[Toby Erkson]

I'm looking for a simple way to output to the console (command line
window?). Can this be done in VBA?

The reason: I have a scheduler that can monitor the task it has executed
and capture any console output for later inspection. I want this as I would
like to send text to the console when an error occurs to help me figure
things out. I don't want a log file.

If you're familiar with VBScripting then what I'm looking for is something
like this --> wscript.echo "Hello world!".

TIA!

 

[Per Jessen]

HI

Maybe this is what you need.
You can output to the Immediate window, which you can see in the VBA editor.

Debug.Print "Hello world"

Regards,
Per

 

[Just Another Yahoo!]

Nope, since the workbook is executed automatically by the scheduler the
Immediate Window won't do me any good. The workbook is only seen by a human
when there's an error and the errors are generally due to the input file and
not the workbook.

 

[Just Another Yahoo!]

Hmm, after re-reading this, just ignore that last sentence.

 

[Toby Erkson]

Well, at least I'm consistent with asking the questions that can't be
answered, LOL

 

[Steve Yandl]

Toby,

Have you looked at creating an instance of the "WScript.Shell" object and
using it's 'exec' method as an alternate means of capturing output from the
task and skip the console window?

If you do use a console type window, can you have the print output and also
have the normal prompt that appears when you launch cmd.exe or does that
prompt create problems for output retrieval?


Steve Yandl

 

[Steve Yandl]

Toby,

As I suggested in my earlier post, I suspect there is a more efficient way
to harvest output from the tasks your sheduler causes to execute. However,
if you do want text to a console window, here is an example. If you launch
this sub with some text as the argument, you get a command interpreter
console window (cmd.exe window) containing the text sent to the sub. If the
text argument is absent, you get the console window with "Hello World"

'----------------------------------------------
Sub StringInConsole(Optional strMsg As String = "")

If Not Len(strMsg) > 0 Then
strMsg = "Hello World"
End If

strMsg = "echo " & strMsg & "&echo off"

Shell "cmd.exe /k" & strMsg, vbNormalFocus

End Sub
'----------------------------------------------

That you could launch with a sub like
'---------------------------------------------
Sub MessageMe()
StringInConsole("This is a test of this fine VBA routine")
End Sub
'---------------------------------------------
or
'---------------------------------------------
Sub MessageMe()
StringInConsole
End Sub
'---------------------------------------------

Steve Yandl

 

[Just Another Yahoo!]

Wow, cool Steve!

That works well for a single line of output but I would need the ability to
output as the program moves along, just like a log file. As is, this opens
a new command window every time it's called. Maybe I could make it into an
object... I'll play some more with it. Thanks for the assistance

 

[Steve Yandl]

Toby,

Take a look at the line from the routine
strMsg = "echo " & strMsg & "&echo off"
Notice how I placed the & characters when I sent echo commands. The &
character when fed to cmd.exe works much like vbCrLf when you're writing VBA
or VBS. In the line above, the first and second & are used to concatenate
my string but the one inside the quotes and right before "echo off" causes
the "echo off" to be sent as a new line (which turns off the echo so you
don't have the command prompt waiting after your final line of text). Just
build up the variable strMsg so that you keep adding the lines of output but
be sure to add in an & between each line. Then at the end, use the line I
placed to append the text string with the echo and 'echo off'.

Steve Yandl

 

[Just Another Yahoo!]

Well, I was hoping to not have to build a string. The goal is to simply
open the command window and spit out text as the program plods along instead
of waiting until it finishes (for better or for worse) to output a built-up
string because that would require error checking code, which is something
I'd like to avoid. Remember, the scheduler app. I use captures the command
window output as the executing program runs so if the executing program
fails for any reason -- be it gracefully or catastrophically -- the [saved]
command window text would show me the last point of noted execution.

Background: The person I replaced wrote many scripts and VBA code but w/o
good error trapping. Right now it's faster for me to put in these sort of
"echo" statements at key points in the VBA code than set up error
trapping -- that would be a long task with too small of a return. This
method would give me a better idea where the error occurred instead of just
"somewhere in this workbook" and it would allow me to insert break points in
appropriate locations. As I come across the rare error I add my error
checking/trapping as necessary.