cancel
Showing results for 
Search instead for 
Did you mean: 

Converting VBA Code to VB to Use in Code Stage

SeanScudellari
Level 4
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
------------------------------
13 REPLIES 13

PabloSarabia
Level 11
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: 

18182.png

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
------------------------------

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:


18183.png

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
------------------------------

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
------------------------------

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?

18187.png

Thank you.

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

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

----------------------------------
------------------------------
---------------------------------------------------------------------------------------------------------------------------------------
Hope this helps you out and if so, please mark the current thread as the 'Answer', so others can refer to the same for reference in future.
Regards,
Devneet Mohanty,
SS&C Blueprism Community MVP 2024,
Automation Architect,
Wonderbotz India Pvt. Ltd.

@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: 

18193.png
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
------------------------------

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
------------------------------

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
------------------------------

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
------------------------------