converting vba to perl without win32::ole

Discussion in 'Perl Misc' started by matt, Sep 8, 2006.

  1. matt

    matt Guest

    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::eek: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;
    }
    }
    }
    }
     
    matt, Sep 8, 2006
    #1
    1. Advertising

  2. matt

    Dave Guest

    "matt" <> wrote in message
    news:...
    >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::eek: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::eek: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).
     
    Dave, Sep 10, 2006
    #2
    1. Advertising

  3. matt

    matt Guest

    > I'm not sure why you refer to win32::eek: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::eek: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;
    >}
     
    matt, Sep 11, 2006
    #3
  4. matt

    Dave Guest

    "matt" <> wrote in message
    news:...
    >> I'm not sure why you refer to win32::eek: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::eek: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.
     
    Dave, Sep 11, 2006
    #4
  5. matt

    matt Guest

    > 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
     
    matt, Sep 11, 2006
    #5
  6. matt

    Dave Guest

    "matt" <> wrote in message
    news:...
    >> 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)
     
    Dave, Sep 11, 2006
    #6
  7. matt

    Dave Guest

    > 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).
     
    Dave, Sep 12, 2006
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Patrick.O.Ige

    Ole ole

    Patrick.O.Ige, Jul 16, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    523
    Patrick.O.Ige
    Jul 16, 2006
  2. Drew Pihera
    Replies:
    0
    Views:
    665
    Drew Pihera
    Feb 4, 2004
  3. psyshrike
    Replies:
    0
    Views:
    102
    psyshrike
    Sep 14, 2003
  4. Lance Hoffmeyer
    Replies:
    0
    Views:
    273
    Lance Hoffmeyer
    Nov 17, 2003
  5. Domenico Discepola

    win32::ole and excel VBA macro conversion: SmallScroll

    Domenico Discepola, May 5, 2004, in forum: Perl Misc
    Replies:
    10
    Views:
    562
    Charlton Wilbur
    May 9, 2004
Loading...

Share This Page