Debogage: Macro TCD

Le 21/08/2020 à 16:14

Bonjour à tous,

Je sollicite votre aide car je n'arrive pas à résoudre une erreur

Ma macro récupère un fichier de données et le transforme en liste. Je cherche ensuite a créer un tableau croisé dynamique pour avoir une meilleur visibilité des résultats.

La macro créer bien le tableau mais celle-ci s'arrête au moment de placer le champs "Valeurs (?)".

J'ai recopié ce que m'a donné l'enregistreur de macro en effectuant la manipulation moi-même.

J'ai pensé que ce serai le caractère spécial ohm qui gênerai mais le problème persiste (d'ailleurs je n'arrive toujours pas à l'afficher correctement .

Je suis novice en VBA donc si vous voyez des bouts de code à améliorer n'hésitez pas non plus ! D'autant plus que la macro va être utilisée par plusieurs personnes donc elle doit être facile à l'utilisation.

Merci d'avance pour votre aide !

PS: Pour l'instant j'ai figé mes plages de TCD afin d'éviter les erreurs mais j’appellerai ensuite des variables. Donc NomConnecteur = P2 et nbpins = 15 pour l'instant.

20resultats-mis-en-forme.xlsm (37.49 Ko)
Sub Mise_en_forme()

'1. DONNEES UTILISATEURS
    Dim NomConnecteur
    Dim fichier
    Dim nbpins

    'Entrée du nom du connecteur
    NomConnecteur = InputBox("Saisir le nom du connecteur en test")
    If NomConnecteur = "" Then
        MsgBox "La macro a été annulée"
        Exit Sub
    End If
    'Entrée du nombre de pins du connecteur
    nbpins = Application.InputBox("Saisir le nombre de pins du connecteur", , , , , , , 1) 'Type = 1 pour forcer l'input en nombre uniquement
    If nbpins = "" Then
        MsgBox "La macro a été annulée"
        Exit Sub
    End If
    'Sélection du fichier résultat
    fichier = Application.GetOpenFilename(FileFilter:="All Files (*.*),*.*", _
                                          Title:="Sélectionner le fichier résultat", _
                                          MultiSelect:=False)

'2. IMPORTATION DANS EXCEL DU FICHIER TEXTE
    Dim skipheader

    'Ouverture d'une sheet avec le nom du connecteur après la sheet "Macro"
    Sheets.Add(After:=Worksheets("Macro")).Name = NomConnecteur
    Sheets(NomConnecteur).Select
    'Nombre de ligne a sauter avant la première valeur
    skipheader = 17
    'Copie du fichier résultat dans la sheet active
    If fichier <> False Then
        With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fichier, Destination:=Range("$A$2"))
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .AdjustColumnWidth = True
        .TextFileParseType = xlDelimited 'xlFixedWidth
        '.TextFileFixedColumnWidths = Array(x, x)
        .TextFileStartRow = skipheader
        .TextFileConsecutiveDelimiter = False
        .TextFileDecimalSeparator = "."
        .TextFileSpaceDelimiter = True 'Espace pour délimitation de colonne
        .TextFileColumnDataTypes = Array(xlTextFormat, xlGeneralFormat) '1ère colonne en texte et 2ème colonne en standard
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
        End With
    End If

'3. FORMATAGE DE LA FEUILLE EXCEL
    Dim ChrSpec
    Dim pin1
    Dim pin2

    'Suppression de la première colonne inutile
    Columns("A:A").Delete Shift:=xlToLeft
    'Insertion de 2 colonnes à gauche pour la liste des pins
    Range("A1").Select
    Selection.EntireColumn.Insert Shift:=xlToRight
    Selection.EntireColumn.Insert Shift:=xlToRight
    'Ecriture des en-têtes
    Range("A1") = "Pin n°1"
    Range("B1") = "Pin n°2"
    'Range("A1:B1").Interior.Color = RGB(50, 255, 50)
    Range("A1:B1").Font.Name = "Calibri"
    Range("A1:B1").Font.Size = 16
    Range("A1:B1").Font.Bold = True

    ChrSpec = ChrW(2126) 'Caractère spécial ohm
    Range("C1") = "Valeurs (" & ChrSpec & ")"
    'Range("C1").Interior.Color = RGB(50, 50, 255)
    Range("C1").Font.Name = "Calibri"
    Range("C1").Font.Size = 16
    Range("C1").Font.Bold = True
    Columns("C:C").NumberFormatLocal = "# ##0"

    Range("A1:C1").Columns.AutoFit 'Adaptation des largeurs de colonnes au texte

    'Ecriture de la liste des pins
    pin1 = 1
    pin2 = 2
    Range("A2") = pin1
    Range("B2") = pin2
    Range("B2").Select

    While pin1 < nbpins
        While pin2 < nbpins
            pin2 = pin2 + 1
            ActiveCell.Offset(1, -1).Select
            ActiveCell = pin1
            ActiveCell.Offset(0, 1).Select
            ActiveCell = pin2
        Wend
    pin2 = pin1 + 1
    pin1 = pin1 + 1
    Wend

'4. CREATION DU TABLEAU CROISE DYNAMIQUE
    Dim SourceData
    Dim TableDestination

    SourceData = "P2!R1C1:R106C3"
    TableDestination = "P2!R1C5"
    'Création du tableau croisé dynamique
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        SourceData, Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:=TableDestination, TableName:=NomConnecteur
    'Placement de la colonne Pin n°1 en ligne
    With ActiveSheet.PivotTables(NomConnecteur).PivotFields("Pin n°1")
        .Orientation = xlRowField
        .Position = 1
    End With
    'Placement de la colonne Pin n°2 en colonne
    With ActiveSheet.PivotTables(NomConnecteur).PivotFields("Pin n°2")
        .Orientation = xlColumnField
        .Position = 1
    End With
    'Placement de la colonne Valeur en data croisées
    ActiveSheet.PivotTables(NomConnecteur).AddDataField ActiveSheet.PivotTables(NomConnecteur). _
        PivotFields("Valeurs (?)"), "Somme de Valeurs (?)", xlSum
    ActiveWorkbook.ShowPivotTableFieldList = False 'Cache les options de champs du Tcd
    With ActiveSheet.PivotTables(NomConnecteur)
        .ColumnGrand = False 'Cache le nom des colonnes
        .RowGrand = False 'Cache le nom des lignes
    End With
End Sub
Le 21/08/2020 à 16:56

Bonjour,

Une procédure fonctionnelle à adapter !?

Cdlt.

Sub CreatePivotTable()
Dim wsData As Worksheet
Dim PTCache As PivotCache, pt As PivotTable
Dim rngData As Range

    Set wsData = Worksheets("P2")
    Set rngData = wsData.Cells(1).CurrentRegion
    Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData)
    Set pt = PTCache.CreatePivotTable(TableDestination:=Cells(5), TableName:="TCD_1")
    With pt
        .ManualUpdate = True
        .AddFields RowFields:="Pin n°1", ColumnFields:="Pin n°2"
        With .PivotFields("Valeurs")
            .Orientation = xlDataField
            .Function = xlSum
            .NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 ;"
        End With
        .RowAxisLayout xlTabularRow
        .ColumnGrand = False
        .RowGrand = False
        .ManualUpdate = False
    End With
End Sub
Le 25/08/2020 à 09:27

Bonjour,

Merci pour votre réponse Jean-Eric.

J'ai essayé avec votre proposition de code. Déjà elle est beaucoup plus propre et je ne connaissais pas certaines fonction comme .CurrentRegion.

Cependant un bug persiste... Le debogage m'indique la ligne .Orientation = xlDataField au moment de placer la colonne "Valeurs" dans le champs Data.

Je commence a me demander si le problème ne vient pas d'un format de ma liste générée précédemment. Si vous avez une idée, je suis preneur !

Cordialement,

Le 25/08/2020 à 09:36

Bonjour,

Oups !...

J'ai modifié le nom du champ Valeurs dans ton tableau. Remplace donc" Valeurs" par "Valeurs (?)" dans la procédure.

Cdlt.

Le 25/08/2020 à 10:11

Désolé j'ai oublié de préciser que j'avais déjà anticiper cette erreur.

J'ai essayer avec "Valeurs (?)" et aussi en changeant plus haut dans la macro en "Valeurs" simplement. Les deux cas génèrent la même erreur.

En changeant le format de la case qui contient "Valeurs" en texte ça ne fonctionne pas non plus.

Le 25/08/2020 à 10:38

Re,

Ton classeur comporte des connexions externes !...

Tu confirmes ? Sont elles nécessaires ?

Cdlt.

Le 25/08/2020 à 10:47

Oui je confirme, je fais appel a un fichier de données brutes.

Le but de ma macro est justement de mettre en forme ces données. Donc oui elle est nécessaire.

Je link l'exemple de fichier brut que j'appelle dans la macro.

7exemple-resultats.txt (2.16 Ko)
Le 25/08/2020 à 10:54

En recommençant la manip manuellement j'ai remarqué que le champ de Data portait le nom "Valeurs2" au lieu de "Valeurs".

J'ai modifié ma macro en ce sens et ça fonctionne !

Bon par contre je sais pas encore pourquoi le champs de données s’appelle comme ça...

Peut-être que "Valeurs" est déjà utilisé autre part...

Le 25/08/2020 à 11:07

Bonjour,

pour les TCD il est souvent inutile de le reconstruire entièrement en vba.
Il est plus simple de le construire manuellement et de se contenter en vba de mettre à jour la plage de source des données une fois celles-ci mises en forme.
eric

Le 25/08/2020 à 11:19

Re,

Essaie ainsi :

Sub CreatePivotTable()
Dim wsPT As Worksheet
Dim PTCache As PivotCache, pt As PivotTable
Dim rngData As Range
Dim ChrSpec: ChrSpec = ChrW(2126)

    Set wsPT = Worksheets("P2")
    Set rngData = wsPT.Cells(1).CurrentRegion
    On Error Resume Next
    wsPT.PivotTables("TCD_1").TableRange2.Clear
    On Error GoTo 0
    Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData)
    Set pt = PTCache.CreatePivotTable(TableDestination:=Cells(5), TableName:="TCD_1")
    With pt
        .ManualUpdate = True
        .AddFields RowFields:="Pin n°1", ColumnFields:="Pin n°2"
        With .PivotFields("Valeurs (" & ChrSpec & ")")
            .Orientation = xlDataField
            .Function = xlSum
            .NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 ;"
            .Caption = ChrW(931) & " Valeurs"
        End With
        .RowAxisLayout xlTabularRow
        .ColumnGrand = False
        .RowGrand = False
        .ManualUpdate = False
    End With
End Sub
Le 25/08/2020 à 11:54

Même erreur, le debogage pointe la ligne With .PivotFields("Valeurs (" & ChrSpec & ")").

Dans le TCD c'est toujours "Valeurs2" qui s'affiche dans la liste des champs.

Concernant ChrSpec = ChrW(2126), il affiche ? au lieu du symbole ohm.

Au début je pensais que c'était la cause mais quand j'enlève le symbole ça ne fonctionne pas on plus.

Le 25/08/2020 à 12:31

Re,

Alt F8 et exécuter CreatePivotTable

Cdlt.

4resultats-mis-en-forme.xlsm (30.13 Ko)
Le 25/08/2020 à 12:59

Erreur d'éxécution '1004':

Impossible de lire la propriété PivotFields de la classe PivotTable.

Il semblerai bien que ce soit le caractère spécial finalement...

Le 25/08/2020 à 13:01

Re,

Le fichier de mon précédent message n'a pas été téléchargé. Il est fonctionnel chez moi !...

Cdlt.

Le 26/08/2020 à 09:13

Bonjour,

Pardon je n'avais pas compris.

Votre fichier fonctionne chez moi aussi !

La différence se situe donc soit dans la variable à la place de "P2" ou "TCD_1"(J'utilise la même pour ces deux informations) soit dans la code au-dessus du TCD.

Je vais remplacer votre code par le mien progressivement pour voir où est le problème.

Merci encore de votre aide c'est sympa de prendre du temps pour les autres :)

Le 26/08/2020 à 09:45

Bonjour,

Vire les caractères spéciaux de tes en-têtes.

Cdlt.

Le 26/08/2020 à 13:26

Le problème venait finalement du fait que le TCD utilise déjà la variable "Valeurs" pour créer le tableau.

Donc nommé une colonne "Valeurs" en champs d'entrée n'est pas une bonne idée (d'où le nom "valeur2" qu'Excel changeait à chaque fois)

J'ai renommé l'entête en "Mesures" et tout est rentré dans l'ordre.

Merci Jean-Eric pour l'aide

Rechercher des sujets similaires à "debogage macro tcd"