topic RE: Converting VBA Code to VB to Use in Code Stage in Product Forum

Converting VBA Code to VB to Use in Code Stage

SeanScud — Thu, 14 Jul 2022 16:19:00 GMT

Hello, I have some VBA code I wrote to get all bullet point text in a Word document and I would like to use this within a code stage in Blue Prism to get all bullet point text within a text variable or preferably, a collection variable. The VBA code runs fine within Word, but I'm having trouble converting the code into VB to work within Blue Prism.

Here is the VBA code:

Dim Para As Paragraph
For Each Para In ActiveDocument.Paragraphs
If Para.Range.ListFormat.ListType = wdListBullet Then
MsgBox Para.Range
End If
Next Para

The main issue I seem to be running across is "paragraph" is not a valid data type in Blue Prism that can be stored in a text or collection variable. So I'm a bit stuck on how to handle this. Also, I know that "wdListBullet" corresponds to a value of 2 for its enumeration, so I believe the value 2 needs to be used instead of "wdListBullet" within the code stage. Any help would be greatly appreciated.

Thank you!

------------------------------
Sean Scudellari
------------------------------

RE: Converting VBA Code to VB to Use in Code Stage

PabloSarabia — Thu, 14 Jul 2022 19:26:00 GMT

Hi @Sean Scudellari

I think that BP is not recognising the Paragraph data item cause you don't have added the Interop dll

Try checking this configuration for the VBO:

I think, the best way to this is to create a new page in this VBO: https://digitalexchange.blueprism.com/dx/entry/3439/solution/ms-word-vbo

This code does not return any compilation errors but I cannot test it.

Dim doc as Object = GetDocument(handle,documentname)
Dim range As Object

Dim Para As Paragraph

For Each Para In ActiveDocument.Paragraphs
If Para.Range.ListFormat.ListType = wdListBullet Then

'do something

End If
Next Para

Try with this!

Hope this helps you!

See you in the Community, bye 🙂

------------------------------
Pablo Sarabia
Solution Manager & Architect
Altamira Assets Management
Madrid
------------------------------

RE: Converting VBA Code to VB to Use in Code Stage

SeanScud — Thu, 14 Jul 2022 20:55:00 GMT

Thank you @Pablo Sarabia! I was created this custom action within the Microsoft Word VBO as you mentioned. This is what my configuration looks like in the Word VBO:

I believe this matches your picture, correct? Or is there anything else I need to change within this configuration? Also, I'm using Word 2010. Perhaps this interop dll is not compatible with Word 2010?

------------------------------
Sean Scudellari
------------------------------

RE: Converting VBA Code to VB to Use in Code Stage

PabloSarabia — Thu, 14 Jul 2022 21:18:00 GMT

Hi @Sean Scudellari

I think it doesn't matter which version. What error do you get when you create a page within that VBO?

Is the exact code you use the one you indicated above?

Bye 🙂

------------------------------
Pablo Sarabia
Solution Manager & Architect
Altamira Assets Management
Madrid
------------------------------

RE: Converting VBA Code to VB to Use in Code Stage

SeanScud — Thu, 14 Jul 2022 21:28:00 GMT

Hey, @PabloSarabia, this is the error I'm getting. I had to make a couple adjustments to your code because I was getting some additional errors, but now I'm just left with this one error that I'm not sure how to resolve. Do I need to create an input for Paragraph?

Thank you.

------------------------------
Sean Scudellari
------------------------------

RE: Converting VBA Code to VB to Use in Code Stage

devneetmohanty07 — Fri, 15 Jul 2022 00:10:00 GMT

Hi Sean,

Try using this code at line number 5. It should ideally work:

Dim Para As Microsoft.Office.Interop.Word.Paragraph

------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future

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

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

RE: Converting VBA Code to VB to Use in Code Stage

SeanScud — Fri, 15 Jul 2022 01:01:00 GMT

@PabloSarabia @devneetmohanty07

Thank you, Devneet. I updated my code and am no longer getting an error, however, the output I get is "System.__ComObject"

Here's the code I now have:

I believe I need the .ToString() in the result or else I'll get an error, however, this is causing my result variable to have an output of "System.__ComObject"

I feel like I am pretty close to getting this and just need to find a way to get the values in the output.

Appreciate your help!

------------------------------
Sean Scudellari
------------------------------

RE: Converting VBA Code to VB to Use in Code Stage

PabloSarabia — Fri, 15 Jul 2022 02:41:00 GMT

Hi @Sean Scudellari

To get the text from a paragraph use this:

result = Para.Range.Text

But, with this you only get one of the texts. Do you want to get a collection of all the texts or something similar?

Bye 🙂

------------------------------
Pablo Sarabia
Solution Manager & Architect
Altamira Assets Management
Madrid
------------------------------

RE: Converting VBA Code to VB to Use in Code Stage

michaeloneil — Fri, 15 Jul 2022 14:24:00 GMT

Hi Sean

You might find this page useful for any future vba conversion to vb Converting Code from VBA to Visual Basic .NET | Microsoft Docs

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

RE: Converting VBA Code to VB to Use in Code Stage

SeanScud — Fri, 15 Jul 2022 16:45:00 GMT

Thank you @Pablo Sarabia! That worked! But yes, getting each bullet point within a collection would be great.

Also, if I wanted to extract only the bullet points from a certain section of the document, how would I do that? The range of each document I need to process will be different (each will have a different number of characters, so would be hard to use a 'start' and 'finish' input variables within a range). However, the text that comes before and after the specific bullet points will be same in each document.

For example, the document would look like this:

Section 1 - Header

Bullet 1
Bullet 2
Bullet 3

Section 2 - Header

I would like to get the bullet point text only between section 1 and section 2 of the document, but the section 1 and section 2 may be in a different range within each document I need to process, but their sections headings/titles will always be the same.

Thank you so much for the help, I greatly appreciate it!

------------------------------
Sean Scudellari
------------------------------

RE: Converting VBA Code to VB to Use in Code Stage

SeanScud — Fri, 15 Jul 2022 16:47:00 GMT

Hey @Michael ONeil. This is very helpful. Thank you so much for pointing this out!

------------------------------
Sean Scudellari
------------------------------

RE: Converting VBA Code to VB to Use in Code Stage

SeanScud — Mon, 18 Jul 2022 21:27:00 GMT

@PabloSarabia Please see my reply above. Thanks!

------------------------------
Sean Scudellari
------------------------------

RE: Converting VBA Code to VB to Use in Code Stage

PabloSarabia — Tue, 19 Jul 2022 05:23:00 GMT

Hi @Sean Scudellari

Sorry, didn't see the message 🙂

First, in your code stage add two input parameters, "strStartText" and "strEndText". (You need to indicate here the start text that you want to find to start checking for bullet texts, and the end text to stop checking)

Also you need to add a output parameter as collection, in my example "dtResult"

Dim doc as Object = GetDocument(handle,documentname)
Dim range As Object

Dim Para As Microsoft.Office.Interop.Word.Paragraph

Dim table As New DataTable
table.Columns.Add("Bullets", GetType(String))

Dim blnStart as Boolean 
blnStart = false

For Each Para In ActiveDocument.Paragraphs

  If Para.Range.Text.Contains(strStartText) Then
    blnStart = true
  End If

  If Para.Range.ListFormat.ListType = wdListBullet and blnStart Then
    table.Rows.Add(Para.Range.Text)


  If Para.Range.Text.Contains(strEndText) Then
    exit for 
  End If

  
Next Para

dtResult = table

Try with this!

Hope this helps you! And if this solves the problem, remember mark as the best answer

See you in the Community, bye 🙂

------------------------------
Pablo Sarabia
Solution Manager & Architect
Altamira Assets Management
Madrid
------------------------------

RE: Converting VBA Code to VB to Use in Code Stage

SeanScud — Tue, 19 Jul 2022 23:22:00 GMT

Hey @PabloSarabia No worries! I don't think I tagged you properly in my previous reply.

I was able to get your code working after tweaking it a little bit. I really appreciate the help!

The final thing I need help with is starting the search for the headers on the second page of the document. I realized that the first page of the document has a table of contents that lists the same Section 1 and Section 2 header names. I need to have the code stage start searching for the bullets on page 2 of the document instead of page 1 so that it skips the table of contents. Please let me know if you know a solution.

Thank you again!

------------------------------
Sean Scudellari
------------------------------