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]

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


Cheers,
Eric

[ewilson]

@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

​

[MarcoMatuttis1]

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

[MarcoMatuttis1]

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.

[MarcoMatuttis1]

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 ?

[JanPiwowarski]

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

[ewilson]

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

[MarcoMatuttis1 · Accepted solution]

@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

[ewilson]

@MarcoMatuttis1,

Outstanding! Glad you have a solution. 😉

Cheers,
Eric​