topic Re: Excel VBO Zahlen und Text Umwandlungsprobleme in Deutschsprachige Community

Excel VBO Zahlen und Text Umwandlungsprobleme

MarcoMatuttis1 — Fri, 05 Nov 2021 08:49:13 GMT

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.

Re: Excel VBO Zahlen und Text Umwandlungsprobleme

ewilson — Fri, 05 Nov 2021 11:40:07 GMT

@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,

Re: Excel VBO Zahlen und Text Umwandlungsprobleme

MarcoMatuttis1 — Wed, 10 Nov 2021 07:52:06 GMT

@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

Re: Excel VBO Zahlen und Text Umwandlungsprobleme

MarcoMatuttis1 — Wed, 10 Nov 2021 13:25:05 GMT

@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 ?

Re: Excel VBO Zahlen und Text Umwandlungsprobleme

ewilson — Wed, 10 Nov 2021 14:25:32 GMT

@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

Re: Excel VBO Zahlen und Text Umwandlungsprobleme

MarcoMatuttis1 — Wed, 10 Nov 2021 14:31:01 GMT

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.

Re: Excel VBO Zahlen und Text Umwandlungsprobleme

ewilson — Wed, 10 Nov 2021 14:59:06 GMT

@MarcoMatuttis1,

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

Create a copy of the Save Workbook As action and name it something like Save Workbook As CSV.
In the new action rename the Code stage to SaveWorkbookAsCSV, otherwise you get a stage name conflict.
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

Re: Excel VBO Zahlen und Text Umwandlungsprobleme

MarcoMatuttis1 — Thu, 11 Nov 2021 07:44:07 GMT

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.

Re: Excel VBO Zahlen und Text Umwandlungsprobleme

ewilson — Thu, 11 Nov 2021 11:40:53 GMT

@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

Re: Excel VBO Zahlen und Text Umwandlungsprobleme

MarcoMatuttis1 — Thu, 11 Nov 2021 13:20:44 GMT

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.

Re: Excel VBO Zahlen und Text Umwandlungsprobleme

ewilson — Thu, 11 Nov 2021 13:55:56 GMT

Ok, and can you verify that on the Formulas menu the Calculation Options are set to Automatic?

Cheers,
Eric

Re: Excel VBO Zahlen und Text Umwandlungsprobleme

ewilson — Thu, 11 Nov 2021 14:24:30 GMT

@MarcoMatuttis1,

Here's something that might work for you. In the Copy and Paste Worksheet action that you're using to paste the source data into the destination, open the Code stage (should be titled Paste Worksheet). The code should look something like this:

Dim sw, dw As Object
Dim ss, ds As Object
Dim excel, sheet, source, destination, cells, cell As Object

Try

sw = GetWorkbook(Handle, Source_Workbook)
dw = GetWorkbook(Handle, Destination_Workbook)

ss = GetWorksheet(Handle, Source_Workbook, Source_Worksheet)
ds = GetWorksheet(Handle, Destination_Workbook, Destination_Worksheet)

sw.Activate()
ss.Activate()
excel = ss.Application
sheet = excel.ActiveSheet
cell = excel.ActiveCell

If Source_Range="" Then
	cells = sheet.Cells
	Destination_Range = "A1"
Else
	cells = sheet.Range(Source_Range)
End If

cells.Select()
source = excel.Selection
source.Copy()
cell.Select()

dw.Activate()
ds.Activate()
sheet = excel.ActiveSheet
cell = excel.ActiveCell
destination = sheet.Range(Destination_Range)
destination.Select()
sheet.Paste()
cell.Select()

My.Computer.Clipboard.Clear()

Success = True

Catch e As Exception
	Success = False
	Message = e.Message
Finally
	sw = Nothing
	ss = Nothing
	dw = Nothing
	ds = Nothing
	excel = Nothing
	sheet = Nothing
	source = Nothing
	destination = Nothing
	cells = Nothing
	cell = Nothing
End Try

Just after line 38, which should be cell.Select(), and before line 40, which should be My.Computer.Clipboard.Clear(), add this line: ds.Calculate(). The resulting code should now look like this:

Dim sw, dw As Object
Dim ss, ds As Object
Dim excel, sheet, source, destination, cells, cell As Object

Try

sw = GetWorkbook(Handle, Source_Workbook)
dw = GetWorkbook(Handle, Destination_Workbook)

ss = GetWorksheet(Handle, Source_Workbook, Source_Worksheet)
ds = GetWorksheet(Handle, Destination_Workbook, Destination_Worksheet)

sw.Activate()
ss.Activate()
excel = ss.Application
sheet = excel.ActiveSheet
cell = excel.ActiveCell

If Source_Range="" Then
	cells = sheet.Cells
	Destination_Range = "A1"
Else
	cells = sheet.Range(Source_Range)
End If

cells.Select()
source = excel.Selection
source.Copy()
cell.Select()

dw.Activate()
ds.Activate()
sheet = excel.ActiveSheet
cell = excel.ActiveCell
destination = sheet.Range(Destination_Range)
destination.Select()
sheet.Paste()
cell.Select()

ds.Calculate()

My.Computer.Clipboard.Clear()

Success = True

Catch e As Exception
	Success = False
	Message = e.Message
Finally
	sw = Nothing
	ss = Nothing
	dw = Nothing
	ds = Nothing
	excel = Nothing
	sheet = Nothing
	source = Nothing
	destination = Nothing
	cells = Nothing
	cell = Nothing
End Try

This should should force a recalculation of all formulas on the worksheet. If that doesn't work, there's another VBA function called CalculateFullRebuild() which forces a full calculation of the data and rebuilds the dependencies for all open workbooks. That's only available on the Application object though.

Cheers,
Eric

Re: Excel VBO Zahlen und Text Umwandlungsprobleme

MarcoMatuttis1 — Thu, 11 Nov 2021 14:37:43 GMT

Thank you Eric. I will try your possible solution and provide feedback tomorrow.

Re: Excel VBO Zahlen und Text Umwandlungsprobleme

MarcoMatuttis1 — Fri, 12 Nov 2021 14:34:00 GMT

Unfortunately the suggestion did not work. I have a suspicion that the data is somehow copied without using the right format.

But changing the format of the source cells before copying nor changing the format after copying seems to work right unless I click in that field and press enter or overwrite the cell with the exact same value by hand.

Anyways, its friday and i have enough for this week. Have a nice weekend. Maybe we can find a solution on monday.

Re: Excel VBO Zahlen und Text Umwandlungsprobleme

MarcoMatuttis1 — Wed, 17 Nov 2021 15:09:21 GMT

I think I have a possible solution that might work, I just need a little help with it.

The target excel worksheet file is a file that has no macros. (.xlsx)

So I can not add the Macro I mentioned above to the file and execute it. The runtime was extremely long but it worked after all so it is my only solution at the moment.

I just need to execute the excel macro as code stage or lets say build a code stage that does the same as the excel macro would do.

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

Anyone here that is familiar enough with visual basic to create a page in the excel vbo which does exactly that ?

Re: Excel VBO Zahlen und Text Umwandlungsprobleme

al2piwo — Wed, 17 Nov 2021 15:48:43 GMT

Hi Marco,

es scheint als wenn Du versuchst die fehlerhafte Kodierung in Excel mithilfe von Workarounds zu beheben. Die Formatierung hat aber vermutlich vor dem Release Change auf BP6.10.1 funktioniert? Falls ja, schau Dir mal den Known Issue BP-5825 an und ob der temporäre Lösungsvorschlag Dir helfen kann. Hier auch mal ein wertvoller Hinweis den es von BP dazu gibt: "

Following that, once you select English again, Blue prism will act as if the system is set to English, including locale formatting.

If the language selected in BP matches the "Windows display language", then the "locale format" is based on the OS's "Region format".
If the language selected in BP does not match the "Windows display language", then the "locale format" is based to the selected BP language.

This cannot be automatically be set to English however and must be selected from the option, this will update the User.config file found in "C:\Users\<USERNAME>\AppData\Roaming\Blue Prism Limited\Automate V3" so the setting will persist for each subsequent login"

Ggf. hast Du aufgrund eines zugrunde liegenden Charsets auch bei anderen Aktionen bereits Probleme bemerkt, falls dem so ist kann dieser Workaround ggf. helfen.

Grüße
Jan

Re: Excel VBO Zahlen und Text Umwandlungsprobleme

ewilson — Wed, 17 Nov 2021 16:02:54 GMT

Hi @MarcoMatuttis1,

Here's what I did:

Create a copy of an existing action like Go To Cell. Change the name of the new action to something like Refresh Cell Range. Here's what mine looks like:

There are two input parameters to the action:

handle - this is the numeric identifier of the workbook
Cell Range - this is a text value that represents the range of cells that you want to update (ex. A1:A50).

You pass these values into the code stage, and here's what my code stage looks like:

Dim wb As Object
Dim r As Object, cr as Object

wb = GetWorkbook(handle, "")
wb.Activate()

cr = wb.ActiveSheet.Range(cellRange)

For Each r In cr
    r.Select
    wb.Application.SendKeys("{F2}")
    wb.Application.SendKeys("{ENTER}")
Next

DoEvents is not a core method of VB.NET unless you add a reference to the System.Windows.Forms.dll library. I don't think it's really necessary here.

When you're ready, call the action and pass in the handle of the specific Excel workbook you opened with the VBO and the range of the cells that need to be refreshed.

There are various things you could do to change this up like passing in a specific worksheet name too. The code above just grabs whatever the active worksheet is on the specific workbook.

Cheers,
Eric

Re: Excel VBO Zahlen und Text Umwandlungsprobleme

MarcoMatuttis1 — Fri, 19 Nov 2021 12:30:48 GMT

@ewilson

Thanks again for your support.

I took your script and it did not change the values. But as I was watching the codestage run selecting each single cell and jumping to the next. The codestage did not exactly the same as when I do it by hand. So I tried not only Select on the Cell but also Activate. Also Activate did not help but I accidently found something on stackoverflow.

My Script now looks like this:

Dim wb As Object
Dim r As Object, cr as Object

wb = GetWorkbook(handle, "")
wb.Activate()

cr = wb.ActiveSheet.Range(cellref)

For Each r In cr
    r.Select
	r.Value = r.Value	
Next

after reassigning / copying with r.Value=r.Value it just works... and its much faster as the macro

Re: Excel VBO Zahlen und Text Umwandlungsprobleme

ewilson — Fri, 19 Nov 2021 13:19:32 GMT

@MarcoMatuttis1,

Outstanding! Glad you have a solution. 😉

Cheers,
Eric