Excel Actions

[FrankieTEWV]

Hello,

I am automating a task that a team does which is downloading an excel report and uploading it into a system. That part I have built and works, however before it uploads the excel report into the system it needs to do some actions within excel and this is where I need some assistance.

The actions I need are not listed in OLDED B or the Excel VBO.

I believe I need to create some code but unsure - can anyone assist me on the actions I need to carry out. Below are the steps I would take if doing it manually.

1.) Select any column in the excel worksheet 

2.) Click 'Data'

3.) Click 'Text to Columns'

4. Select 'Delimited'

5.) Click 'Next'

6.) Text Qualifier  - Select 'None' on the drop down 

7.) Click Next

😎 Click Finish

Thanks in advance

Frankie

[Jrwork]

Hi, @FrankieTEWV 

A few weeks ago I went through something related, I used the DX object that solved it:

https://digitalexchange.blueprism.com/dx/entry/3439/solution/blue-prism---read-csv

The DLL is already in the folder

[FrankieTEWV]

Thanks so much - i will give this a go

[Jrwork]

I'm at your disposal if you have any questions 

[Mohamad_747]

@FrankieTEWV 

If your request is not resolved yet you can use a macro VBA and create a custom code in ms excel vbo to call the Macro . 

This is the Macro : 

Sub DynamicTextToColumns(columnToSelect As String, destinationCell As String)
Columns(columnToSelect).Select
Selection.TextToColumns Destination:=Range(destinationCell), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True

End Sub

And For Blue Prism Custom code stage you can duplicate the run macro action and add a new action named Run Macro With Arguments : 

For Argument1, Argument2 its a text datatype

You provide this in the process when you call it if for example you want to choose the Column A

Macro_Name = DynamicTextToColumns

Argument1 = A:A

Argument2 = A1

Regards