Excel VBO once again - format numbers as text to real numbers

[MarcoMatuttis1]

I have a .xlsx file that I get from a SAP Export. When I open the file it shows the little autocorrect indicator in almost all the cells.

see screenshot:

When I hover the yellow exclamation mark indicator i get the following message:

translation: The number in this cell is formated as text or an apostrophe is before that number.

I can fix this error very fast when I do it manually. I click the exclamation mark and select convert the text to number and within seconds all the 25000+ cells are corrected. But I can't get blueprism to do the same.

Is there any way I can do this with the whole worksheet with e.g. excel vbo ?

For some reason I can not automate excel like I would automate other applications. I can't spy certain buttons and screen elements reliably.

Unfortunately I also can not change the output of SAP to deliver myself a perfect formatted excel sheet to begin with. I must correct these cells containing number as string myself.

Any advice, hint or solution ?

------------------------------
Marco Matuttis
Robot Process Automation Developer
INTER Krankenversicherung AG
Mannheim
------------------------------

[John__Carter]

Hi Marco

One option would be to extend the Excel VBO with a new page that performs the formatting. For this I usually start by recording a macro so I can study the VBA. Then it's a case of replicating the functionality in a code stage using .Net. A quick Google suggest the code will be something like this

Sheets("Sheet1").Cells.NumberFormat = "#,##0.00"
or
Sheets("Sheet1").Range("G:K").NumberFormat = "#,##0.00"

If you only need to read the file then maybe you could try using an OLEDB query instead of opening it in Excel.

------------------------------
John Carter
Professional Services
Blue Prism
------------------------------

[MarcoMatuttis1]

Thank you John for your Hint.

The Number format command did not work but I actually found something in Excel which seems promising.

I tried to capture the command and syntax by recording a macro and get this:

Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True

With this I'm able to adjust the complete column at once. But I struggle with the syntax when I put it in a codestage.

My codestage looks like this:

(to begin with i duplicated the select codestage)

Dim wb, ws As Object
Dim excel, sheet, range As Object

Try

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

wb.Activate()
ws.Activate()
excel = ws.Application
sheet = excel.ActiveSheet
range = sheet.Range(Reference)
range.Select()

range.TextToColumns(DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), 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

But I get several errors when I check the code:

I'm not experienced at all with visual basic and vba for excel at all. So I just work with "trial and error" so far. in this case I don't even really know what xlDelimited and clDoubleQute really means ...

------------------------------
Marco Matuttis
Robot Process Automation Developer
INTER Krankenversicherung AG
Mannheim
------------------------------

[MarcoMatuttis1 · Accepted solution]

Ok it seems I found a solution that works.

Dim wb, ws As Object
Dim excel, sheet, range As Object

Try

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

wb.Activate()
ws.Activate()
excel = ws.Application
sheet = excel.ActiveSheet
range = sheet.Range(Reference)

range.TextToColumns(Destination:=range,ConsecutiveDelimiter:=False, DataType:="1", TextQualifier:="1", Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=False, 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

------------------------------
Marco Matuttis
Robot Process Automation Developer
INTER Krankenversicherung AG
Mannheim
------------------------------