XL 2016 Séparateurs décimales & milliers sur un import csv

[sabrina_]

Bonjour à tous,

Voilà mon tout premier post !
Je débute en VBA, et le forum a déjà répondu à plusieurs problématiques rencontrées, mais là je coince.

J'ai créé un outil Excel qui permet d'importer 2 fichiers csv via Power Query et qui va "analyser" le tout.
Les fichier sont importés de nos systèmes US.

Dans ma société nous avons plusieurs environnement différents avec des langues US, FR, etc..
Mon PC est en Français, séparateurs systèmes cochés, mais quand j'extrais mes csv de nos systèmes encodés en 12:00, les décimales sont des points.
J'ai dupliqué ma macro en créant un import Français & un Anglais, en utilisant "Application.International(xlCountryCode)" pour diriger les personnes ayant une version US (leur csv sont encodés en 12:52) vers la bonne macro.
Jusque là, ça fonctionne bien.

En revanche, pour certaines personnes qui ont des cvs 12:00 comme moi, et PC français, l'import des chiffres ne se fait pas car leur séparateur de décimal est en virgule. Du coup les colonnes ne se remplissent pas... L"Application.International(xlCountryCode)" ne fonctionne pas comme ils sont aussi en Français.

J'ai modifié la query avec leur paramètres, et cela fonctionne.
Mais...J'arrive pas à "détecter" en amont le "séparateur" pour lui dire :
- Si version anglaise alors macro N°Z
- Si version française mais séparateur "." alors macro N°X
- Si version française mais séparateur "," alors macro N°Y

Je me suis penchée sur Application.DecimalSeparator & Application.ThousandsSeparator mais j'arrive pas "diriger" ma macro. Je crois que j'arrive pas à m'en servir tout court.
Impossible de remplacer en amont la "," par le "." en amont comme cela passe directement par Power Query.

Voici le code qui fonctionne chez moi :

Public Sub ImportSPRFR()
Dim FileToLoad As String
Dim spr_sheet As String
Dim ConnexionName As String
Dim StrFormula As String

FileToLoad = SelectedExcel  'chemin du fichier SPR à charger - Demande à l'utilisateur de choisir le fichier SPR
spr_sheet = "SPR" 'Nom de la feuille contenant les données du SPR


'Ne pas modifier les lignes suivantes
ConnexionName = "SPR"
StrFormula = "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""" & FileToLoad & """),[Delimiter=""#(tab)"", Columns=9, Encoding=1200, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Utiliser la première ligne pour les en-têtes"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Modifier le type"" = Ta" & _
        "ble.TransformColumnTypes(#""Utiliser la première ligne pour les en-têtes"",{{""Territory"", type text}, {""Payout Measure Name"", type text}, {""Detail Measure Name"", type text}, {""QTD Posted Revenue"", type number}, {""PY Qtr Rev (Gate Goal)"", type number}, {""Gate Attainment %"", type number}, {""Estimated YOY Growth"", type number}, {""Quarterly Goal"", type n" & _
        "umber}, {""Quarterly Attainment %"", type number}})," & Chr(13) & "" & Chr(10) & "    #""Fractionner la colonne par délimiteur"" = Table.SplitColumn(#""Modifier le type"", ""Territory"", Splitter.SplitTextByDelimiter("" | "", QuoteStyle.Csv), {""Territory.1"", ""Territory.2"", ""Territory.3""})," & Chr(13) & "" & Chr(10) & "    #""Type modifié"" = Table.TransformColumnTypes(#""Fractionner la colonne par délimiteur"",{{""" & _
        "Territory.1"", type text}, {""Territory.2"", type text}, {""Territory.3"", Int64.Type}})," & Chr(13) & "" & Chr(10) & "    #""Colonnes renommées"" = Table.RenameColumns(#""Type modifié"",{{""Territory.1"", ""Territory""}, {""Territory.2"", ""Employee Name""}, {""Territory.3"", ""ID FedEx""}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Colonnes renommées"""
 

On Error GoTo ErrMgt

'On essaie de créer une nouvelle connexion
    ActiveWorkbook.Queries.Add Name:=ConnexionName, Formula:=StrFormula
   
    With Sheets(spr_sheet).ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=SPR;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [SPR]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = False
        .ListObject.DisplayName = "_SPR"
        .Refresh BackgroundQuery:=True
    End With
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=True
Exit Sub

ErrMgt:
If Err.Number = -2147024809 Then
'Si la requete existe déjà et provoque une erreur, alors on modifie la requete existante

ActiveWorkbook.Queries(ConnexionName).Formula = StrFormula
   
Worksheets(spr_sheet).ListObjects("_SPR").Refresh

Else
    MsgBox Err.Number & " - " & Err.Description
    Exit Sub
End If

A chaque fois, c'est le "StrFormula" qui pose problème.

En remplaçant cette partie pour les autres versions, ça fonctionne bien chez les utilisateurs.
Reste à savoir comment combiner tout ça.

Version anglaise :

StrFormula = "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""" & FileToLoad & """),[Delimiter="";"", Columns=27, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Utiliser la première ligne pour les en-têtes"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Modifier le" & _
        " type"" = Table.TransformColumnTypes(#""Utiliser la première ligne pour les en-têtes"",{{""TERR_ID"", type text}, {""VPNAME"", type text}, {""DIRECTORNAME"", type text}, {""MANAGERNAME"", type text}, {""EMPLOYEENAME"", type text}, {""FISCAL YEAR QUARTER"", type text}, {""GLBL_ENTI_LEVEL_NBR"", type number}, {""CNTRY_LEVEL_NBR"", type number}, {""FAC_LEVEL_NBR"", typ" & _
        "e number}, {""ENTITY_NAME"", type text}, {""FAC_NM"", type text}, {""ADDRESS"", type text}, {""CITY"", type text}, {""STATE"", type text}, {""COUNTRY"", type text}, {""FAC_PSTL"", Int64.Type}, {""EAN_LEVEL_NBR"", type number}, {""EAN_NBR"", Int64.Type}, {""MEASURE"", type text}, {""CYQ_REVENUE"", type text}, {""QUARTERLY_PY_REVENUE"", type text}, {""YOY +/- Total""," & _
        " type text}, {""YOY +/- ADNR"", type text}, {""QUARTERLY_POSTED_DAYS"", type text}, {""QUARTERLY_TOTAL_DAYS"", Int64.Type}, {""ACTIVE_OPP_FLG"", type text}, {""LAST_CALL_DATE"", type text}})," & Chr(13) & "" & Chr(10) & "    #""Type modifié avec paramètres régionaux"" = Table.TransformColumnTypes(#""Modifier le type"", {{""CYQ_REVENUE"", type number}}, ""en-US"")," & Chr(13) & "" & Chr(10) & "    #""Type modifié avec par" & _
        "amètres régionaux1"" = Table.TransformColumnTypes(#""Type modifié avec paramètres régionaux"", {{""QUARTERLY_PY_REVENUE"", type number}}, ""en-US"")," & Chr(13) & "" & Chr(10) & "    #""Type modifié avec paramètres régionaux2"" = Table.TransformColumnTypes(#""Type modifié avec paramètres régionaux1"", {{""YOY +/- Total"", type number}}, ""en-US"")," & Chr(13) & "" & Chr(10) & "    #""Type modifié avec paramètres régionau" & _
        "x3"" = Table.TransformColumnTypes(#""Type modifié avec paramètres régionaux2"", {{""YOY +/- ADNR"", type number}}, ""en-US"")," & Chr(13) & "" & Chr(10) & "    #""Type modifié avec paramètres régionaux4"" = Table.TransformColumnTypes(#""Type modifié avec paramètres régionaux3"", {{""QUARTERLY_POSTED_DAYS"", type number}}, ""en-US"")," & Chr(13) & "" & Chr(10) & "    #""Type modifié avec paramètres régionaux5"" = Table.Tr" & _
        "ansformColumnTypes(#""Type modifié avec paramètres régionaux4"", {{""QUARTERLY_TOTAL_DAYS"", type number}}, ""en-US"")" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Type modifié avec paramètres régionaux5"""

Et la version Française (séparateur ",")

StrFormula = "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""" & FileToLoad & """),[Delimiter=""#(tab)"", Columns=9, Encoding=1200, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Utiliser la première ligne pour les en-têtes"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Modifier le type"" = Ta" & _
        "ble.TransformColumnTypes(#""Utiliser la première ligne pour les en-têtes"",{{""Territory"", type text}, {""Payout Measure Name"", type text}, {""Detail Measure Name"", type text}, {""QTD Posted Revenue"", type number}, {""PY Qtr Rev (Gate Goal)"", type number}, {""Gate Attainment %"", type number}, {""Estimated YOY Growth"", type number}, {""Quarterly Goal"", type n" & _
        "umber}, {""Quarterly Attainment %"", type number}})," & Chr(13) & "" & Chr(10) & "    #""Fractionner la colonne par délimiteur"" = Table.SplitColumn(#""Modifier le type"", ""Territory"", Splitter.SplitTextByDelimiter("" | "", QuoteStyle.Csv), {""Territory.1"", ""Territory.2"", ""Territory.3""})," & Chr(13) & "" & Chr(10) & "    #""Type modifié"" = Table.TransformColumnTypes(#""Fractionner la colonne par délimiteur"",{{""" & _
        "Territory.1"", type text}, {""Territory.2"", type text}, {""Territory.3"", Int64.Type}})," & Chr(13) & "" & Chr(10) & "    #""Colonnes renommées"" = Table.RenameColumns(#""Type modifié"",{{""Territory.1"", ""Territory""}, {""Territory.2"", ""Employee Name""}, {""Territory.3"", ""ID FedEx""}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Colonnes renommées"""

Désolée pour le post à rallonge ^^
Et merci d'avance pour votre aide !

 

[chris]

RE

Avant de regarder la fonction regarde les étapes des requêtes et surtout teste sur vos environnements.

Le typage en fonction n'est que la cerise sur le gâteau...

 

[sabrina_]

Oui, je dessus, en tout cas pour ma partie.

Mais je bug d'entrée :

Formula.Firewall : Requête « csv_Extract 1 » (étape « Source ») référence d'autres requêtes ou étapes et ne peut donc pas accéder directement à une source de données. Reconstruisez cette combinaison de données.

 

[chris]

RE
Lancer PowerQuery (Données, Obtenir des données, Lancer PowerQuery), puis Fichier, Options et paramètres, Options de requête, partie GLOBAL : Confidentialité, Toujours ignorer les paramètres de niveau de confidentialité

Cela supprimera le message d'erreur.
A noter que cette option ne sert à rien dans la quasi totalité des cas et limite les possibilités, d'où son décochage.

Éventuellement faisable par une ligne de VBA

ThisWorkbook.Queries.FastCombine = True

 

[sabrina_]

Bonjour Chris,

Ca fonctionne chez moi, merci !
Je vois avec mes collègues pour leur partie.