Change comma to dot, different regional settings

[tigergutt]

Hi

We have created a work-around in excel to generate some updated data that we can copy from this sheet and paste directly into our cloud software. An issue that we discovered was some users have comma as seperator or decimal definer, whilst other use DOT. The DOT is how the cloud software write it's numbers, so pasting anything different results in invalid data and nothing works.
We decided to try and automate this, as we expect many users either don't want to changed their settings, or they simply don't understand how to...

I tried a simple formula like this: Range("A5:L10000").Replace What:=",", Replacement:="." But this does not work, when settings are using comma, it just won't change the "," to "." (if i use DOT as my settings, then this formula works fine, the problem is when comma is your setting)

Dr. Google did have many suggestions, but nothing that I could make work.

As an additional note, to test it on my machine I changed settings in Excel-Options-Advanced the decimal separator to be "," so I could try and test this locally.
I fear, if the issue is also possibly related to system settings, this could further complicate things.

Any comments and advice is much appreciated!

DrillPlan Operating Window NO SF_Final.xlsm

[ByteMarks]

Try adding this function to one of the modules

Function DStoDOT(OnOff As Boolean)
    With Application
        .UseSystemSeparators = Not OnOff
        .DecimalSeparator = "."
    End With
End Function

Then in your copy code

Sub CopyOW_FG()
'
' CopyOW_FG Macro
'
    DStoDOT (True)
    
    Range("A5:C5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    DStoDOT (False)
End Sub

[CK76]

In each of your Copy code. You can set up something like below.

Dim OrigSep, OrigSys
With Application
    OrigSep = .DecimalSeparator
    OrigSys = .UseSystemSeparators
    .DecimalSeparator = ","
    .UseSystemSeparators = False
End With
' Your code

With Application
    .DecimalSeparator = OrigSep
    .UseSystemSeparators = OrgSys
End With

I don't like doing something like this and prefer to enforce same config for software across organization. But if that can't be done. Using code above will change Excel's separator setting for the code/process and then change it back.

[tigergutt]

Hi CK76
And thanks for you reply!

Sadly I am not that clever, and I don't get how to input my code into your suggestion above. Is it possible to ask for a complete example for one of the copy codes?

[tigergutt]

Hi ByteMarks

And thanks for you input!
It does not paste with DOT, still pasts the commma, I have to disable "DStoDOT (False)", but then settings are changed for the user, which isn't ideal.
Is there a way to run "DStoDOT (False)" when the workbook is closed?

[CK76]

Basically much the same as Bytemarks' example.

Sub CopyWI_PP()
    Dim OrigSep, OrigSys
    With Application
        OrigSep = .DecimalSeparator
        OrigSys = .UseSystemSeparators
        .DecimalSeparator = ","
        .UseSystemSeparators = False
    End With
    
    Range("J5:L5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    With Application
        .DecimalSeparator = OrigSep
        .UseSystemSeparators = OrgSys
    End With
End Sub

[tigergutt]

Thanks again CK76, but unfortunately I am at the same obstacle. It is still pasting with comma.

[PrizeGotti]

-Misread, thought the post was about delimiters

[PrizeGotti]

Sub Comma2Dot()
    Dim cell As Range
    
    For Each cell In Range("A5:C5")
        cell.Value = Replace(cell.Value, ",", ".")
    Next cell
End Sub

Change the range to the required range and run before copying.

Tested and worked without issues.

[Artik]

PrizeGotti, when the decimal separator in Windows is a comma, trying to replace the comma with a dot with a macro won't do anything. The result will continue to be numbers with a comma.
On the other hand, with this system setting (i.e. comma) this code

Range("A5:L10000").Replace What:=".", Replacement:="."

converts dots to commas. Yes, yes, there is no mistake, we are converting a dot to a dot, and the result will be numeric values with a comma.

Artik

[PrizeGotti]

Weird, I'm using the system settings (Dot) and it worked fine on my machine, and was able to convert the dot to a comma (I swapped them around in the replace command).

When I disabled using the system settings and switched it to a comma, it didn't work like you said. When I also changed my system to a comma in general, it also didn't work. But as soon as I put my system back to a dot, it had no issues converting those dots into commas.

Strange that it works one way, but not the other.

[ByteMarks]

It seems that when the dot is changed back the clipboard is also modified.


Here is a different approach.
The dot is currently a comma because my regional setting is a dot, so the effect is reversed.

in Thisworkbook

Const SheetName$ = "Find_Replace"
Const aDot$ = "," 'change to a dot in reality. set to a comma for testing when regional is a dot

Private Sub Workbook_Activate()
With ActiveSheet
    If .Name = SheetName Then Call ChangeSeparator(.Name)
End With
End Sub

Private Sub Workbook_Deactivate()
Application.UseSystemSeparators = True
End Sub

Private Sub Workbook_SheetActivate(ByVal sh As Object)
Call ChangeSeparator(sh.Name)
End Sub


Sub ChangeSeparator(shN$)
Dim blnUseDot As Boolean
blnUseDot = (shN = SheetName)

With Application
    .UseSystemSeparators = Not blnUseDot
    If blnUseDot Then .DecimalSeparator = aDot
End With
End Sub

[Artik]

Weird, I'm using the system settings (Dot) and it worked fine on my machine, and was able to convert the dot to a comma (I swapped them around in the replace command).
(...)
Strange that it works one way, but not the other.

I will explain how the Range.Replace method works when the decimal separator in Windows is a comma.
We want to replace dots with commas. Why then do we use dot to dot replacement in the code? Because the method when converting numbers is guided by the US separator system, where the decimal separator is a dot and the thousands separator is a comma. The actual dot from the cell is converted to a dot, but this is still in VBA. In the next step, the value from VBA is transferred to Excel. And at this point, the value with the dot is interpreted as a US decimal number notation, so there is an implicit conversion from US to local system at the VBA-Excel border. For this reason, converting the comma to a period by code fails in systems where the decimal separator is a comma; simply implicit conversion converts this back to a number in the local system. The same operation done manually using the Find and Replace tool converts a comma to a dot without any problems.
In English-speaking systems there is no such problem because a number with a decimal dot is compatible with the US notation of numbers.
PrizeGotti, sorry for the confusion. Only now did I notice that you used the VBA.Replace function, not the Range.Replace method. The above description is of course about the method. Although the VBA function will also cause some conversion problems. After 1 returns the results of your Comma2Dot procedure. After 2 returns the results after a slight modification:

cell.Value = "'" & VBA.Replace(cell.Value, ",", ".")

i.e., forcing the text format of the cell.

Artik

Pic1.png

[tigergutt]

Thanks all for you interesting inputs and discussions. Highly appreciated!
I have gone for something second best, but I hope for now this will work, basically what i have done is using the original suggestion from ByteMarks, but added a msgbox to allow user to paste the data bore the code returns the comma/DOT settings.
Simple, but I would not have manged without your support, so thanks again!

Sub CopyOW_FG()
'
' CopyOW_FG Macro
'
    DStoDOT (True)
'
    Range("A5:C5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    MsgBox "Paste you data in DrillPlan Operating Window before clicking OK"
    
    DStoDOT (False)
End Sub

[ByteMarks]

Thanks for the update.