VBA Tableau Croisé Dynamique sur sur des données volumineuse

Le 11/09/2017 à 10:59

Bonjour

J'aimerai savoir s'il y a des limite sur des données à partir de 160 000 observations ?

j'automatise mes TCD le code marche bien lorsque je suis sur un petit volume de données. Mais pour des données de 160 000 observations par exemple çà me sort un message d'erreur (Erreur d’exécution '13' : Incompatibilité de type) et le problème est au niveau de la ligne en couleur. Le même code marche pour les autres TCD...

Je vous remercie d'avance et bonne journée et bon début de semaine.

ci-dessous mon code :

Option Explicit  
Dim wsData As Worksheet, wsPT As Worksheet
Dim rngData As Range
Dim wb As Workbook
Dim ptCache As PivotCache
Dim pt As PivotTable
Dim NomTable As String
Dim DLig As Long
Dim i, j As Long

Sub TCD_tab()

    Application.DisplayAlerts = False

    Application.ScreenUpdating = False

    Set wsData = Worksheets("data")
    Set rngData = wsData.Cells(1).CurrentRegion
    Set wsPT = wb.Worksheets("Resultat1")

    'Suppression de tous les TCD existants dans la feuille

   For Each pt In wsPT.PivotTables
        pt.TableRange2.Clear
    Next pt

 With wsPT

  [b][color=#FF0000]  Set pt = ptCache.CreatePivotTable(wsPT.Range("B12"), "TCD_1", , 4)[/color][/b]

     With pt
        .ManualUpdate = True

        With .PivotFields("dépt")
            .Orientation = xlRowField
            .Caption = "Dépt"
            .Position = 1
        End With

        With .PivotFields("regime")
            .Orientation = xlRowField
            .Caption = "Regime"
            .Position = 2
        End With

        With .PivotFields("spécialité")
            .Orientation = xlRowField
            .Caption = "Domaine"
            .Position = 3
        End With

         With .PivotFields("masse")
            .Orientation = xlDataField
            .Calculation = xlPercentOfRow
            .NumberFormat = "0.00%"
        End With

        With .PivotFields("famille.nom")
            .Orientation = xlColumnField
            .Position = 1
        End With

       .ManualUpdate = False
      End With

End With

    Set pt = Nothing
    Set ptCache = Nothing
    Set rngData = Nothing
    Set wsPT = Nothing: Set wsData = Nothing

    Application.DisplayAlerts = True

End Sub
Le 11/09/2017 à 11:08

Bonjour,

PTCache n'est pas défini !...

Cdlt.

Bonjour,

PTCache n'est pas défini !...

Cdlt.

Le 11/09/2017 à 11:26

Bonjour Jean-Eric,

Désolé, en supprimant les commentaires j'ai du une ligne de code qui définit le PTCache,

et donc le code en tout est :

Option Explicit
Dim wsData As Worksheet, wsPT As Worksheet
Dim rngData As Range
Dim wb As Workbook
Dim ptCache As PivotCache
Dim pt As PivotTable
Dim NomTable As String
Dim DLig As Long
Dim i, j As Long

Sub TCD_tab()

    Application.DisplayAlerts = False

    Application.ScreenUpdating = False

    Set wsData = Worksheets("data")
    Set rngData = wsData.Cells(1).CurrentRegion
    Set wsPT = wb.Worksheets("Resultat1")

   For Each pt In wsPT.PivotTables
        pt.TableRange2.Clear
    Next pt

 With wsPT

    Set ptCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, rngData, 4)

    Set pt = ptCache.CreatePivotTable(wsPT.Range("B12"), "TCD_1", , 4)

     With pt
        .ManualUpdate = True

        With .PivotFields("dépt")
            .Orientation = xlRowField
            .Caption = "Dépt"
            .Position = 1
        End With

        With .PivotFields("regime")
            .Orientation = xlRowField
            .Caption = "Regime"
            .Position = 2
        End With

        With .PivotFields("spécialité")
            .Orientation = xlRowField
            .Caption = "Domaine"
            .Position = 3
        End With

         With .PivotFields("masse")
            .Orientation = xlDataField
            .Calculation = xlPercentOfRow
            .NumberFormat = "0.00%"
        End With

        With .PivotFields("famille.nom")
            .Orientation = xlColumnField
            .Position = 1
        End With

       .ManualUpdate = False
      End With

End With

    Set pt = Nothing
    Set ptCache = Nothing
    Set rngData = Nothing
    Set wsPT = Nothing: Set wsData = Nothing

    Application.DisplayAlerts = True

End Sub
Le 11/09/2017 à 11:30

Re,

Quelle ligne précise est surlignée en jaune lors de l'erruer ?

Cdlt.

Le 11/09/2017 à 11:32

l'erreur provient de la ligne :

  Set ptCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, rngData, 4)
Le 11/09/2017 à 11:34

Bonjour,

'Erreur d’exécution '13' : Incompatibilité de type' apparait si le type n'est pas celui attendu. Par exemple du texte à la place d'un numérique ou bien un code erreur dans la cellule.

Tu as quoi dans wsPT.Range("B12") au moment de l'erreur ?

eric

Edit :

Ah, faudrait savoir... ton code a changé et la ligne en erreur aussi...

Le 11/09/2017 à 11:52

Bonjour Eric,

dans wsPT.Range("B12"), c'est le début de mon TCD, donc l'étiquette 'Somme de masse.'

Et si je supprime une bonne partie de ligne (c'est a dire reduire l'observation a 10 000 au lieu de 160 000) ca me genre bien le TCD...

Le 11/09/2017 à 11:59

Re,

A quoi correspond rngData dans la procédure ?

Vérifie la source des données (plage ou tableau).

Cdlt.

Le 11/09/2017 à 15:25

Mon rngData correspond à la plage de données de ma base (il y a des variables numériques et textes)

Mais le programme marche quand je diminue le nombre de ligne. j'ai 160 000 et si je supprime les ligne en ramenant à 10 000 ca marche bien.

J'ai vérifié les formats des données mais rien

Le 11/09/2017 à 16:38

A part pour les filtres et d'autres particularités, les limites sont surtout liées à la mémoire disponible.

En as-tu assez ou peux-tu tester sur un pc mieux pourvu ?

A la limite contrôle la disponibilité de la mémoire virtuelle (C:\ proche de la saturation, et bien mémoire gérée par windows coché) mais si tu vas trop dessus ça va ramer.

Le 11/09/2017 à 17:42

RE,

Peux tu essayer cette procédure et me redire ?

Cdlt.

Sub TCD_tab()
    Application.ScreenUpdating = False
    Set wb = ActiveWorkbook
    Set wsData = wb.Worksheets("data")
    Set rngData = wsData.Cells(1).CurrentRegion
    Set wsPT = wb.Worksheets("Resultat1")
    For Each pt In wsPT.PivotTables
        pt.TableRange2.Clear
    Next pt
    With wsPT
        Set ptCache = wb.PivotCaches.Create(xlDatabase, rngData)
        Set pt = ptCache.CreatePivotTable(.Range("B12"), "TCD_1")
        With pt
            .ManualUpdate = True
            .AddFields RowFields:=Array("dépt", "regime", "spécialité"), ColumnFields:="famille.nom"
            With .PivotFields("masse")
                .Orientation = xlDataField
                .Function = xlSum
                .Calculation = xlPercentOfRow
                .NumberFormat = "0.00%"
            End With
            '.RowAxisLayout xlTabularRow
            .ManualUpdate = False
        End With
    End With

    Set pt = Nothing
    Set ptCache = Nothing
    Set rngData = Nothing
    Set wsPT = Nothing: Set wsData = Nothing
    Set wb = Nothing

End Sub
Le 11/09/2017 à 17:54

Oui Jean-Eric,

Je viens de tester mais il y a toujours une erreur d’exécution '13' : Incompatibilité de type au niveau :

Set ptCache = wb.PivotCaches.Create(xlDatabase, rngData)
Le 11/09/2017 à 18:28

Re,

Peux-tu nous communiquer une partie de la source des données ?

Surtout la ligne des entête de colonnes.

Cdlt.

Le 12/09/2017 à 09:17

Oui,

J'ai en tout 160 000 lignes pour 84 colonnes.

Et le fichier est lourd (environ 160 Mo)

J'ai supprimé les autres colonnes que j'utilise pas pour diminuer le nombre de colonnes et le dernier code que tu m'as envoyé marche...

avec les 160 000 lignes pour 20 colonnes...

Il y a peut-etre des colonnes qui ont des caractères bizarre comme a dit Eriiic.

Je pense que je vais faire comme çà. J'utiliserai ton dernier code avec des colonnes reduites.

Je te remercie beaucoup Jean-Eric

Bonne journée vous

Le 12/09/2017 à 09:50

Bonjour,

Je te mets le lien pour les limites des TCDs pour Excel 2010.

https://support.office.com/fr-fr/article/Sp%C3%A9cifications-et-limites-relatives-%C3%A0-Excel-1672b34d-7043-467e-8e27-269d656771c3#ID0EBABAAA=Excel 2010

Es-tu hors limite pour certains points ?

Regarde aussi sur les intitulés de tes en-têtes de colonnes et renomme les si tu as un doute.

Tu peux aussi voir du côté de Power Query pour Excel 2010 (complément gratuit Microsoft). Il te permettra de récupérer les seules données nécessaires pour ton TCD.

Il y a des éléments de réponse pour l'erreur 13 lors de la création des PivotCaches (Google).

Je regarderai en fin de journée.

Cdlt..

Le 13/09/2017 à 08:59

Bonjour Jean-Eric,

Merci,

c'est fait, le problème était plus lié aux entêtes des colonnes. J'ai renommer et celà marche bien.

Encore une tonne de merci

Le 13/09/2017 à 09:09

Bonjour,

Content pour toi et que cela te serve de leçon.

Cdlt.

Rechercher des sujets similaires à "vba tableau croise dynamique donnees volumineuse"