Excel VBO Zahlen und Text Umwandlungsprobleme

[MarcoMatuttis1]

Ich hab ein Problem mit der Umwandlung von Zahlen und Texten in Excel über Blueprism.

SAP öffnet mir über die Exportfunktion eine Excel Instanz mit Daten.

Die Daten so wie sie im Excel drin sind, sind vom Format her korrekt.

Wenn ich die Datei jetzt über Excel VBO - Save Workbook as  speichere als Text (Tabstopp-getrennt) dann versaut mir Excel die Zahlenformate und fügt einigen Texten noch Anführungszeichen hinzu.

Screenshot dazu:

Das Datum in der Spalte Erfaßt am wurde aus 22.12.2021 zu 12/22/2021. Das hab ich korrigieren können indem ich vor dem Speichern die Zellen als MM.DD.YYYY formatiert habe. Deswegen ist es auch geschwärzt.

Aus normalen Text aus der Excel Datei oben wird Text in Anführungszeichen unten. Nicht bei allen Einträgen aber bei manchen.

Bei den Zahlen in der Spalte Wert/Währ wird aus dem Zahlenformat mit Punkt als Tausendertrennzeichen und Komma als Dezimaltrennzeichen genau das umgekehrte Format. Zudem packt er jede Zahl bei der das Tausendertrennzeichen Komma vorkommt in Anführungszeichen.

Weiß jemand wie ich das Problem beheben kann, zumindest mit den Zahlen ?

Das Anführungszeichen Problem lässt sich wohl beheben in dem ich einfach suchen und ersetzen auf die Textdatei anwende und Anführungszeichen mit nichts ersetze.

Mein Ausgabeformat in der Textdatei muss  aber zwingend Tausendertrennzeichen Punkt und Dezimaltrennzeichen Komma haben.

Das ist für die Weiterverarbeitung der Datei Pflicht.

[ewilson]

@MarcoMatuttis1,

I have a feeling those items are being enclosed in quotes because the default column separator on your system is the comma for a CSV. You could try changing the locale settings, or if you're handy with VBA you could change the code stage to address this. Take a look at this thread on Stack Overflow. You'll want to add the "Local:=true" portion to the Save As code. 

Cheers,
​

[MarcoMatuttis1]

@ewilson​

Thank you for your reply. Unfortunately I'm not sure if that "Local:=True" will help me with my problem. At the moment I'm facing this problem on 2 different cases. At least I think it is kinda the same problem.

In my example from above I'm saving the excel as a text file with tab separated values. My Windows 10 regional settings are german, my Excel also runs on german, only blue prism runs in "English UK" Settings. In my Excel sheet the numbers and dates have the right format. But when I use the Excel VBO save as function it will save the values in a different format. From the german number format in the Excel sheet 1.016,40 it will save it as "1,016.40" in the tab separated text file.

My suspicion is, that the bluebrism VBO somehow mixes the language settings and for some reason the result is not longer interpreted as a number but as text because it writes it in quotation marks in this text file.

I have copied the source from the Excel VBO:

newworkbookname = ExecWithTimeout(Timeout, "Save Workbook As",
Function()
    Dim wb as Object  = GetWorkbook(handle,workbookname)
    Dim excel as Object = wb.Application

    excel.DisplayAlerts = False
    wb.SaveAs(filename,20)
    excel.DisplayAlerts = True

    Return wb.Name
End Function)

In another process I have a similar problem with the save workbook. I use the format cells action to format some cells explicit as numbers. Then I copy these cells cells from workbook a to workbook b. when i open workbook b afterwards and sort it then the new cells that I just copied are not in the right order because excel does not interprete these numbers as real numbers. I also have the feeling that this also has something to do with the language settings.

Heres the normal Save Workbook:

ExecWithTimeout(Timeout, "Save Workbook",
Sub()
    Dim wb as Object = GetWorkbook(handle,workbookname)
    Dim excel as Object = wb.Application
    excel.DisplayAlerts = False
    Invoke(wb, "Save")
    excel.DisplayAlerts = True
End Sub)

Do you know where I should put that local:=true in these 2 code stages that I can test this ? Sadly I'm not experienced with Visual Basic at all 

[MarcoMatuttis1]

@ewilson

Here is my other problem:

I have this Excel Sheet. Until Row 29850 is old data. From A29851 to M32800 is data that I copied from another worksheet with the MS Excel VBO - Copy and Paste Worksheet Range Action.

In the coloums AA to AN is an SVERWEIS (VLOOKUP in English i think) formula looking like this for example:

=SVERWEIS($A32761;'C:\bothomedir\VMST\O\Bereich\CO\Kostenplanung\Datenbasis\[Listenfelder_2021.xlsx]Vertriebsstruktur'!$A$7:$G$303;3;FALSCH)

​In the old data from row 29850 upwards everything works fine because the formula in AA to AN recognize the data in coloumns A and B correctly.

In this example you can see that the numbers that are in Cell A29851 (red circle cell) is not recognized properly because excel shows #NV in these Cells from AA to AN.

When I know click in to the formula of A29851 and press enter the number is getting properly recognized as a number and the cells AA to AN display the right information.

I stole a macro from some website that goes through all the cells and does exactly that.

Sub RefreshAllCells()
Dim r As Range, rr As Range
Set rr = Selection
For Each r In rr
    r.Select
    Application.SendKeys "{F2}"
    Application.SendKeys "{ENTER}"
    DoEvents
Next
End Sub

​Unfortunately this macro takes like 40 minutes to go through all the new 3000 rows to refresh the cells. Recalculation or refreshing the worksheet did not work.

When I copy the data from the worksheet A to B by hand. With mouse and keyboard. mark cells. right click, select copy and paste it into the other will all the numbers are recognized correctly in worksheet B. But when I use the VBO Action it does not work properly. Also formatting the cells afterwards by hand or blueprism will not work. I have to click every single cell and press enter for it to work::

Got a solution for that as well ?

[ewilson]

@MarcoMatuttis1,

You've got quite the list of issues. 😂​ Lets start with the most basic. You mentioned that your OS and Office applications are all running in German locale. Have you considered running the German localized version of Blue Prism? What version of Blue Prism are you running? Are you using the Excel VBO that shipped with your specific software, or have you downloaded the latest Excel VBO from the Digital Exchange?

Cheers,
Eric

[MarcoMatuttis1]

I'm running Blue Prism 6.10.1 and I'm using the excel VBO that came with it.

For the first problem in my opening post I changed the blue prism language to german and tested if it would make a difference in exporting these dates and numbers. Unfortunately it didn't solve my problem.

Since the processes that are now running in production have been created with the english version of blueprism (we started blueprism in our company when there was no german version) I had to stick to it because there were many errors when I switched the language to german for testing because there are many actions that would require adjustments to the german language in the application manager.

I kinda solved the first problem now. I created a custom Excel object and use blueprism to do the stuff like I would do it by hand. Move the mouse and Click Save file as by mouse etc.

Nevertheless I was hoping to solve my problem with the provided Excel VBO actions. 

Unfortunately I can not use the same method to solve my problem number 2 because iam not able to mark cells and copy stuff from a to b by using blue prism to navigate mouse and keyboard actions.

[ewilson]

@MarcoMatuttis1,

On the first issue here's what I would suggest trying.​

1. Create a copy of the Save Workbook As action and name it something like Save Workbook As CSV.
2. In the new action rename the Code stage to SaveWorkbookAsCSV, otherwise you get a stage name conflict.
3. Open the code stage and change the line wb.SaveAs(filename) (should be line 11) to look like this - wb.SaveAs(Filename:=filename, FileFormat:=62, Local:=True)

The FileFormat:=62 portion basically instructs Excel to specifically save the file in xlCSVUTF8 format. If you want to use regular old CSV (i.e. xlCSV), change the 62 to a 6 (ex. FileFormat:=6).

Give that a try and see if there's a difference.

Cheers,
Eric
​

[MarcoMatuttis1]

There seems to be a slight misunderstanding. I don't need the export as .csv, i need it as tab stop seperated textfile. 

I already made a duplicate of the original Save Workbook as Action and made a slight edit to it. As far as I have read on various websites the right file format for tab stop separated values is 20 (see here: https://docs.microsoft.com/en-us/office/vba/api/excel.xlfileformat and https://stackoverflow.com/questions/39766963/excel-vba-save-as-tab-delimited-file ).

I added your suggeestion and now my code stage looks like this:

The Textfile I generated with this passes the eye test. All the numbers have the right format and also the texts no longer have quotation marks, I will test the textfile later if it passes the next step of our process. But as far I can tell. This worked. Thank you very much!

EDIT

i just noticed that the images I upload are not clickable thumbnails. I edit my post above describing my 2nd problem so that you can see the actual screenshot.

[ewilson]

@MarcoMatuttis1,

I’m glad to hear we may have resolved your initial issue.

Regarding the second issue with copy-and-paste, if you click on the column header of one of the problem columns (so the entire column of data is selected) and set the formatting to Number does that have any effect?​​ What about if you do that before the new data is copied into the workbook?

Cheers,
Eric

[MarcoMatuttis1]

Unfortunately not. I tried formating single cells, ranges and even the whole coloumn. I also tried formating the cells in worksheet A before i copy and paste them to worksheet B.
I can even change the cell format to text and then back to number and it will not trigger the effect.

Only when I click in the area where I can edit the formula and then press enter it actually "updates" the cell.