Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

VBA to Perl

by sanPerl (Friar)
on Feb 10, 2006 at 11:36 UTC ( #529325=perlquestion: print w/ replies, xml ) Need Help??
sanPerl has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks,
I am Not good in VBA, and I would like to convert a VBA Macro to Perl. After reading few help pages from ActivePerl site. I wrote a Perl code. Now it is giving following error
Can't modify non-lvalue subroutine call at rapid.pl line 16.
My VBA Macro goes as follows
VBA Macro
Selection.HomeKey wdStory Selection.Text = "This is the inserted text 1" Selection.Collapse wdCollapseEnd Selection.TypeParagraph Selection.Text = "This is the inserted text 2" Selection.Collapse wdCollapseEnd Selection.TypeParagraph
and my Perl script goes as follows.
Perl Script
use strict; use warnings; use Win32::OLE; use Win32::OLE::Const 'Microsoft Word'; use Cwd; my ($file) = @ARGV; my $cwd_path = getcwd(); $cwd_path=~ s/\//\\/gs; my $file_name = "$cwd_path\\$file"; my $Word = Win32::OLE->new('Word.Application'); $Word->{'Visible'} = 1; $Word->Documents->Open($file_name) || die("Unable to open document", W +in32::OLE->LastError());; $Word->Selection->HomeKey (wdStory); $Word->Selection->Text="This is the inserted text"; $Word->Selection->Collapse (wdCollapseEnd); $Word->Selection->TypeParagraph; $Word->Selection->Text("This is the inserted text 2"); $Word->Selection->Collapse (wdCollapseEnd); $Word->Selection->TypeParagraph; $Word->Documents($file_name)->Save(); $Word->Quit();
I searched from 'SuperSearch' but could Not receive satisfactory information. I would be grateful, if somebody could guide me on this. I don't expect straightforward solutions but if you could direct me to any good tutorials on this topic, I would be grateful to you.
Regards,
Sandeep
Update
Dear All,
Thanks to all for your support. It was my lack of knowledge that I was using "Text" instead of "TypeText". Thanks to marto for providing this hint.

Comment on VBA to Perl
Select or Download Code
Re: VBA to Perl
by mhacker (Sexton) on Feb 10, 2006 at 11:59 UTC
    I've never used Win32::OLE, but I saw this:
    $Word->Selection->Text="This is the inserted text"; $Word->Selection->Collapse (wdCollapseEnd); $Word->Selection->TypeParagraph; $Word->Selection->Text("This is the inserted text 2");
    I guess that you cannot assign to the Text function like you do in the first line. It is a function that you need to supply an argument to (like you do in the last line).
Re: VBA to Perl
by marto (Chancellor) on Feb 10, 2006 at 12:01 UTC
    Hi sanPerl,

    The code $Word->Selection->Text="This is the inserted text" Looks wrong to me, do you want to select or type text? If you want to type text I think it should be in the form of TypeText("your text goes here"). The code I posted below is untested (I don't have access to test this at the moment) but I think it should work: Tested and working, see my updated at the bottom of this post.
    use strict; use warnings; use Win32::OLE; use Win32::OLE::Const 'Microsoft Word'; use Cwd; my ($file) = @ARGV; my $cwd_path = getcwd(); $cwd_path=~ s/\//\\/gs; my $file_name = "$cwd_path\\$file"; my $Word = Win32::OLE->new('Word.Application'); $Word->{'Visible'} = 1; my $document = $Word->Documents->Open($file_name) || die("Unable to op +en document", Win32::OLE->LastError()); my $Selection = $Word->Selection; $Selection->HomeKey (wdStory); $Selection->TypeText("This is the inserted text"); $Selection->Collapse (wdCollapseEnd); $Selection->TypeParagraph; $Selection->TypeText("This is the inserted text 2"); $Selection->Collapse (wdCollapseEnd); $Selection->TypeParagraph; $Word->Documents($file_name)->Save(); $Word->Quit();

    You should check out the Win32::OLE documentation, under Hints for Microsoft Office automation it reads:

    'The object model for the Office applications is defined in the Visual Basic reference guides for the various applications. These are typically not installed by default during the standard installation.'

    If you are unsure of the object model surf over to http://msdn.microsoft.com and have a look around.

    ActiveState have an article How to convert a VBA macro to Perl which should be worth a read.

    Update:I just tested the VB macro in Word 2003 against the code I posted above. To me it looks like the document produced by the VB code is the same as the document produced by the Perl code.

    Hope this helps.

    Martin

      Hi marto.

      use strict; use warnings; use Win32::OLE; use Win32::OLE::Const 'Microsoft Word'; use Cwd; #my ($file) = @ARGV; #my $cwd_path = getcwd(); #$cwd_path=~ s/\//\\/gs; #my $file_name = "$cwd_path\\$file"; my $file_name = "c:\\entityandsymbol.doc"; my $Word = Win32::OLE->new('Word.Application'); $Word->{'Visible'} = 1; my $document = $Word->Documents->Open($file_name) || die("Unable to op +en document", Win32::OLE->LastError()); my $Selection = $Word->Selection; $Selection->HomeKey (wdStory); $Selection->TypeText("This is the inserted text"); $Selection->Collapse (wdCollapseEnd); $Selection->TypeParagraph; $Selection->TypeText("This is the inserted text 2"); $Selection->Collapse (wdCollapseEnd); $Selection->TypeParagraph; $Word->Documents($file_name)->Save(); $Word->Quit();

      I am getting the error as follows:

      Unable to open documentOLE exception from "Microsoft Word": Could not open macro storage. Win32::OLE(0.1701) error 0x800a175d in METHOD/PROPERTYGET "Open" at array.pl line 4630.

      But the word file opening corectly without modification which I did in coding.

      Suggest me in correct way.

      Updated: I found the solution for the above problem as per "http://www.blink.com.au/tech/trouble.htm" information.

      Could Not Open Macro Storage Error (Run-time error 5981)

      When running BLINK you can get the error message "Could not open macro storage". This is caused by either not having Service Pack 1 installed with Microsoft® Office, or because the Visual Basic and Microsoft® Word installation has become corrupted.

      To fix it you will need to either install Service Pack 1, if it has not been installed before, or reinstall Microsoft® Word.

      I am reinstalled the Microsoft Word then the above code working fine.

      Regards,
      Velusamy R.


      eval"print uc\"\\c$_\""for split'','j)@,/6%@0%2,`e@3!-9v2)/@|6%,53!-9@2~j';

        Hi Samy_rio,

        Your script runs fine for me.

        Google on: Microsoft Office "Could not open macro storage"

        The results indicate there could be a problem with your Office setup.

        Sorry not to be able to help more.

        John

Re: VBA to Perl
by aquarium (Curate) on Feb 10, 2006 at 12:03 UTC
    on line 16 should be....Text("This is the inserted text");
    without the = sign
    the hardest line to type correctly is: stty erase ^H
Re: VBA to Perl
by BrowserUk (Pope) on Feb 10, 2006 at 12:06 UTC

    You may already know the answer to your own question :)

    In the line giving the error

    $Word->Selection->Text="This is the inserted text";

    You are trying to treat $Word->Selection->Text as a variable and perl is complaining that Can't modify non-lvalue subroutine call, which means that it must be a subroutine and not a variable.

    Three lines lower down you have

    $Word->Selection->Text("This is the inserted text 2");

    Same thing, but this time you are using it as a subroutine. Change the error line to match the other one and that error will probably go away.


    Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.
    Lingua non convalesco, consenesco et abolesco. -- Rule 1 has a caveat! -- Who broke the cabal?
    "Science is about questioning the status quo. Questioning authority".
    In the absence of evidence, opinion is indistinguishable from prejudice.
Re: VBA to Perl
by Errto (Vicar) on Feb 10, 2006 at 19:09 UTC
    AFAIK this should also work:
    $Word->Selection->{Text}="This is the inserted text";
Re: VBA to Perl
by spurperl (Priest) on Feb 11, 2006 at 06:15 UTC
    I just want to note that while browsing the ActivePerl page the other day, I run upon their automatic VBA to Perl converter. This tool is part of their professional development kit (non-free) - it takes a VBA script and faithfully converts it to a Perl script using Win32::OLE. This can be very useful for a company in need of massive VBA to Perl conversions.
      Selection.Find.Execute "aaa" Selection.TypeParagraph Selection.MoveRight 1, 1
Re: VBA to Perl
by planetscape (Canon) on Feb 11, 2006 at 07:57 UTC

    To supplement marto's great links, you may also have on your machine something called "OLE Browser" or "Type Library Browser" if you installed Win32-OLE-0.1403 or upgraded from that to a later version. You might find it in C:\Perl\html\OLE-Browser or C:\Perl\html\lib\site\Win32\OLE depending on your installation.

    Something else I've found to be very handy indeed is West Wind Technology's GetConstants utility, which allows you to look at all those cryptic constants MS Office uses. (A short snippet of output is below.) Note that they do shuffle the links around occasionally, so it may be necessary to do some searching. :-)

    *** Constant Group: WdTableFieldSeparator #define wdSeparateByParagraphs 0 #define wdSeparateByTabs 1 #define wdSeparateByCommas 2 #define wdSeparateByDefaultListSeparator 3

    HTH,

    planetscape

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://529325]
Front-paged by MidLifeXis
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (14)
As of 2014-09-23 15:40 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (227 votes), past polls