Blue Prism Product

 View Only
last person joined: yesterday 

This community covers the core Blue Prism product.

  • 1.  How to put Text to Columns with Excel VBO

    Posted 03-15-2018 22:06
    Hi,  I have been trying to create a Code Stage in order to put text of a CSV file delimited by a "^" in Columns.  Initially I tried using Import CSV, replacing the delimiter and putting it in a Collection,  but the files are too big and it gives Memory Errors.    Has anyone done anything like this. I did a code but it´s not working.  Dim wb, ws, range, excel as Object wb = GetInstance(handle) ws = GetWorkbook(handle, WorkbookName).ActiveSheet.Range(cellref,cellref).Activate() excel= ws.Application range = ws().Selection.TextToColumns(OtherChar, Other)   I would really appreciate if anyone has some inputs here. Thanks!


  • 2.  Try This…
    Best Answer

    Posted 03-23-2018 10:58
    Try This   Dim RR As Object Dim excel As Object Try RR = GetWorksheet(Handle, WorkBookName, WorkSheetName) RR.Activate() excel = RR.Application RR.UsedRange.Select() excel.Selection.TextToColumns (DataType:=1, ConsecutiveDelimiter:=True,Other:=True,OtherChar:=Delimiter) Success = True Catch e As Exception Success = False Message = e.Message End Try


  • 3.  RE: Try This…

    Posted 01-11-2020 07:48
    Hi Rakesj Reddy,

    How to use Fieldinfo in your code, I need to convert as as text or general.

    Thanks in Advance

    ------------------------------
    SREEKANTH GTR
    Junior software engineer
    Wipro
    Indian/Maldives
    ------------------------------



  • 4.  RE: How to put Text to Columns with Excel VBO

    Posted 09-02-2020 23:56
    Create an intenger matrix
    Dim matrix = New Integer(6, 1) {{1, 1}, {2, 1}, {3, 9}, {4, 1}, {5, 1}, {6, 1}, {7, 1}}

    Then you could use it as parameter for FieldInfo
    FieldInfo:=matrix

    Full Code

    Dim wb, ws As Object
    Dim excel, sheet, range As Object
    Dim matrix = New Integer(6, 1) {{1, 1}, {2, 1}, {3, 9}, {4, 1}, {5, 1}, {6, 1}, {7, 1}}

    Try

    wb = GetWorkbook(Handle, Workbook)
    ws = GetWorksheet(Handle, Workbook, Worksheet)

    wb.Activate()
    ws.Activate()
    excel = ws.Application

    excel.Selection.TextToColumns (Destination:=excel.Range(Cell_Reference), DataType:=1, _
    TextQualifier:=-4142, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    :=Delimiter, FieldInfo:=matrix, TrailingMinusNumbers:=True)

    Success = True

    Catch e As Exception
    Success = False
    Message = e.Message
    Finally
    wb = Nothing
    ws = Nothing
    excel = Nothing
    sheet = Nothing
    range = Nothing
    End Try

    ------------------------------
    Miguel Carrillo
    ------------------------------



  • 5.  RE: How to put Text to Columns with Excel VBO

    Posted 03-02-2022 13:57
    Hi @Miguel Carrillo,

    I wanted to convert text or custom columns to date columns using a code stage. But when i declare the array as you mentioned above its not working. Its throwing an error



    I have recorded the manual operation using record macro. PFB

    Could you please help me in declaring the array for the above code in such a way that i can use it in my code stage!!!!!!


    ------------------------------
    Sasikanth Machavarapu
    ------------------------------



  • 6.  RE: How to put Text to Columns with Excel VBO

    Posted 03-02-2022 15:07
    Hi @Sasikanth Machavarapu,

    If you're trying to change the format of a column have you tried using the Format Cell action and passing in the format that you want? The action is designed to work on a single cell at a time, but you could create a custom version that handles an actual range by adding an extra input value for the end of the range. Then you would change the single line Code stage to include the end of range value.


    Cheers,


    ------------------------------
    Eric Wilson
    Director, Integrations and Enablement
    Blue Prism Digital Exchange
    ------------------------------



  • 7.  RE: How to put Text to Columns with Excel VBO

    Posted 03-04-2022 15:30

    Hi the following code should work:

    In this link you will find the integer for the type of format you need it. https://docs.microsoft.com/en-us/office/vba/api/excel.xlcolumndatatype
    Remember that the The first element is the column number (1-based), and the second element is one of the XlColumnDataType constants specifying how the column is parsed.

    Dim wb, ws As Object
    Dim excel, sheet, range As Object
    Dim matrix = New Integer(2, 1) {{1, 4}, {2, 4}}
    
    Try
    
    wb = GetWorkbook(Handle, Workbook)
    ws = GetWorksheet(Handle, Workbook, Worksheet)
    
    wb.Activate()
    ws.Activate()
    excel = ws.Application
    
    excel.Selection.TextToColumns (Destination:=excel.Range(Cell_Reference), DataType:=1, _
    TextQualifier:=-4142, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    :=Delimiter, FieldInfo:=matrix, TrailingMinusNumbers:=True)
    
    Success = True
    
    Catch e As Exception
    Success = False
    Message = e.Message
    Finally
    wb = Nothing
    ws = Nothing
    excel = Nothing
    sheet = Nothing
    range = Nothing
    End Try​

    Let me know if you have any questions. And I could try to explain it to you in a deeper way.
    Have a nice day.



    ------------------------------
    Miguel Carrillo
    ------------------------------



  • 8.  RE: How to put Text to Columns with Excel VBO

    Posted 6 days ago
    Hello,

    I am trying to use this code to convert  AG column (33th column from an Excel file with mutiple columns) into a DATE format column but I think I am doing something wrong because I get the "The given key was not present in the dictionary" error.

    Maybe the input is not ok? Can you please help me ?




    Thank you,
    Irina

    Have a nice day!

    ------------------------------
    Irina Mihalache
    ------------------------------



  • 9.  RE: How to put Text to Columns with Excel VBO

    Posted 4 days ago
    Hi @Irina Mihalache,

    ​The error "The given key was not present in the dictionary" generally comes when you have created the instance (i.e. handle) and keep on making changes in the code while in debug mode. In such scenario, the handle which you have created, lost its value from the memory and you get the error. The best way to resolve the issue is reset the code and again run it. The issue won't appear. Please let me know if that is not the scenario.

    Note- I am assuming that there is only single handle in the process


    ------------------------------
    Manpreet Kaur
    Manager
    Deloitte
    ------------------------------



  • 10.  RE: How to put Text to Columns with Excel VBO

    Posted 3 days ago
    Hello Manpreet Kaur,

    Thank you so much for your response! I resolved the "The given key was not present in the dictionary" error, now the message is "Success", but not working as expected.

    I need to make AG column (column number 33) from 'General' to 'Date' (DMY option) and I am using this code below, but nothings happens, although I do not receive any error. I also tried with DataType 4, and TextQualifier 1, but nothing changes.


    Dim wb, ws As Object

    Dim excel, sheet, range As Object

    Dim matrix = New Integer(6, 1) {{1, 1}, {2, 1},{3, 9},{4, 1},{5, 1},{6, 1},{7,1}}

     

    Try

     

    wb = GetWorkbook(Handle, Workbook)

    ws = GetWorksheet(Handle, Workbook, Worksheet)

     

    wb.Activate()

    ws.Activate()

    excel = ws.Application

     

    excel.Columns("AG:AG").Select

    excel.Selection.TextToColumns (Destination:=excel.Range("AG1"), DataType:=1, _

    TextQualifier:=-4142, ConsecutiveDelimiter:=False, Tab:=True, _

    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=matrix, TrailingMinusNumbers:=True)

     

    Success = True

     

    Catch e As Exception

    Success = False

    Message = e.Message

    Finally

    wb = Nothing

    ws = Nothing

    excel = Nothing

    sheet = Nothing

    range = Nothing

    End Try




    ------------------------------
    Irina Mihalache
    ------------------------------



Welcome to the Blue Prism Product Community!

Whether you’re looking to manage a complex infrastructure, maintain security and compliance, bring new products to market faster, or gain operational speed and agility in an uncertain economy, Blue Prism delivers — with the flexibility you need to create the business you want. From deployment on-premise, through a cloud service provider or as SaaS, to a skillful and adaptable digital workforce that continually expands to meet your enterprise needs, you can gain enhanced operational insight and control while your people reclaim the time they need to focus on great work.

Product PageKnowledge BaseBlue Prism Training Offering
Product Research ProgramUpdates, Releases & Announcements

FAQs

Blue Prism is intelligent automation — business-developed, no-code automation that pushes the boundaries of robotic process automation (RPA) to deliver value across any business process in a connected enterprise.

A combination of RPA with expanded cognitive and AI capabilities, Blue Prism is different than other automation technology on the market. With one Blue Prism license, you gain instant access to an already AI equipped digital workforce, along with the tools you need to build and delegate automations. Click here for more information on Blue Prism and Intelligent Automation.
To learn more about how Blue Prism can help your organization and how much it will cost to get started, please Contact our Sales department.
Blue Prism can be downloaded from our customer portal. If you would like to consume or download any material it is necessary to create an account on the Portal. Once you have registered, you can access the download options for Blue Prism here.
Yes! Installed on your own machine and supported by our training materials and product documentation, you can use all the features of the full enterprise product for free with our Blue Prism Trial – giving you the opportunity to learn the basics before moving to a full production implementation. Click here for more information and to download the trial.
Yes! You can access our known issue list for Blue Prism from our Support Portal.
Regardless of your industry, Blue Prism’s Digital Workforce can adhere to strict governance and compliance standards without limiting productivity. Click here for more information on how your industry can benefit from Blue Prism.