Macros & Parameters, etc.
Andrew Wax
Pardon the following question if it has been posted/answered before - I am
kind of new to the Excel programming type stuff....
I have created a macro by recording the macro - the basic functionality is:
- open an ASCII file (delimited by ,)
- graph the datapoints (scatter XY) graph
- output the graph to the printer
Questions:
- I will be trying to execute this from Perl so I would like to be able to
execute this macro from outside of excel - basically passing a command line
parameter (the macro name) have it do it's thing and pass control back to my
perl program. Is this possible?
- Can I can I pass the file name to the macro (so I can use the same
macro for different files)? extending this could I pass the parameters for
the chart title, etc. also?
Any suggestions would be appreciated.
Andrew
--
Rob Bovey
Normally you'd need to automate Excel to perform this kind of thing, but
from your description it sounds as if you might get away with a
self-executing and terminating VBA program in an Excel workbook (or add-in).
The code in this workbook would have the following basic structure:
Sub Auto_Open()
Application.OnTime Now(), ThisWorkbook.Name & "!MyMacro"
End Sub
Sub MyMacro()
'''
''' Add all your code here.
'''
MsgBox "Hello from MyMacro!"
''' Prevent Excel from prompting to save the workbook.
ThisWorkbook.Saved = True
''' Close Excel.
Application.Quit
End Sub
The Auto_Open macro is a specially named macro that Excel recognizes and
runs automatically on startup. You can use the Auto_Open macro to bootstrap
your macro.
I've shown the Auto_Open procedure using the Application.OnTime method
to run your macro. This tells Excel to run your macro immediately after
Auto_Open has completed execution. Giving Auto_Open a chance to complete
clears memory of all Excel initialization-related code and data and tends to
make things more stable, as opposed to calling MyProc directly.
Let's say you've added to the code above to a workbook saved as
E:\MyBook.xls. You can execute this program from a batch file by simply
typing:
C:\<Excel Path Here>\Excel.exe E:\MyBook.xls
so this is what you should load into your shell script.
--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/
* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
"Andrew Wax" <w...@att.net> wrote in message
news:WmsY7.209347$WW.12...@bgtnsc05-news.ops.worldnet.att.net...
Harlan Grove
>Pardon the following question if it has been posted/answered before - I am
>kind of new to the Excel programming type stuff....
>
>I have created a macro by recording the macro - the basic functionality is:
> - open an ASCII file (delimited by ,)
> - graph the datapoints (scatter XY) graph
> - output the graph to the printer
>
>Questions:
>- I will be trying to execute this from Perl so I would like to be able
to
>execute this macro from outside of excel - basically passing a command line
>parameter (the macro name) have it do it's thing and pass control back to
my
>perl program. Is this possible?
To be honest, you might find it easier to translate the macro into Perl and
automate the entire process in Perl. Having Perl run a VBA macro is a very
interesting way of learning just how difficult debugging can be. Better to
use only one scripting engine.
That said, Excel treats almost all command line arguments as filenames. The
only exceptions are a very few switches that begin with /. You could load
the macro name into an environment variable and have the VBA macro get it
using the Environ function.
>- Can I can I pass the file name to the macro (so I can use the same
>macro for different files)? extending this could I pass the parameters for
>the chart title, etc. also?
You mean the ASCII file's name? If so, you could put it on the same command
line as the XLS file containing the macro. Excel would open the ASCII file
automatically, so the XLS file's macro wouldn't have to. Then presumably
there'd be only two open files in Excel, so you could access each file with
the Excel Application object's Workbooks property, which is a collection
class.
Harlan Grove
...
> Normally you'd need to automate Excel to perform this kind of thing,
but
>from your description it sounds as if you might get away with a
>self-executing and terminating VBA program in an Excel workbook (or
add-in).
Begging the question how to run Excel synchronously from within a Perl
script if not via automation. If it doesn't need to be run synchronously,
easy enough to spawn a separate Excel process. But if it does, it's A LOT
easier to ensure proper execution order using automation.
>The code in this workbook would have the following basic structure:
>
> Sub Auto_Open()
> Application.OnTime Now(), ThisWorkbook.Name & "!MyMacro"
> End Sub
>
> Sub MyMacro()
> '''
> ''' Add all your code here.
> '''
> MsgBox "Hello from MyMacro!"
A perverse joke here because the OP is trying to run this effectively as a
background process from Perl, so the last possible thing desired would be
ANY unnecessary user interaction? Especially perverse if the Perl script
were to open Excel in a hidden Window, resulting in a hung Excel process.
> ''' Prevent Excel from prompting to save the workbook.
> ThisWorkbook.Saved = True
> ''' Close Excel.
> Application.Quit
> End Sub
>
> The Auto_Open macro is a specially named macro that Excel recognizes
and
>runs automatically on startup. You can use the Auto_Open macro to bootstrap
>your macro.
>
> I've shown the Auto_Open procedure using the Application.OnTime method
>to run your macro. This tells Excel to run your macro immediately after
>Auto_Open has completed execution. Giving Auto_Open a chance to complete
>clears memory of all Excel initialization-related code and data and tends
to
>make things more stable, as opposed to calling MyProc directly.
...
Perhaps, but why not the alternative of moving the code from MyMacro into
Auto_Open, since the OP presumably has no desire to do anything other than
run that code before quitting Excel?
Rob Bovey
<<Begging the question how to run Excel synchronously from within a Perl
script if not via automation. If it doesn't need to be run synchronously,
easy enough to spawn a separate Excel process. But if it does, it's A LOT
easier to ensure proper execution order using automation.>>
Automation is not obviously easier in this case. Neither is it obvious
that Excel must run synchronously with the Perl script. If synchronous
execution is required, I am assuming the person who asked the question has
the skill to write a shell-and-wait routine in Perl. If not, I'm sure he can
quickly find some stock code to do this on one of the Perl newsgroups.
<<A perverse joke here because the OP is trying to run this effectively as a
background process from Perl, so the last possible thing desired would be
ANY unnecessary user interaction? Especially perverse if the Perl script
were to open Excel in a hidden Window, resulting in a hung Excel process.>>
This example was designed to be tested quickly from a batch file to
verify that it was, in fact, behaving as desired. Obviously you would remove
the MsgBox statement in the production code. Based on the tone of the
poster's message I am assuming he has the skill to figure that out.
<<Perhaps, but why not the alternative of moving the code from MyMacro into
Auto_Open, since the OP presumably has no desire to do anything other than
run that code before quitting Excel?>>
You need to read my message a bit more carefully. Let me repeat it.
"Giving Auto_Open a chance to complete clears memory of all Excel
initialization-related code and data and tends to make things more stable,
as opposed to calling MyProc directly."
This is standard practice among knowledgeable Excel programmers. Excel does
not garbage collect its own initialization code unless given a chance to
halt completely for just a moment after startup.
--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/
* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
"Harlan Grove" <hrl...@aol.com> wrote in message
news:uRg7cr2kBHA.1948@tkmsftngp03...
Harlan Grove
> <<Begging the question how to run Excel synchronously from within a Perl
> script if not via automation. If it doesn't need to be run synchronously,
> easy enough to spawn a separate Excel process. But if it does, it's A LOT
> easier to ensure proper execution order using automation.>>
>
> Automation is not obviously easier in this case. Neither is it obvious
> that Excel must run synchronously with the Perl script. If synchronous
> execution is required, I am assuming the person who asked the question has
> the skill to write a shell-and-wait routine in Perl. If not, I'm sure he
can
> quickly find some stock code to do this on one of the Perl newsgroups.
Shell & wait is a necessary technique for running non-automation-capable
processes synchronously (e.g., batch files running non-GUI applications like
command line versions of PkZip). It's inefficient because the calling
process that launches the child process is still executing, thus soaking up
time slices and other resources, which is why the wait loop is needed. Perl
is a modern scripting language, so its 'system' function provides
synchronous execution without the baggage of a shell & wait loop.
I'll stick with the recommendation I made in my direct response to the OP:
better to do the entire job in Perl with no VBA macros at all. Less chance
of problems that way.
> <<A perverse joke here because the OP is trying to run this effectively as
a
> background process from Perl, so the last possible thing desired would be
> ANY unnecessary user interaction? Especially perverse if the Perl script
> were to open Excel in a hidden Window, resulting in a hung Excel
process.>>
>
> This example was designed to be tested quickly from a batch file to
> verify that it was, in fact, behaving as desired. Obviously you would
remove
> the MsgBox statement in the production code. Based on the tone of the
> poster's message I am assuming he has the skill to figure that out.
I would have thought generation of the printout would have been sufficient
verification.
As for guessing what the OP knows and doesn't know about Excel & VBA, given
the questions he asked (passing parameters to Excel on the command line, how
to pass filenames), it's not at all clear he has as much understanding of
Excel/VBA as of Perl. If not, it may not be obvious to him that he'd need to
delete the MsgBox statement until it's hung his Perl script a few times.
> <<Perhaps, but why not the alternative of moving the code from MyMacro
into
> Auto_Open, since the OP presumably has no desire to do anything other than
> run that code before quitting Excel?>>
>
> You need to read my message a bit more carefully. Let me repeat it.
>
> "Giving Auto_Open a chance to complete clears memory of all Excel
> initialization-related code and data and tends to make things more stable,
> as opposed to calling MyProc directly."
>
> This is standard practice among knowledgeable Excel programmers. Excel
does
> not garbage collect its own initialization code unless given a chance to
> halt completely for just a moment after startup.
Meaning there's a resource leak if Excel quits within a startup macro? More
reason to dispense with VBA entirely and rely on Perl for full automation
since Excel presumably bypasses many of its initialization procedures when
run as an automation server.
But interesting with regard to general Excel use. So this potential
instability in Excel's initialization code makes it a bad idea to perform
most nontrivial tasks in Auto_Open or Workbook_Open? Or just makes it a bad
idea to quit Excel from within either?
Rob Bovey
better to do the entire job in Perl with no VBA macros at all. Less chance
of problems that way.>>
OK, we differ.
<<I would have thought generation of the printout would have been sufficient
verification.>>
I meant for testing the general concept prior to porting code into it.
<<As for guessing what the OP knows and doesn't know about Excel & VBA>>
If you don't know enough not to display message boxes from background
processes, debugging that code would be a useful exercise IMO.
<<Meaning there's a resource leak if Excel quits within a startup macro?>>
It's not a leak per se, just resources in use that are no longer needed.
They will be garbage collected eventually if Excel exits properly. However,
I've experienced a number of situations where not allowing these resources
to be garbage collected before starting my own application interfered with
the execution of its VBA. The chances of this happening are directly related
to the complexity and resource-intensiveness of your own application.
However, it certainly won't hurt anything to do it regardless.
<<More reason to dispense with VBA entirely and rely on Perl for full
automation since Excel presumably bypasses many of its initialization
procedures when run as an automation server.>>
This would be a guess, since I don't know if your assertion regarding
Excel resource usage when automated is correct.
<<So this potential instability in Excel's initialization code makes it a
bad idea to perform most nontrivial tasks in Auto_Open or Workbook_Open? Or
just makes it a bad idea to quit Excel from within either?>>
This doesn't have anything to do with shutdown. The idea is to allow all
code to pause for just an instant after Excel finishes opening to trigger
its garbage collection routine. All of my significant applications are
structured similar to the following:
Sub Auto_Open()
Application.OnTime Now(), "AppAutoOpen"
End Sub
Sub AppAutoOpen()
''' Run my application startup code here.
End Sub
BTW, I advise not putting any code at all behind Document objects. Excel
VBA projects have a very well-known habit of becoming corrupted. This is due
to problems with the way the VBProject is persisted in the structured
storage format as far as I can tell.
If your project consists entirely of modules, classes, and forms, my
code cleaning utility or something similar can fix this right up. However,
because Document objects cannot be removed from the VBProject without
deleting their corresponding Excel UI component any corruption in their VBA
can potentially force you to manually rebuild an entire worksheet, chart or
even the whole workbook, depending on where the problem lies.
--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/
* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
"Harlan Grove" <Hrl...@aol.com> wrote in message
news:7mKY7.258$fe1....@bgtnsc06-news.ops.worldnet.att.net...
Harlan Grove
>
> If you don't know enough not to display message boxes from background
>processes, debugging that code would be a useful exercise IMO.
It's not the output/display, it's the need to press the #$%& OK button
that's the problem. If the OP were proficient in Perl but a novice in VBA,
would it be reasonable to assume they'd know that MsgBox wasn't a modeless
dialog and would suspend macro execution until dismissed? The Perl/Tk
message widget, with which the OP may possibly be acquainted, is display
only by default, and won't suspend script execution when it's displayed.
It's not at all obvious to me that anyone who knows Perl but not VBA would
think VBA's MsgBox would operate substantially differently than Perl/Tk's
message widget.
> <<More reason to dispense with VBA entirely and rely on Perl for full
> automation since Excel presumably bypasses many of its initialization
> procedures when run as an automation server.>>
>
> This would be a guess, since I don't know if your assertion regarding
>Excel resource usage when automated is correct.
I don't have the url, but there's a knowledge base article that mentions the
things Excel does not do when run as an automation server: loading add-ins,
loading any files in the startup folder, etc. Nothing specific to workbooks,
though. So is this application initialization garbage collection or workbook
open event garbage collection? Indicates poor design either way - either
application initialization garbage collection hasn't commenced before
processing workbook startup scripts, or the application state during startup
script execution leaves much to be desired.
...
> BTW, I advise not putting any code at all behind Document objects.
Excel
>VBA projects have a very well-known habit of becoming corrupted. This is
due
>to problems with the way the VBProject is persisted in the structured
>storage format as far as I can tell.
>
> If your project consists entirely of modules, classes, and forms, my
>code cleaning utility or something similar can fix this right up. However,
>because Document objects cannot be removed from the VBProject without
>deleting their corresponding Excel UI component any corruption in their VBA
>can potentially force you to manually rebuild an entire worksheet, chart or
>even the whole workbook, depending on where the problem lies.
1: make backups.
2: use revision control with programming projects.
3: failure to comply with 1 and 2 means you will eventually get burned.
Most of my VBA code and complete cell formula listings of my major workbooks
reside in a CVS respository, and I keep the last two generations of
workbooks on a local zip disk. [Yes, I have learned the value of doing this
from experience.] While I've never had to do it, I could rebuild all of
these workbooks without too much trouble. If it happened a few times, I'd
think about automating the process.
For the OP: keep multiple backup copies.
If you put no code into document objects, how could you make use of event
handlers? Make them all wrappers calling procedures in modules?
I understand that writing bullet-proof background applications in Excel/VBA
may require forgoing many features that would have been useful if
implemented more cleanly. It's the need to do so (and remembering which
features are no-nos) that leads me to believe that it's simply easier to
dispense with VBA and handle such tasks using automation. I'll admit it's
personal taste.
Rob Bovey
handlers? Make them all wrappers calling procedures in modules?>>
WithEvents class modules handle this quite nicely. In fact they allow
you to trap events at the Application level, which you can't do otherwise.
<<that leads me to believe that it's simply easier to dispense with VBA and
handle such tasks using automation.>>
In the right hands it would probably result in fewer problems, but I've
seen so much automation handled so badly that I wonder how many people
really understand it well enough to use it correctly.
--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/
* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
"Harlan Grove" <Hrl...@aol.com> wrote in message
news:slNY7.321649$W8.12...@bgtnsc04-news.ops.worldnet.att.net...
Harlan Grove
><<If you put no code into document objects, how could you make use of event
>handlers? Make them all wrappers calling procedures in modules?>>
>
> WithEvents class modules handle this quite nicely. In fact they allow
>you to trap events at the Application level, which you can't do otherwise.
OK, so if I had on sheet controls like command buttons whose event handlers
by default go into the containing worksheet's object module I'd need to
place their event handling code into Class modules?
I admit I haven't delved into Class modules. And the Excel 97 online help
for WithEvents is skimpy. At least I have Walkenbach's Excel 2000 VBA
Programming book. Looks like if I had several on sheet command buttons, I'd
need a separate Class module for each, no? If so, how much lovely additional
overhead?
> <<that leads me to believe that it's simply easier to dispense with VBA
and
> handle such tasks using automation.>>
>
> In the right hands it would probably result in fewer problems, but I've
>seen so much automation handled so badly that I wonder how many people
>really understand it well enough to use it correctly.
Comparable to the number who can write decent VBA code. For every botched
piece of automation code in the wide world I wouldn't be surprised if
there's at least twice as much impenetrable VBA code.
But that's besides the point. Someone competent in Perl but not VBA is
likely to make less of a mess with automation written in Perl.