CODEWITHSUNDEEP
excelvbaexcel-formula
MmVv
MmVv

Reputation: 553

VBA - comma/dot number format (US > EU (German))

Got a piece of code where are some parameters that I want to have with (,) instead of (.) What my code is doing, taking some system specific files (.dif) and convert them into .txt , but there I managed to convert Column A to certain date format (instead of US format), but problem is with another Columns. Column M to Column T are "Temperatures", and US format is with (.), so you have for instance 25.5 C or German (EU) is with (,) 25,5 C.

p.s. Temperatures are marked with T11, T21, T31 and so on...

What I wanted is to convert them all to Comma and I am not managing with .NumberFormat or changing it in Excel > Advanced > 1000 separator options.

I am getting .txt file but with still (.) and that makes me always unnecessary trouble when importing it to Power Query /BI > have to always remove previous step (not a lot of people is familiar with PBI and Editor, and when you say to someone to take care of it...people forgetting easily because you have lot of steps to do..)

This is part of the code: 

Sub DoFolder(Folder, targetFolder As String)
Dim Workbook
Dim SubFolder

For Each SubFolder In Folder.SubFolders
        DoFolder SubFolder, targetFolder
Next
Dim File
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each File In Folder.Files

    ' *** Operate on each file ***
    Set Workbook = Workbooks.Open(File)

    ' *** Sort, filter and rename each file/column ***
    If Workbook.FileFormat = -4158 Then
        Set Workbook = Workbook.ActiveSheet
            'Workbook.Columns("D:E").EntireColumn.Delete
            'Workbook.Columns("E").EntireColumn.Delete
            'Workbook.Columns("R:T").EntireColumn.Delete
            Workbook.Rows("1").Delete

            Range("A1:W1").Value = Array("Date/Time", "P0 [mbar]", "P1 [mbar]", "P3 [mbar]", "P7 [mbar]", "PS160 [mbar]", "P190 [mbar]", "P220 [mbar]", _
            "Q1 [ppb]", "Q2 [ppb]", "Q3 [ppb]", "Q4 [ppb]", _
            "T11 [°C]", "T21 [°C]", "T31 [°C]", "T12 [°C]", "T22 [°C]", "T32 [°C]", "T01 [°C]", "T02 [°C]", "FM1 [slm]", "FM2 [slm]", "FM3 [slm]")

            Workbook.Columns("A").NumberFormat = "dd.mm.yyyy hh:mm"
            'Workbook.Columns("M").NumberFormat = "#.###"
            'Workbook.Columns("N").NumberFormat = "#.###"
            'Workbook.Columns("O").NumberFormat = "#.###"
            'Workbook.Columns("P").NumberFormat = "#.###"
            'Workbook.Columns("Q").NumberFormat = "#.###"
            'Workbook.Columns("R").NumberFormat = "#.###"
            'Workbook.Columns("S").NumberFormat = "#.###"
            'Workbook.Columns("T").NumberFormat = "#.###"


    ' *** Preparing new folder for filtered/sorted files ***
        Dim FLDR_NAME As String
        FLDR_NAME = Mid(Application.ActiveWorkbook.Path, InStrRev(Application.ActiveWorkbook.Path, "\") + 1)
        Dim fso As Object
        Set fso = CreateObject("Scripting.FileSystemObject")

        FLDR_NAME = targetFolder & "\" & FLDR_NAME
        If Not fso.FolderExists(targetFolder) Then
               fso.CreateFolder (targetFolder)
        End If

        If Not fso.FolderExists(FLDR_NAME) Then
               fso.CreateFolder (FLDR_NAME)
        End If

    ' *** Save As Converter_Converted as separate file ***
        Dim newFileName As String
        newFileName = FLDR_NAME & "\" & Workbook.Name & ".txt"
        Application.DisplayAlerts = False
        Workbook.SaveAs FileName:=newFileName

    ' *** Close and SaveAs ***
        Application.ActiveWorkbook.Close
    End If
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

After converting with VBA

PowerBI (Editor)

This in PBI Editor is auto- change type, what I dont wanna everytime to have.

As it should be!

On last picture from PBI, is actually what I wanted, but without further steps of replacing values and changing values.

Upvotes: 0

Views: 1575

Answers (2)

MmVv
MmVv

Reputation: 553

So, I found out finally something (for me at least is hell of a help)!

I way playing in PowerQuery Editor, and also with those formats, regional settings and so on... the quickest and easiest way to have your data "set" in adquate format (worked in my case - living in Germany), is once when you uploaded your data into editor, just go in Options > Regional settings > and Locale into English (UK) and hit REFRESH into the Editor - this one works flawlessly in my case, cant figure out 100% why Germany as locale doesnt work properly, but putting another Locale with one click is better than "remove steps" and format everyhting.

What I wanna say, it recognizes your data as long as your data set is homogenous and without any discrepancies...and again, it is not needed to change completely your regional settings on PC, just upload the data and during that you can alaways change into Editor everything you need.

enter image description here

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60334

IF your Windows Regional System setting is using comma for the decimal separator, and IF you want to convert the US-centric decimal numbers to your system, then you should be using the Change Type using Locale, and specify English(United States) en-US as the locale.

Upvotes: 1

Related Questions