topic Vba code in BluePrism in Product Forum

Vba code in BluePrism

ErjolaMema — Wed, 02 Feb 2022 10:59:00 GMT

Hi Everyone!

I want to adapt that code in Blue Prism..but cant finish it all.

Would someone help me?

Here is the code:

Option Explicit

Sub RemoveBlankParas()
    Dim oDoc        As Word.Document
    Dim para        As Word.Paragraph
    Dim i           As Integer
    Dim oRng        As Range
    Dim lParas      As Long
    Dim lEnd        As Long
    Dim lDeleted    As Long

    Set oDoc = ActiveDocument
    lParas = oDoc.Paragraphs.Count          ' Total paragraph count
    'Debug.Print "Total paragraph Count: " & lParas

    ' Loop thru each page
    i = 0       ' Reset starting page - if I'm testing
    Do
        ' Select one page
        i = i + 1
        Selection.GoTo What:=wdGoToPage, Which:=wdGoToAbsolute, Count:=i
        Set oRng = Selection.Range
        oRng.End = Selection.Bookmarks("\Page").Range.End
        oRng.Select

        Debug.Print "Range Count: " & oRng.Paragraphs.Count        ' Paragraphs in this page range
        lEnd = lEnd + oRng.Paragraphs.Count                         ' Keep track of how many processed

        For Each para In oRng.Paragraphs
            'Debug.Print "Par Len:" & vbTab & Len(para.Range.Text) & " | " & Left(para.Range.Text, Len(para.Range.Text) - 1)
            If Len(para.Range.Text) = 1 Then
                para.Range.Delete
                lDeleted = lDeleted + 1
            Else        ' If not blank, then delete o more in this page!
                Exit For
            End If
        Next para

        ' Calc how many paragraphs processed
        If lDeleted + lEnd >= lParas Then       ' If more that we started with, let's call it a day!
            Exit Do
        End If
    Loop

    ' You can add code to loop thru each page and if only one paagraph, ...
    ''' Check if 'empty' page

    ' Get latest count...
    lParas = oDoc.Paragraphs.Count          ' Total paragraph count

    lDeleted = 0        ' reset stuff - in case
    lEnd = 0
    i = 0
    Do
        i = i + 1
        Selection.GoTo What:=wdGoToPage, Which:=wdGoToAbsolute, Count:=i
        Set oRng = Selection.Range
        oRng.End = Selection.Bookmarks("\Page").Range.End
        oRng.Select

        Debug.Print "Range Count: " & oRng.Paragraphs.Count        ' Paragraphs in this page range
        lEnd = lEnd + oRng.Paragraphs.Count
        If oRng.Paragraphs.Count = 1 Then
            If oRng.Paragraphs(1).Range.Text = Chr(12) & Chr(13) Then
                oRng.Paragraphs(1).Range.Delete
                lDeleted = lDeleted + 1
                i = i - 1
            'ElseIf Len(oRng.Paragraphs(1).Range.Text) = 1 Then
            '    oRng.Paragraphs(1).Range.Delete
            '    lDeleted = lDeleted + 1
            '    i = i - 1
            End If
        End If
        If lEnd >= lParas Then
            Exit Do
        End If
    Loop

    ' Finally!!!  Deal with the lingering final page-break!
    Selection.GoTo What:=wdGoToPage, Which:=wdGoToAbsolute, Count:=999      ' Go to Last Page.
    Set oRng = Selection.Range                                              ' Select the end..
    oRng.MoveStart wdCharacter, -3                                          ' Backup 3 characters
    If Left(oRng.Text, 2) = Chr(13) & Chr(12) Then                          ' Should be 13+12
        oRng.Text = ""                                                      ' Remove that thingy!
    End If

    Set para = Nothing
    Set oDoc = Nothing
    Exit Sub
End Sub

AND BELOW IS HOW I HAVE MADE IT

Dim d = GetDocument(handle,documentname)
Dim a As Object = GetInstance(handle)

Dim oDoc As a
Dim para As a.Paragraphs
Dim i As Integer=1
Dim oRng As Range
Dim lParas As Long
Dim lEnd As Long
Dim lDeleted As Long
Dim wdGoToPage As Integer
Dim wdGoToAbsolute As Integer
Dim wdCharacter As Integer

lParas = oDoc.Paragraphs.Count

Do While i = i + 1
Selection.GoTo (What:=wdGoToPage, Which:=wdGoToAbsolute, Count:=i)
oRng = a.Selection.Range
oRng.End = a.Selection.Bookmarks("\Page").Range.End
oRng.Select

d.Debug.Print "Range Count: " & oRng.Paragraphs.Count ' Paragraphs in this page range
lEnd = lEnd + oRng.Paragraphs.Count ' Keep track of how many processed
For Each para In oRng.Paragraphs
'Debug.Print "Par Len:" & vbTab & Len(para.Range.Text) & " | " & Left(para.Range.Text, Len(para.Range.Text) - 1)
If Len(para.Range.Text) = 1 Then
para.Range.Delete
lDeleted = lDeleted + 1
Else ' If not blank, then delete o more in this page!
Exit For
End If
Next para

' Calc how many paragraphs processed
If lDeleted + lEnd >= lParas Then ' If more that we started with, let's call it a day!
Exit Do
End If
Loop

lParas = oDoc.Paragraphs.Count ' Total paragraph count

lDeleted = 0 ' reset stuff - in case
lEnd = 0
i = 0
Do
i = i + 1
a.Selection.GoTo (What:=wdGoToPage, Which:=wdGoToAbsolute, Count:=i)
oRng = a.Selection.Range
oRng.End = a.Selection.Bookmarks("\Page").Range.End
oRng.Select

d.Debug.Print "Range Count: " & oRng.Paragraphs.Count ' Paragraphs in this page range
lEnd = lEnd + oRng.Paragraphs.Count
If oRng.Paragraphs.Count = 1 Then
If oRng.Paragraphs(1).Range.Text = Chr(12) & Chr(13) Then
oRng.Paragraphs(1).Range.Delete
lDeleted = lDeleted + 1
i = i - 1
'ElseIf Len(oRng.Paragraphs(1).Range.Text) = 1 Then
' oRng.Paragraphs(1).Range.Delete
' lDeleted = lDeleted + 1
' i = i - 1
End If
End If
If lEnd >= lParas Then
Exit Do
End If
Loop

' Finally!!! Deal with the lingering final page-break!
a.Selection.GoTo (What:=wdGoToPage, Which:=wdGoToAbsolute, Count:=999 ) ' Go to Last Page.
oRng = a.Selection.Range ' Select the end..
oRng.MoveStart ( wdCharacter, -3 ) ' Backup 3 characters
If Left(oRng.Text, 2) = Chr(13) & Chr(12) Then ' Should be 13+12
oRng.Text = "" ' Remove that thingy!
End If

para = Nothing
oDoc = Nothing

------------------------------
Erjola Mema
------------------------------

RE: Vba code in BluePrism

Scarry_Terry — Wed, 02 Feb 2022 15:31:00 GMT

@ErjolaMema,

In order the help you at least a bit you should provide more information instead of providing x lines of code without any context. What are you trying to achieve, what exactly does not work, what kind of exceptions it throws
If I were you I would start with the the MS Word VBO object and divide your code into the more actions (in case those ones do not already exists. For instance, there is Create Instance, Open....).

Just by having a brief glance over your code. Some things does not really make sense.
For instance the fourth line of code. "A" is not a type, it is variable you already declared line above....

Dim oDoc As a

Another obvious one would be the line 26. There is neither function nor method debug.print for variable "d". Although, I guess I understand what you tried to did here. You tried to implement Debug.Print from the VBA, this is not possible in VB.NET. If you are trying to debug this code, first thing I recommend is to divide it into several actions (code stages). In case you might want to get some info out of code stage while debugging, perhaps you can do MessageBox.Show....

d.Debug.Print "Range Count: " & oRng.Paragraphs.Count ' Paragraphs in this page range

These are just general advises, regarding the actions specific to MS Word, I won't be able to help much... Hopefully, after you clarify some things, someone else will step in and help 🙂

------------------------------
Matej Kubiš
------------------------------

RE: Vba code in BluePrism

devneetmohanty07 — Wed, 02 Feb 2022 15:36:00 GMT

Hi Erjola,

I have cleaned up the code a bit so that it can work seamlessly without any compiler issue. However, since I am not really aware about the use case so I can't say if the business output is something that is being achieved or not. Please refer the below section of code for your reference:

Dim d = GetDocument(handle,documentname)
Dim a As Object = GetInstance(handle)

Dim oDoc = d
Dim para As Object
Dim i As Integer=1
Dim oRng As Object
Dim lParas As Long
Dim lEnd As Long
Dim lDeleted As Long
Dim wdGoToPage As Integer
Dim wdGoToAbsolute As Integer
Const wdCharacter As Integer = 1

lParas = oDoc.Paragraphs.Count

Do While i = i + 1

oDoc.Selection.GoTo (What:=wdGoToPage, Which:=wdGoToAbsolute, Count:=i)
oRng = a.Selection.Range
oRng.End = a.Selection.Bookmarks("\Page").Range.End
oRng.Select

lEnd = lEnd + oRng.Paragraphs.Count ' Keep track of how many processed

For Each para In oRng.Paragraphs

If Len(para.Range.Text) = 1 Then

para.Range.Delete
lDeleted = lDeleted + 1

Else ' If not blank, then delete o more in this page!

Exit For

End If

Next para

' Calc how many paragraphs processed
If lDeleted + lEnd >= lParas Then ' If more that we started with, let's call it a day!

Exit Do

End If

Loop

lParas = oDoc.Paragraphs.Count ' Total paragraph count

lDeleted = 0 ' reset stuff - in case
lEnd = 0
i = 0
Do
i = i + 1
a.Selection.GoTo (What:=wdGoToPage, Which:=wdGoToAbsolute, Count:=i)
oRng = a.Selection.Range
oRng.End = a.Selection.Bookmarks("\Page").Range.End
oRng.Select

lEnd = lEnd + oRng.Paragraphs.Count
If oRng.Paragraphs.Count = 1 Then

If oRng.Paragraphs(1).Range.Text = Chr(12) & Chr(13) Then
oRng.Paragraphs(1).Range.Delete
lDeleted = lDeleted + 1
i = i - 1
'ElseIf Len(oRng.Paragraphs(1).Range.Text) = 1 Then
' oRng.Paragraphs(1).Range.Delete
' lDeleted = lDeleted + 1
' i = i - 1
End If

End If

If lEnd >= lParas Then

Exit Do

End If

Loop

' Finally!!! Deal with the lingering final page-break!
a.Selection.GoTo (What:=wdGoToPage, Which:=wdGoToAbsolute, Count:=999 ) ' Go to Last Page.
oRng = a.Selection.Range ' Select the end..
oRng.MoveStart (wdCharacter, -3 ) ' Backup 3 characters

If Left(oRng.Text, 2) = Chr(13) & Chr(12) Then ' Should be 13+12

oRng.Text = "" ' Remove that thingy!

End If

para = Nothing
oDoc = Nothing

------------------------------
----------------------------------
Hope it helps you and if it resolves you query please mark it as the best answer so that others having the same problem can track the answer easily

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
Wonderbotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------