topic RE: How to write a collection in MS WORD VBO in Product Forum

How to write a collection in MS WORD VBO

PatrykLiberski — Fri, 05 Jul 2019 13:48:00 GMT

Hello, I've got one problem. I need to write my collection which contains 50 rows into my MS Word file.
How can I do it? I've checked that there is an action named Type Text, but it is only for text type, not for a collection.
Could you please give me some advice ?
Best regards

------------------------------
Patryk Liberski

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

RE: How to write a collection in MS WORD VBO

david.l.morris — Fri, 05 Jul 2019 13:59:00 GMT

You'll probably need some custom code to programmatically create a table and then set the cell values of the table by looping through the rows/columns of your collection in BP.

I imagine you could also use hotkeys (or maybe ALT+N+T+I and then spy the popup window to configure the table size) to create the table. The easiest way is if the table already exists. The MS Word VBO has an action for 'Add Table Row'. So you'd just need to be sure it already has enough columns. With this, you'd loop through your collection in Blue Prism, assigning the table cell values as you go.

I don't already have custom code for this, so maybe someone else will respond with a code solution for this. In the meantime, I'm guessing you'd do something like this:
https://docs.microsoft.com/en-us/visualstudio/vsto/how-to-programmatically-create-word-tables?view=vs-2019

------------------------------
Dave Morris
3Ci @ Southern Company
Atlanta, GA
------------------------------

RE: How to write a collection in MS WORD VBO

IshanMahajan — Fri, 05 Jul 2019 14:43:00 GMT

If your collection is not much complicated and you just need to write it in word document, you can try - Utility - JSON object, it will convert the collection to a text string, you can apply some string operations to the text string to get it in your desired format.

Like replacing ":'" by space, and removing { and }, not sure what would be other challenges but this seems to be simple approach.

------------------------------
Ishan Mahajan
India
------------------------------

RE: How to write a collection in MS WORD VBO

david.l.morris — Fri, 05 Jul 2019 14:48:00 GMT

@PatrykLiberski

Ishan makes a good point. You could use a quick convert action to put the data into Word. I could have sworn I saw in your post that you wanted it in a Word Table. If you don't need it in a table, try the JSON object as Ishan mentions. You could also try the Utility - Strings action 'Get Collection as CSV' and do similar string manipulation to change the field delimeter and such. I think it's gonna depend on what the purpose is of putting the data into Word that will affect how you need to format it.

Edit: As far as getting the collection into Word after converting from collection to CSV or to JSON, you can use the resulting text data item as input to the 'Utility - Environment' VBO's action 'Set Clipboard' to get the data into the clipboard. Then You can use the MS Word VBO's action 'Paste from Clipboard'.

------------------------------
Dave Morris
3Ci @ Southern Company
Atlanta, GA
------------------------------

RE: How to write a collection in MS WORD VBO

GopalBhaire — Sat, 06 Jul 2019 08:54:00 GMT

You can write collection to excel and copy paste it to Word as well or you can try this custom code

Dim doc as Object = GetDocument(handle,documentname)
Dim word As Object = doc.Application
Dim s As Object = word.Selection.Range
Dim tblNew As Object 
Dim celly,cellx As Integer 
cellx=1
celly=1

If headers Then
	tblNew = doc.Tables.Add(s,dt.Rows.Count+1,dt.Columns.Count,1,1)
	Else
	tblNew = doc.Tables.Add(s,dt.Rows.Count,dt.Columns.Count,1,1)
End If

If headers Then
	For Each col As System.Data.DataColumn In dt.Columns
		tblNew.Cell(cellx,celly).Range.Text = col.ColumnName
		celly += 1
	Next
	cellx += 1
End If

For Each row As System.Data.DataRow In dt.Rows
	celly = 1
	For Each col As System.Data.DataColumn In dt.Columns
		tblNew.Cell(cellx,celly).Range.Text = row(col)
		celly += 1
	Next
	cellx += 1
Next

handle, documentname, dt (collection) & headers(flag) are inputs

------------------------------
Gopal Bhaire
Analyst
Accenture
------------------------------

RE: How to write a collection in MS WORD VBO

HarshadkumarPat — Wed, 13 Nov 2019 13:57:00 GMT

Hi Gopal,

Thanks for sharing the code. I have crated code stage as you suggested, but I am getting following error. Can you please let me know if I need to select any namespace or dll to make this code works? There is no error at code run stage, but I am getting following error related "Object variable or With block variable not set."

It would be great if you can help me with this error.

------------------------------
Harshadkumar Patel
Senior Automation Associate
S&P Global Market Intelligence
Asia/Kolkata
------------------------------

RE: How to write a collection in MS WORD VBO

AyshaM — Fri, 07 Aug 2020 15:58:00 GMT

Were you able to resolve this?

I am getting the following errors:
1. "GetDocument" is not declared

Because of this error in code stage I am not able to run my process.
Any help would be greatly appreciated

------------------------------
Aysha M
------------------------------