win32::ole and excel VBA macro conversion: SmallScroll

[Domenico Discepola]

Hello all. I'm trying to write some code to scroll my window pane (in
Microsoft Excel) & wish convert the following VBA macro to Perl format:
ActiveWindow.SmallScroll ToRight:=-3

I have defined the following: $excel (excel object), $worksheet (worksheet
object), $workbook (workbook object).

I tried:
$excel->ActiveWindow->SmallScroll->{ToRight} = -3;
$workbook->ActiveWindow->SmallScroll->{ToRight} = -3;
$worksheet->ActiveWindow->SmallScroll->{ToRight} = -3;

all without success... Any suggestions would be appreciated.

 

[Bob Walton]

Hello all. I'm trying to write some code to scroll my window pane (in
Microsoft Excel) & wish convert the following VBA macro to Perl format:
ActiveWindow.SmallScroll ToRight:=-3

I have defined the following: $excel (excel object), $worksheet (worksheet
object), $workbook (workbook object).

I tried:
$excel->ActiveWindow->SmallScroll->{ToRight} = -3;
$workbook->ActiveWindow->SmallScroll->{ToRight} = -3;
$worksheet->ActiveWindow->SmallScroll->{ToRight} = -3;

all without success... Any suggestions would be appreciated.

Try:

$excel->ActiveWindow->SmallScroll({ToRight=>-3});

 

[Domenico Discepola]

Try:

$excel->ActiveWindow->SmallScroll({ToRight=>-3});

Thanks Bob - it worked like a charm. Now, here's my next question... I
find it very difficult to translate VBA into Perl. The existing
documentation is very basic. For instance, although Activestate's ASPN
provides a general example of how to translate VBA into Perl, it could not
provide me with the proper method that you did (as you can tell by my
trial-and-error approach). One trick I learned from someone in the
newsgroups is outlined in the following code. He suggested I print out all
key/values in the hash associated with each object. This way, you can drill
down and search through all methods available in each object. However, I
was still unable to find out how to scroll an Excel worksheet. My question
is, does anyone know of a good reference guide/book that gives lots of
concrete examples? I read the Win32 Perl Programming book by Dave Roth and
he does provide great examples but not enough of them. I would even go
about suggesting the development of some kind of module that helps
individuals drill down into various objects' methods and properties...

#!perl
use strict;
use warnings;
use Win32::OLE;

sub quitapp () {
my ( $ComObject ) = @_;
$ComObject->Quit();

}

sub main () {
my $class = "Excel.Application";

#Create application class called "$excel"
my $excel = Win32::OLE->GetActiveObject( $class );

if ( ! $excel ) {
$excel = new Win32::OLE( $class, \&quitapp ) || die "Could not create COM
${class} object\n";
}

$excel->{SheetsInNewWorkbook} = 1;

#create a new workbook
my $workbook = $excel->Workbooks->Add();

#create a new worksheet
my $worksheet = $workbook->WorkSheets(1);
$worksheet->{Name} = "test";

while ( my ($key, $value) = each %$excel ) {
#print "$key = $value\n";

if ( $key eq 'ActiveWindow' ) {

while ( my ( $key2, $value2 ) = each %$value ) {
#print "$key2 = $value2\n";

if ( $key2 eq 'Panes' ) {
while ( my ( $key3, $value3 ) = each %$value2 ) {
print "$key3 = $value3\n";

}
}
}

}

}

$workbook->Close();
sleep(2);
$excel->Quit();
}

&main();
exit 0;

 

[Bob Walton]

....
Thanks Bob - it worked like a charm. Now, here's my next question... I
find it very difficult to translate VBA into Perl. The existing

The basic steps are outlined in the docs for Win32::OLE. The thing I
used for your case is the fourth line of the synopsis, for example -- I
don't see how one could get clearer than that. I have always found what
I needed to know about Win32::OLE in the Win32::OLE docs. If you study
the docs and follow them to the letter, you will be *way* better off
than your admitted "trial and error approach". Also, you will find the
"OLE Browser" in Excel to be a source of information found nowhere else.

If you want to "drill down into an object's methods and properties", you
have a couple of choices. One is the Perl debugger. Another is the
Data:umper module. Another is to peruse the module's source code.
And another is to read the docs. Guess which one is easiest.


....

 

[moller]

SNIP


Thanks Bob - it worked like a charm. Now, here's my next question... I
find it very difficult to translate VBA into Perl. The existing
documentation is very basic. For instance, although Activestate's ASPN
SNIP

was still unable to find out how to scroll an Excel worksheet. My question
is, does anyone know of a good reference guide/book that gives lots of
concrete examples? I read the Win32 Perl Programming book by Dave Roth and
he does provide great examples but not enough of them.

I wholeheartedly agree, a good book on the subject would have saved me weeks
of experimenting and tinkering.

In february I got thrown into the deep end of the pool with Dave Roth's win32
book and Progamming Perl. The assignment was: take these textfiles with data
and make us some nice tables and charts in ms excel.

And having no experience with eiter Perl or MSExcel or COM objects I found it
quite challenging.

And now they want a gui so I'm having fun with Perl/Tk.

Peter

 

[Domenico Discepola]

The basic steps are outlined in the docs for Win32::OLE. The thing I
used for your case is the fourth line of the synopsis, for example -- I
don't see how one could get clearer than that. I have always found what
I needed to know about Win32::OLE in the Win32::OLE docs. If you study
the docs and follow them to the letter, you will be *way* better off
than your admitted "trial and error approach". Also, you will find the
"OLE Browser" in Excel to be a source of information found nowhere else.

If you want to "drill down into an object's methods and properties", you
have a couple of choices. One is the Perl debugger. Another is the
Data:umper module. Another is to peruse the module's source code.
And another is to read the docs. Guess which one is easiest.

If I may play devil's advocate... Here's an example of what I consider to
be confusing documentation. On Activestate's website,

"How do I convert a VBA macro to Perl?
If you record a macro in Microsoft Office, this can often be translated
directly into Perl. In Visual Basic for Applications (VBA) the syntax is
like this:

object.method(argument).property = value

In Perl this becomes

object->method(argument)->{property} = value;
"
So, as it was suggested, I used Excel's macro recording feature to record
the following VBA code:
ActiveWindow.SmallScroll ToRight:=-3

So, using the documentation mentioned above, I translated this into:
$excel->ActiveWindow->SmallScroll->{ToRight} = -3;
which didn't work. This seemingly simple example causes newbie programmers
much grief. I saw no mention of adding parenthesis (as you provided in your
solution): $excel->ActiveWindow->SmallScroll({ToRight=>-3});

 

[Domenico Discepola]

I wholeheartedly agree, a good book on the subject would have saved me weeks
of experimenting and tinkering.

In february I got thrown into the deep end of the pool with Dave Roth's win32
book and Progamming Perl. The assignment was: take these textfiles with data
and make us some nice tables and charts in ms excel.

And having no experience with eiter Perl or MSExcel or COM objects I found it
quite challenging.

And now they want a gui so I'm having fun with Perl/Tk.

Peter

Finally, someone who agrees ;-)

I had similar "assignments"... The "worst one" was to take csv files and
generate Excel workbooks with pivot tables (try figuring that one out).
Roth's book was very helpful. I would very much like for a guru to write a
book on using Perl with Windows applications (perhaps with a section for
each popular Microsoft Office application - Word, Excel, Outlook, etc. ).

The reason I am stressing this is that my business users are constantly on
the lookout for ways to improve efficiency - and I don't think I am alone.
For example, if they could automatically receive a monthly Excel file with
pre-formatted reports/pivot tables, it would save them lots of time. If our
executives could receive automatically generated/formatted powerpoint files,
they wouldn't have to waste their time creating those. Why would I choose
to do this in Perl (as opposed to other programming languages)? Because
Perl is the best ;-) Perl provides a lot of functionality with very little
code (especially thanks to those regexs and cpan modules).

By the way, I was also asked to create a gui for one of my applications. I
used vb.net for that. It was nothing more than a front-end interface to my
perl programs, with some code for reading/writing parameters to/from the
registry.

Dom

 

[Richard Morse]

"How do I convert a VBA macro to Perl?
If you record a macro in Microsoft Office, this can often be translated
directly into Perl. In Visual Basic for Applications (VBA) the syntax is
like this:

object.method(argument).property = value

In Perl this becomes

object->method(argument)->{property} = value;
"
So, as it was suggested, I used Excel's macro recording feature to record
the following VBA code:
ActiveWindow.SmallScroll ToRight:=-3

So, using the documentation mentioned above, I translated this into:

$excel->ActiveWindow->SmallScroll->{ToRight} = -3;

which didn't work. This seemingly simple example causes newbie programmers
much grief. I saw no mention of adding parenthesis (as you provided in your
solution): $excel->ActiveWindow->SmallScroll({ToRight=>-3});

So, look closely at the Activestate example:

object.method(argument).property = value
^

See that '.' before the property?

Now, look at the VBA created by the macro recording feature:

ActiveWindow.SmallScroll ToRight:=-3

Note the absence of the '.'? VB does not require parenthesis on all
function calls (neither does Perl, for that matter). So what you have
here is a call to ActiveWindow.SmallScroll. It is being passed what I
assume is a named parameter called 'ToRight', which has a value of -3.
Hence:

$excel->ActiveWindow->SmallScroll( { ToRight => -3 } );

which is a function call to SmallScroll, passing in a named parameter
called 'ToRight'.

HTH,
Ricky

(disclaimer: I don't know VBA -- I can read it a little, and can fix the
occasional broken code)

 

[Bob Walton]

You should *never* have to "experiment and tinker". It is *way* more
effective to study and learn the terminology (so you know, for example,
what exactly VBA means when its docs say "property"), and then follow
the docs to the letter. When you do that, things work -- you can write
code that works the first time. The approach of copying the first
example you see, ignoring "minor" differences, must be hugely
frustrating. It is not the docs or the books that are deficient, it is
the technique by which you are approaching programming that is deficient.


....

 

[moller]

You should *never* have to "experiment and tinker". It is *way* more

^^^^^^

A nice word but unforunatly I was not in a position to argue.
After one week at a new job I was given a 4 week deadline and
having no experince with eiter perl or excel I do what I must.

After two week the logic was done[1], the following three weeks
I spent trying to get excel to do what I wanted with charts
,fonts, textboxes, colors and whatnot. Because the charts had
to look "Just So".....

The price of tinkering in this way is not so good code but we
got a new customer on it and it's paying a third of my salary.
And I'm the poor bugger trying to clean up the code whenever I
have some time.

effective to study and learn the terminology (so you know, for
example, what exactly VBA means when its docs say "property"), and

Well I didn't have the time and they deemed a VB book unnessesary.
I was after all writing the thing in perl. (Also their decision)

then follow the docs to the letter. When you do that, things work --
you can write code that works the first time. The approach of copying
the first example you see, ignoring "minor" differences, must be
hugely frustrating. It is not the docs or the books that are ^^^^^^^^^^^^^^^^^^
very

deficient, it is the technique by which you are approaching
programming that is deficient.

I spent several years at uni learning proper[2] computer science.
And I have tried many times to do software development the *right*
way but sadly the goal has mostly been to get it out of the door NOW.

I'm not telling where I work

I wish someone would give me a problem to solve that I could spend
six moths on, reading the apropriate documentation. Selecting
the right tools for the job and perhaps maby get a properly written
specification (ohh wishful thinking).


I have never been able to use most the skills I was tought
regarding software design, robustness and efficency.

The only thing that I use is what they taught us about computer
languages. No matter what funny words they use or what weird
syntax they use they are fundamentally the same.

So one of my abilitys is to take a problem and solve it[3] with
the tools given to me (language/development environment).
Give me a week I can start working with (almost) any pice of code
you can throw at me written in any obscure computer language.

/Well just my opinion


[1] I mean that the design was done, the data extraction
was done, the data massaging was done and I knew
how to write the data to a worksheet in excel.

[2] I thought about writing about how good it is/was
but cant be botherd. If interested read at
http://www.it.uu.se/edu/masters/CS
Note! I was there more 92-96 so it has
probably changed

[3] For varying values of the solve

 

[Charlton Wilbur]

M> I spent several years at uni learning proper[2] computer
M> science. And I have tried many times to do software
M> development the *right* way but sadly the goal has mostly been
M> to get it out of the door NOW.

This was much my experience, too, when I was working directly for a
corporation. I studied pure academic computer science; the computer
was mainly involved, it seemed at times, because proving programs
correct is tiresome. There was an upper-level course in software
engineering, as it happened; I took it, and found that most of it
should have been immediately apparent, and thus I had a rather low
opinion of software engineering because its practitioners apparently
needed to be spoonfed common-sense axioms.

And then I went to work, and realized that the people on the ground,
doing the programming, didn't need to be spoonfed the common-sense
axioms -- rather, the managers did.

M> I wish someone would give me a problem to solve that I could
M> spend six moths on, reading the apropriate
M> documentation. Selecting the right tools for the job and
M> perhaps maby get a properly written specification (ohh wishful
M> thinking).

You will never get a properly written specification unless you refuse
to even begin the coding until you have one, and if you do that you'll
probably be fired first. The common middle-manager is afraid to
commit anything to paper; by refusing to provide you with a clear
specification, he wins both ways: if what you give him is what he
needs, he wins, and if what you give him is not what he needs, it's
your fault for not being psychic. If you're feeling more charitable,
you might conclude that he doesn't *know* what he needs until he sees
what you give him and decides if it's what he needs or not.

M> I have never been able to use most the skills I was tought
M> regarding software design, robustness and efficency.

I have. Of course, I'm doing development on spec of application
software that I hope to sell, and so anything that increases its
robustness means I'll be doing less support work later.

And for that matter, after I left my last job, one of my former
coworkers emailed me to thank me for the test suites/scripts I had
written for all of the Perl modules I wrote. Sad that I took a lot of
heat for slow development, and until after I left nobody noticed how
bug-free and problem-free the code I produced was. (There's probably
about 15,000 lines of my Perl code that's still in production there.)

Charlton