Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > converting vba to perl without win32::ole

Reply
Thread Tools

converting vba to perl without win32::ole

 
 
matt
Guest
Posts: n/a
 
      09-08-2006
I am currently trying to convert a vba macro to perl. The vba macros
are basically find and replaces on txt files. The problem is that there
are roughly 25000 find and replaces and they are setup to run in a
sequential order, meaning that they search for a string and if the
string is found then it will find and replace from that point on until
EOF. Once it reaches the EOF it will then reset to the start of the txt
file and continues on to the next find and replace. How can I
accomplish this type of parsing with perl. I am trying to get the vba
code out of microsoft word so I do not want to use the win32:le for
this conversion. Here is a snippet of the VBA and converted perl code.
Thanks for any help in advance.

<SNIP>
VBA code:

Set variable = New variable_Class
If variable.FindText("<R [,]") = True Then
Selection.Collapse wdCollapseEnd
If variable.FindText("<R [,]") = True Then
Selection.Collapse wdCollapseEnd
If variable.FindText("<R [,]") = True Then
Selection.Collapse wdCollapseEnd
If variable.FindText("[0-9]{1,} [0-9]{1,} [0-9]") =
True Then
Selection.HomeKey wdStory
variable.FindandReplace "(^13+pg,[0-9]{1,}^13)(+l)
", " \2\1"
Call Subroutine
End If
End If
End If
End If


Perl converted code:

if ($_=~/<R [,]/){
#Selection.Collapse wdCollapseEnd <==not sure how to convert
this to perl
if ($_=~/<R [,]/){
#Selection.Collapse wdCollapseEnd <==not sure how to convert
this to perl
if ($_=~/<R [,]/){
#Selection.Collapse wdCollapseEnd <==not sure how to convert
this to perl
if ($_=~/[0-9]{1,} [0-9]{1,} [0-9]/){
#Selection.HomeKey wdStory <==not sure how to convert this
to perl
$_=~s/(\n\+pg,[0-9]{1,}\n)(\+l)/ $2$1/g;
&Subroutine;
}
}
}
}

 
Reply With Quote
 
 
 
 
Dave
Guest
Posts: n/a
 
      09-10-2006

"matt" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) oups.com...
>I am currently trying to convert a vba macro to perl. The vba macros
> are basically find and replaces on txt files. The problem is that there
> are roughly 25000 find and replaces and they are setup to run in a
> sequential order, meaning that they search for a string and if the
> string is found then it will find and replace from that point on until
> EOF. Once it reaches the EOF it will then reset to the start of the txt
> file and continues on to the next find and replace. How can I
> accomplish this type of parsing with perl. I am trying to get the vba
> code out of microsoft word so I do not want to use the win32:le for
> this conversion. Here is a snippet of the VBA and converted perl code.
> Thanks for any help in advance.
>
> <SNIP>
> VBA code:
>
> Set variable = New variable_Class
> If variable.FindText("<R [,]") = True Then
> Selection.Collapse wdCollapseEnd
> If variable.FindText("<R [,]") = True Then
> Selection.Collapse wdCollapseEnd
> If variable.FindText("<R [,]") = True Then
> Selection.Collapse wdCollapseEnd
> If variable.FindText("[0-9]{1,} [0-9]{1,} [0-9]") =
> True Then
> Selection.HomeKey wdStory
> variable.FindandReplace "(^13+pg,[0-9]{1,}^13)(+l)
> ", " \2\1"
> Call Subroutine
> End If
> End If
> End If
> End If
>
>
> Perl converted code:
>
> if ($_=~/<R [,]/){
> #Selection.Collapse wdCollapseEnd <==not sure how to convert
> this to perl
> if ($_=~/<R [,]/){
> #Selection.Collapse wdCollapseEnd <==not sure how to convert
> this to perl
> if ($_=~/<R [,]/){
> #Selection.Collapse wdCollapseEnd <==not sure how to convert
> this to perl
> if ($_=~/[0-9]{1,} [0-9]{1,} [0-9]/){
> #Selection.HomeKey wdStory <==not sure how to convert this
> to perl
> $_=~s/(\n\+pg,[0-9]{1,}\n)(\+l)/ $2$1/g;
> &Subroutine;
> }
> }
> }
> }
>


OK this code is just covering for the lack of a true Regular Expression
engine in VBA. The Selection.Collapse bit is just saying 'start the next
part of the search from the end of the last succesful one'.

It is equivalent to:

if ($_ =~ /<R [,].*<R [,].*<R [,].*[0-9]+ [0-9]+ [0-9]/) {
$_ =~ s/(\n\+pg,[0-9]+\n)(\+l)/ $2$1/g;
&Subroutine;
}

(untested)

If this is not enough to put you on the right trial post again for more
clarification.

I'm not sure why you refer to win32:le, if you want to parse the VBA code
using perl to automatically convert it, you can save it as a text file,
(.vbs is just a plain text file).


 
Reply With Quote
 
 
 
 
matt
Guest
Posts: n/a
 
      09-11-2006
> I'm not sure why you refer to win32:le, if you want to parse the VBA code
> using perl to automatically convert it, you can save it as a text file,
> (.vbs is just a plain text file).


Only reason I mentioned win32:le was because I didn't want someone to
suggest that I use the ActiveState Perl Dev Kit and run the VBScript
converter tool. My main goal is to remove the memory hog Microsoft
products.

One other question I have is: "Will the global substitution after the
if stmt replace until the EOF or will it replace for the entire file?"
>if ($_ =~ /<R [,].*<R [,].*<R [,].*[0-9]+ [0-9]+ [0-9]/) {
> $_ =~ s/(\n\+pg,[0-9]+\n)(\+l)/ $2$1/g;
> &Subroutine;
>}


 
Reply With Quote
 
Dave
Guest
Posts: n/a
 
      09-11-2006

"matt" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) oups.com...
>> I'm not sure why you refer to win32:le, if you want to parse the VBA
>> code
>> using perl to automatically convert it, you can save it as a text file,
>> (.vbs is just a plain text file).

>
> Only reason I mentioned win32:le was because I didn't want someone to
> suggest that I use the ActiveState Perl Dev Kit and run the VBScript
> converter tool. My main goal is to remove the memory hog Microsoft
> products.
>
> One other question I have is: "Will the global substitution after the
> if stmt replace until the EOF or will it replace for the entire file?"
>>if ($_ =~ /<R [,].*<R [,].*<R [,].*[0-9]+ [0-9]+ [0-9]/) {
>> $_ =~ s/(\n\+pg,[0-9]+\n)(\+l)/ $2$1/g;
>> &Subroutine;
>>}

>


It will replace for the whole file, as does the original VBA code, I think -
Selection.HomeKey wdStory
resets to the beginning of the file.

However I'm not sure now that your searching and your Selection operations
are happening on the same thing...

Notably findText and FindandReplace are not standard methods in VBA. What
libraries are you using? What is Variable_class?

I think you need to provide fuller information.


 
Reply With Quote
 
matt
Guest
Posts: n/a
 
      09-11-2006
> It will replace for the whole file, as does the original VBA code, I think -
> Selection.HomeKey wdStory
> resets to the beginning of the file.

Selection.HomeKey wdStory does reset to the beginning of the file and
the VBA code does replace for the whole file

> However I'm not sure now that your searching and your Selection operations
> are happening on the same thing...
>
> Notably findText and FindandReplace are not standard methods in VBA. What
> libraries are you using? What is Variable_class?

Sorry about the lack of information(definitely don't know VBA very
well, I am trying to fix someone elses mess) findText will search for a
string that is passed in and will highlight to the selection object the
next occurance. FindandReplace will search through the document for
the passed in string and replace it with the replacement string (uses
..Execute Replace:=wdReplaceAll)

How would I search for a string and once found do find and replaces
from that point on until EOF in perl?

Thanks for your help so far

 
Reply With Quote
 
Dave
Guest
Posts: n/a
 
      09-11-2006

"matt" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) ups.com...
>> It will replace for the whole file, as does the original VBA code, I
>> think -
>> Selection.HomeKey wdStory
>> resets to the beginning of the file.

> Selection.HomeKey wdStory does reset to the beginning of the file and
> the VBA code does replace for the whole file
>
>> However I'm not sure now that your searching and your Selection
>> operations
>> are happening on the same thing...
>>
>> Notably findText and FindandReplace are not standard methods in VBA. What
>> libraries are you using? What is Variable_class?

> Sorry about the lack of information(definitely don't know VBA very
> well, I am trying to fix someone elses mess) findText will search for a
> string that is passed in and will highlight to the selection object the
> next occurance. FindandReplace will search through the document for
> the passed in string and replace it with the replacement string (uses
> .Execute Replace:=wdReplaceAll)
>
> How would I search for a string and once found do find and replaces
> from that point on until EOF in perl?
>
> Thanks for your help so far
>


You are probably looking for the \G modifier:

if ($_ =~ /<R [,].*<R [,].*<R [,].*[0-9]+ [0-9]+ [0-9]/) {
$_ =~ s/\G(\n\+pg,[0-9]+\n)(\+l)/ $2$1/g;
&Subroutine;
}

(untested)


 
Reply With Quote
 
Dave
Guest
Posts: n/a
 
      09-12-2006
> You are probably looking for the \G modifier:
>
> if ($_ =~ /<R [,].*<R [,].*<R [,].*[0-9]+ [0-9]+ [0-9]/) {
> $_ =~ s/\G(\n\+pg,[0-9]+\n)(\+l)/ $2$1/g;
> &Subroutine;
> }
>
> (untested)
>
>


No, please ignore this I misunderstood the /G anchor.

This should work :

if ($_ =~ /<R [,].*<R [,].*<R [,].*[0-9]+ [0-9]+ [0-9]/) {
# Use lookbehind assertion and loop to make global
1 while ($_ =~ s/(?<=<R [,].*<R [,].*<R [,].*[0-9]+ [0-9]+
[0-9].*)(\n\+pg,[0-9]+\n)(\+l)/ $2$1/
&Subroutine;
}

(untested)

You might get away with the /g modifier rather than the while loop - try it
and see. I can't quite work out all the consequences of the multiple .* in
the look-behind assertion on the behavior of /g, although it might be OK. If
it is slow there may be ways to optimise it (using .*? may help).


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Upload file without user interaction in vba google.com@motix.com ASP General 4 06-27-2007 09:17 AM
Please help me pass an array from VBA to Perl and populate it. Newbie at wits' end! david.f.jenkins@usa.net Perl Misc 10 10-06-2006 07:18 PM
loading a macro programmatically in excel VBA or perl surf Perl Misc 0 03-22-2006 11:10 PM
VBA to Perl macro conversion Matt Garrish Perl Misc 0 09-17-2004 09:20 PM
Initiate perl script in linux from a Windows VBA program b wreath Perl Misc 3 06-02-2004 04:32 PM



Advertisments