Issue in Write Collection (Fast) action in MS Excel VBO

[_Amlansahoo]

Hi Team,

I am facing an issue while using Write Collection (Fast) action in Excel VBO. When there is already data in Excel and Bot is trying to write from the specific cell it's replacing the adjacent column or Row with blank values.
@ewilson Please look into the issue it seems to be a bug in the code stage.

Below is an example of the same.
Initial Value in my Excel as below image,

Now BOT is using Write Collection (Fast) to write Value in B2 as 10, then the adjacent rows and column value automatically became blank


Similarly when BOT is using Write Collection (Fast) to write Value in B2 as 10 and B3 as 5, then the adjacent column turns to blank.

------------------------------
Amlan Sahoo
Senior RPA Consultant
WonderBotz
------------------------------

[RománSánchez_Pé]

Hi Geoff, 

Thank you kindly for you answer.

Yes, i'm using the latest version in DX 10.1.10.

In fact, when I use the regular action "write collection" every value is being inserted correctly.

Thank you & Kind regards,

------------------------------
Román Sánchez
RPA Developer
------------------------------

------------------------------
Román Sánchez Pérez
------------------------------

[GeoffHirst]

Hi Roman,

Thanks for confirming your asset version. I will investigate this today and report back progress. 

regards

------------------------------
Geoff Hirst
Senior DX Engineer - Digital Exchange - EMEA
SS&C Blue Prism
------------------------------

[GeoffHirst]

Hi Roman,

This is indeed an interesting one. From my side because I do not see different functionality between the two actions. Both are doing exactly the same function on my setup.

Now, I have created a collection with some data and a couple of columns one of which is a text column and I am placing in values similar to yours with similar formatting. The other column I have made a Number column, and entered the same value. Formatting isn't possible on the number column.

When I run my test process, both actions perform identically. The interesting thing is that the column that is Text, the value in Excel has the comma as the thousands separator and in the correct places, the cell format is Number. The number column has no separator and the format applied to the numeric value is General.

My concern is mainly over the fact that you are getting a different outcome between the 2 actions and I don't. In addition, Excel seems to be working some magic of its own here.

I am wondering if we could devise a way of specifying specific formats for cells would help you at all? I think this is down to what Excel does and its localization factors. There are a couple of possible properties of a range that might be candidates to hold some kind of tag that could be subsequently read by another process and apply a specific format. I am writing out loud here, please forgive me if this is irrelevent to you. 

Here is the outcome that I got when I tried an example.

I think at this time, we are at the mercy of how Excel formats cells depending on the incoming data, also localization is playing a role here. 

Please feedback any thoughts you may have.

regards

------------------------------
Geoff Hirst
Senior DX Engineer - Digital Exchange - EMEA
SS&C Blue Prism
------------------------------

[GeoffHirst]

Roman,

I had a conversation with Eric Wilson last night about this and he also recommended that it might be worth checking the locale of your OS, Excel and Blue Prism. Ideally, all should be on the same locale. You are probably good here but worth a check.

regards

------------------------------
Geoff Hirst
Senior DX Engineer - Digital Exchange - EMEA
SS&C Blue Prism
------------------------------

[RománSánchez_Pé]

Hi Geoff,

I do apologise for the late reply.

To your first answer: 
I have applied a macro into the excel setting all cells to "General" format and still values are not copied as they're supposed to.
I've also tried & applied the action into a different process with a blank Excel but nothing seems to be copied as it is in the collection.

To your second answer:
I believe the locale from my OS, Excel & BluePrism  are all synchronized since i never experienced any other format, neither any other type of issue on any other on my processes... but as you recommend it is definitely worth a check.

Thank you kindly for your help & time Geoff,

Kind regards,

------------------------------
Román Sánchez
RPA Developer
------------------------------

------------------------------
Román Sánchez Pérez
------------------------------

[GeoffHirst]

Hi Román,

This is a puzzling one. We think that the difference in which the fast one works compared to the normal one is because the normal one uses a method called SetProperty (This can be seen in the global code for the asset). SetProperty uses reflection, which isn't known for its speed, hence we made a fast one that doesn't use reflection but it just dumps an array of objects into an excel range. The original one, does some other background tricks like applies the local culture etc. 

' The common language runtime (CLR) automatically passes locale ID 1033 to all methods and properties
' in the Excel object model that accept locale-sensitive data. There is no way to change this behavior
' automatically for all calls into the object model. However, you can pass a different locale ID to a
' specific method by using InvokeMember to call the method and by passing the locale ID to the culture
' parameter of the method.
' https://msdn.microsoft.com/en-us/library/bb157877.aspx
' Additionaly we must use the culture that Excel is using and not the local machine.

Now this could be causing the difference you are seeing between the two actions. As I don't see any difference, its difficult to say for certain. I will keep looking into this and will report back with anything useful.

Sorry not to be able to resolve immediately, but there are a number of variables involved here.

regards

------------------------------
Geoff Hirst
Senior DX Engineer - Digital Exchange - EMEA
SS&C Blue Prism
------------------------------

[RománSánchez_Pé]

Hi Geoff,

Thank you kindly for your reply.

Do not worry. I really appreciate your help & time on this matter.

Kind regards,

------------------------------
Román Sánchez
RPA Developer
------------------------------

------------------------------
Román Sánchez Pérez
------------------------------

[RománSánchez_Pé]

Hi Geoff,

Hope you are fine.

This code works as fast as the posted one. I'm not an expert in .NET but works very good for me.

I'll share it & perhaps you could give me your opinion about it?

Thank you & Kind regards,

Dim ws, cref As Object
Dim c, r, colNum, rowNum As Integer

Try
    ' Obtener la hoja de trabajo
    ws = GetWorksheet(handle, workbookname, worksheetname)
    cref = ws.Range(cellref)
    rowNum = cref.row
    colNum = cref.column

    ' Preparar la matriz para almacenar los datos del DataTable
    Dim arr(collection.Rows.Count - 1, collection.Columns.Count - 1) As Object
    For r = 0 To collection.Rows.Count - 1
        For c = 0 To collection.Columns.Count - 1
            arr(r, c) = collection.Rows(r).Item(c)
        Next
    Next

    ' Escribir los encabezados de columna si es necesario
    If includecolnames Then
        For c = 0 To collection.Columns.Count - 1
            ws.Cells(rowNum, colNum + c).Value = collection.Columns(c).ColumnName
        Next
        rowNum += 1
    End If

    ' Definir el rango en el que se escribirán los datos
    Dim startCell As Object = ws.Cells(rowNum, colNum)
    Dim endCell As Object = ws.Cells(rowNum + collection.Rows.Count - 1, colNum + collection.Columns.Count - 1)
    Dim writeRange As Object = ws.Range(startCell, endCell)

    ' Establecer el formato de número para el rango si es necesario
    writeRange.NumberFormat = "@"  ' Establece el formato a Texto

    ' Escribir toda la matriz de datos de una vez
    writeRange.Value = arr

    ' Opcional: Ajustar automáticamente el ancho de las columnas para una mejor legibilidad
    writeRange.EntireColumn.AutoFit()

    Success = True
Catch e As Exception
    Success = False
    Message = e.Message
Finally
    ' Limpiar
    ws = Nothing
    cref = Nothing
End Try

------------------------------
Román Sánchez Pérez
------------------------------

[GeoffHirst]

Hi Román,

Your code looks fine, I haven't tried to run it yet, but if it works for you then that's great news. What I will say is, you aren't using reflection so you retain the speed of the fast one. Reflection allows you do to do some cool things, but the trade off is speed, even on todays processors.

If you are happy this is your solution, then we can leave it here, if you need more help, just ask.

Thanks for being a Blue Prism user.

regards

------------------------------
Geoff Hirst
Senior DX Engineer - Digital Exchange - EMEA
SS&C Blue Prism
------------------------------