Problème format de donnée malgré formatage de la cellule

**Malfudan** · 27/09/2021, 08h23

Bonjour,
Je rencontre un problème lors de la récupération lors de la récupération d'un fichier issue d'un SI entreprise :
l'extraction issue du SI comporte des données numérique et des dates formaté en texte. Dans mon utilisation, j''ai mis en place une macro pour aller chercher le contenu du fichier Excel issue de mon SI et je viens les coller moyennant des trie dans un onglet. Pour se faire j'utilise dans ma macro un code pour coller les valeurs:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
        If Sheets(MaFeuilleDestination).Cells(lignecorrespondante, 13) <> Sheets(MaFeuilleSource).Cells(I, 13) Then             'si différence de contenu de cellule alors
            Sheets(MaFeuilleDestination).Cells(lignecorrespondante, 13) = Sheets(MaFeuilleSource).Cells(I, 13).Value            'copie colle la donnée
            Sheets(MaFeuilleDestination).Cells(lignecorrespondante, 13).Font.Color = RGB(14, 4, 204)                            'changer couleur cellule
            Sheets(MaFeuilleDestination).Cells(lignecorrespondante, 13).Font.Bold = True                                        'met en gras le texte

seulement même en ne collant que les valeurs je me retrouve avec une anomalie "nombre stocké sous forme de texte" (voir image). J'ajoute que le changement de format de cellule ne résout pas le problème
Nom : fichier issu du SI.PNG
Affichages : 266
Taille : 21,8 Ko

Nom : fichier issu du SI.PNG
Affichages : 266
Taille : 21,8 Ko

J'ai trouvé une bidouille pour résoudre le problème il faut copier une cellule contenant "1" et faire un coller de valeur factoriel sur les cellules en anomalie(voir image).
Nom : Collage factoriel.PNG
Affichages : 267
Taille : 13,7 Ko

Nom : Collage factoriel.PNG
Affichages : 267
Taille : 13,7 Ko

Je ne sais pas si automatiser cette fonction serait la solution sinon comment faire au niveau code pour faire un en gros un collage *1 comme le fait cette fonction de collage spécial, sachant qu'il faut juste cibler les colonnes qui contiennes des nombres ou des dates car elle ne marche pas avec du texte ("texte" -> collage spécial -> champ vide).

Voilà si quelqu'un a déjà rencontré ce problème et a une solution propre je suis preneur :-)
Voici un fichier semblable à celui issu du SI : Test export anomalie.xlsx

**78chris** · 27/09/2021, 08h52

Bonjour

Ne pas confondre le type de la donnée et son format : l'habit ne fait pas le moine

En général cela se règle à l'import : tu pars d'un csv ?

**Malfudan** · 27/09/2021, 09h33

Bonjour 78chris,

Oui j'ai bien constaté que le format ne changeait pas grand chose. Non je ne parts malheureusement pas d'un CSV, je récupère un fichier comme celui dans mon message initial (.xls). d'où ma demande, je pense qu'il faudrait que lors de l'ouverture du fichier je convertisse les données?

**Pierre Fauconnier** · 27/09/2021, 09h59

Salut.

Perso, je m'orienterais vers Power Query (intégré à ta version) qui est l'outil idéal pour récupérer et préparer des CSV en vue de l'analyse des données avec Excel (tableau croisé dynamique, graphique, ...) en se passant de VBA.

Power Query permet notamment de filtrer, trier, transformer, regrouper, réorganiser (colonnes), ... Que du bonheur lorsqu'on y a goûté

**Malfudan** · 27/09/2021, 13h35

Ok merci de ton retour, Je viens de fouiller un peu du côté Power Query. J'arrive effectivement à récupérer les données propre, par contre pour automatiser je ne sais pas trop comment faire quelque chose de propre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
Sub Macro8()
'
' Macro8 Macro
'
 
'
    ActiveWorkbook.Queries.Add Name:="Feuil1", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.Workbook(File.Contents(""C:\Users\k.philippon\Desktop\hors réseau\Coord\Import test.xlsx""), null, true)," & Chr(13) & "" & Chr(10) & "    Feuil1_Sheet = Source{[Item=""Feuil1"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""En-têtes promus"" = Table.PromoteHeaders(Feuil1_Sheet, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Type modifié"" = Table.TransformColumnTypes(#""En-têtes promus"",{{""Ty" & _
        "pe batiment"", type text}, {""Trigramme"", type text}, {""Type de fait"", type text}, {""Num"", Int64.Type}, {""Annee"", Int64.Type}, {""Objet"", type text}, {""Delta nature de l'avarie"", type text}, {""Date msg FT"", type date}, {""Date DI"", type text}, {""Date TO"", type date}, {""Date fin de travaux"", type date}, {""Date msg cloture"", type date}, {""Descripti" & _
        "f reparation"", type text}, {""Para golf msg"", type text}, {""Code SITINDIS"", type text}, {""Ref contrat"", type text}, {""Mot cle"", type text}, {""Intervenant"", type text}, {""Bigramme"", type text}, {""Libelle bigramme"", type text}, {""Description app integrant"", type text}, {""RFO en avarie"", type text}, {""Materiel"", type number}, {""Designation"", type " & _
        "text}, {""Statut"", type text}, {""Site"", type any}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Type modifié"""
    Sheets.Add After:=ActiveSheet
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Feuil1;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Feuil1]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = False
        .ListObject.DisplayName = "Feuil12"
        .Refresh BackgroundQuery:=False
    End With
    Range("F3").Select
    ActiveWindow.SmallScroll Down:=6
    Range("F8").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    ActiveWindow.SmallScroll Down:=-12
End Sub

Je voudrais juste rajouter un onglet avec un nom spécifique avec la donnée convertie puis le supprimer en fin de macro (après avoir copié collé les données).

**Pierre Fauconnier** · 27/09/2021, 13h59

Pas certain de bien cerner le besoin ^^

A priori, on ne crée pas la requête Power Query par VBA, puisque l'on dispose d'un IHM (interface homme machine) très bien fait. Donc, perso, je créerais la requête dans Power Query. Power Query renvoie le tableau final dans Excel, et je ne vois pas bien pourquoi il faudrait le supprimer.

Bref, je n'ai pas compris ce que tu souhaites réaliser

**Malfudan** · 27/09/2021, 14h24

En gros:
je souhaite juste faire une conversion de données et envoyé le contenu dans un onglet "A" spécifique.
Ensuite je lance une macro qui vient "importer" les données dans l'onglet "A" dans un onglet "Synthèse", la macro vient ajouter les données qui n’étaient pas présentes et elle met à jour les données qui ont changées (ajout de date , d'informations...) avec une mise en forme sur les données ajoutées et modifiés pour que l'utilisateur voit rapidement les changements.
Aucun intérêt de garder le contenu du Query, car mon but est juste de convertir à la demande.

**Malfudan** · 29/09/2021, 17h18

Bon et bien j'ai réussis à trouver mon bonheur via Power Query :-).

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
Call DELQUERY                                   'appel la fonction
On Error Resume Next                            'pas de message si erreur
Application.DisplayAlerts = False
Sheets(OngletIMP).Delete                        'supprime l'onglet
Application.DisplayAlerts = True
 
'crée une requète sur la base du tableau souhaité
    ActiveWorkbook.Queries.Add Name:="CONVERT Import", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source =Excel.Workbook(File.Contents(" & Chr(34) & Linktab & Chr(34) & "), null, true)," & Chr(13) & "" & Chr(10) & "    Feuil1_Sheet = Source{[Item=""Feuil1"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""En-têtes promus"" = Table.PromoteHeaders(Feuil1_Sheet, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Type modifié"" = Table.TransformColumnTypes(#""En-têtes promus"",{{""Ty" & _
        "pe batiment"", type text}, {""TEST"", type text}, {""Type de fait"", type text}, {""Num"", Int64.Type}, {""Annee"", Int64.Type}, {""Objet"", type text}, {""Delta nature de l'avarie"", type text}, {""Date msg FT"", type date}, {""Date DI"", type text}, {""Date TO"", type date}, {""Date fin de travaux"", type date}, {""Date msg cloture"", type date}, {""Descripti" & _
        "f reparation"", type text}, {""Para golf msg"", type text}, {""Code SITINDIS"", type text}, {""Ref contrat"", type text}, {""Mot cle"", type text}, {""Intervenant"", type text}, {""Bigramme"", type text}, {""Libelle bigramme"", type text}, {""Description app integrant"", type text}, {""RFO en avarie"", type text}, {""Materiel"", type number}, {""Designation"", type " & _
        "text}, {""Statut"", type text}, {""Site"", type any}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Type modifié"""
'crée un onglet nommé par la variable ongletIMP dans lequel la requète est envoyée
    Sheets.Add(After:=Worksheets(Worksheets.Count)).Name = OngletIMP
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=CONVERT Import ;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [CONVERT Import]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = False
        .ListObject.DisplayName = "CONVERT Import"
        .Refresh BackgroundQuery:=False
    End With

Merci de ton retour

**78chris** · 29/09/2021, 17h32

Bonjour à tous

Je suis du même avis que Pierre

On ne crée quasi jamais les requêtes par VBA

Il est très facile de créer une requête paramétrée utilisable avec des sources différentes et/ou des filtres différents par exemples.

**Pierre Fauconnier** · 29/09/2021, 17h49

Salut Chris,

Surtout qu'ici, le code supprime la feuille qui contient l'ancienne query pour la reconstruire après, alors qu'il suffit d'actualiser la requête déjà créée. De plus, le code court-circuite les erreurs et les alertes, ce qui fait que si un problème surgit, on n'en est pas averti, et on ne peut que constater après exécution que "ça n'a pas marché"...

@ Malfudan

Donc, pour bien fixer les choses:

On crée la requête Power Query par l'interface Power Query dans Excel;
On prépare les données dans Power Query (aménagement de colonnes, traitements divers, ...);
On renvoie dans Excel.

Après, une simple actualisation (par clic droit ou par code vba) actualise la requête. Si le nom du fichier est modifié (ou autre altération de la requête initiale), on peut paramétrer la recherche. J'ai écrit quelques billets sur Power Query, notamment sur sa paramétrisation.

**Malfudan** · 30/09/2021, 10h43

Bonjour Pierre merci pour ton retour, pour le on error resume next, il n'est là de base que pour l'onglet OngletImp à supprimer. J'ai modifié le code depuis pour éviter la désactivation des erreur:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
__________________________Dans mes fonctions______________________________
 
Public Function FeuilExiste(FeuilleAVerifier As String) As Boolean
'fonction qui vérifie si la "FeuilleAVerifier" existe dans le Classeur actif
 
On Error GoTo SiErreur
Dim Feuille
 
    FeuilExiste = False
    For Each Feuille In Sheets
        If UCase(Feuille.Name) = UCase(FeuilleAVerifier) Then
            FeuilExiste = True
            Exit Function
        End If
    Next Feuille
Exit Function
 
SiErreur:
    FeuilExiste = CVErr(xlErrNA)
End Function
 
_____________________________Dans mon SUB__________________
 
Call DELQUERY                                   'appel la fonction de suppression de requête
 
If FeuilExiste(OngletIMP) = True Then           'appel la fonction de controle de présente d'onglet
    Application.DisplayAlerts = False
    Sheets(OngletIMP).Delete                        'supprime l'onglet
    Application.DisplayAlerts = True
End If
 
'crée une requète sur la base du tableau souhaité
    ActiveWorkbook.Queries.Add Name:="CONVERT Import SIGLE", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source =Excel.Workbook(File.Contents(" & Chr(34) & Linktab & Chr(34) & "), null, true)," & Chr(13) & "" & Chr(10) & "    Feuil1_Sheet = Source{[Item=""Feuil1"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""En-têtes promus"" = Table.PromoteHeaders(Feuil1_Sheet, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Type modifié"" = Table.TransformColumnTypes(#""En-têtes promus"",{{""Ty" & _
        "pe batiment"", type text}, {""Trigramme"", type text}, {""Type de fait"", type text}, {""Num"", Int64.Type}, {""Annee"", Int64.Type}, {""Objet"", type text}, {""Delta nature de l'avarie"", type text}, {""Date msg FT"", type date}, {""Date DI"", type text}, {""Date TO"", type date}, {""Date fin de travaux"", type date}, {""Date msg cloture"", type date}, {""Descripti" & _
        "f reparation"", type text}, {""Para golf msg"", type text}, {""Code SITINDIS"", type text}, {""Ref contrat"", type text}, {""Mot cle"", type text}, {""Intervenant"", type text}, {""Bigramme"", type text}, {""Libelle bigramme"", type text}, {""Description app integrant"", type text}, {""RFO en avarie"", type text}, {""Materiel"", type number}, {""Designation"", type " & _
        "text}, {""Statut"", type text}, {""Site"", type any}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Type modifié"""
'crée un onglet nommé par la variable ongletIMP dans lequel la requète est envoyée
    Sheets.Add(After:=Worksheets(Worksheets.Count)).Name = OngletIMP
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=CONVERT Import SIGLE;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [CONVERT Import SIGLE]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = False
        .ListObject.DisplayName = "Feuil1"
        .Refresh BackgroundQuery:=False
    End With
 
Sheets("Synthèse").Activate                                                         ' appel onglet synthese
Call MAJLISTE                                                                      'appelle macro
 
Call DELQUERY                                    'appel la fonction
 
If FeuilExiste(OngletIMP) = True Then           'appel la fonction de controle de présente d'onglet
    Application.DisplayAlerts = False
    Sheets(OngletIMP).Delete                        'supprime l'onglet
    Application.DisplayAlerts = True
End If

Pour la notion de paramétrage de Query Je vais regarder les possibilité et me pencher sur le sujet et le dialogue possible entre les requête et les traitement de données à l’intérieur mais là au vue de mon utilisation Je vais partir sur cette solution pour le moment :-): Pas de changement de format, chemin du fichier source qui change tous le temps, rapidité de la solution actuelle solution actuelle et facilité d'intégration dans mon code actuel (ce n'est qu'un tout petit bout de mon code).

Problème format de donnée malgré formatage de la cellule

Macros et VBA Excel

Discussions similaires

Partager

Partager