Tableau dynamique croisée créé à partir de Codes VBA - code d'erreur

[bouclesdor]

Bonjour à tous,

Après de nombreuses recherches sur internet pour trouver un code VBA pour faire un tableau dynamique croisé avec plusieurs critères, j'ai trouvé un code que j'ai adapté à mon fichier. Jusque là tout allait bien.

J'ai fait plusieurs TDC dans plusieurs onglets car j'analyse mon fichier des ventes selon les années, selon les pays, etc... dans tous mes onglets qui contiennent des TDC j'ai un bouton qui me sert de Reestablish Connection et j'ai le code suivant qui y est rattaché: (je présice que je ne suis pas la pro des codes et pour ce code j'arrive pas a comprendre ce qu'il fait...)

Sub ReestablishConnection_produits()
Dim strFile As String
Dim strPath As String
Dim strCon As String

With ThisWorkbook
strPath = .Path
strFile = .FullName

strCon = _
"ODBC;" & _
"DSN=Excel Files;" & _
"DBQ=" & strFile & ";" & _
"DefaultDir=" & strPath & ";" & _
"DriverId=790;" & _
"MaxBufferSize=2048;" & _
"PageTimeout=5"

With .Worksheets("produits$")
If .PivotTables.Count > 0 Then .PivotTables(PIVOTNAME).PivotCache.Connection = strCon
End With
End With
End Sub

Lorsque j'ouvre mon fichier excel, j'ai un code d'erreur

" Run time error 1004
The connection for this pivot table has been deleted"

Et lorsque je fais debug la partie du code VBA qui vient en jaune est:

If .PivotTables.Count > 0 Then .PivotTables(PIVOTNAME).PivotCache.Connection = strCon

Je sais que vous aimez travailler avec un fichier mais comme se sont des infos confidentielle je ne peux mettre le fichier alors j'espère qu'avec ses informations vous pourrez m'aider ou me mettre sur une piste de quoi faire pour résoudre mon problème. J'ai inscrit ce code VBA parce qu'il était dans le fichier d'exemple qu ej'ai trouvé sur internet mais j'arrive pas à comprendre à quoi il sert alors peut-etre que c'est un problème vraiment niaiseux mais pour moi la débutante il est insurmontable... alors je fais appel à vous...

Merci à l'avance et si vous avez des questions n'hésitez pas je vais vous répondre du mieux que je peux car je veux résoudre se problème.

Bouclesdor

 

[bouclesdor]

Re : Tableau dynamique croisée créé à partir de Codes VBA - code d'erreur

Bonjour TotoTiti2008,

Merci de prendre le temps de m'aider. J'ai fait encore plein de tests ce matin et lorsque j'utilise mes boutons macros tout fonctionne très bien mais mon problème est que j'aimerais que mes tableaux ce mettre à jour à l'ouverture et j'y arrivais pas car lorsque je mets le code suivant:

ThisWorkbook.RefreshAll

dans ma feuille thisworkbook j'ai un message d'errreur.

"ODBC Excel Driver Login failed
Cannot update. Database or object is read only."
OK Cancel

lorsque je fais OK j'ai une fenêtre "Select Workbook" (voir pièce jointe le print screen).

Je vois le nom du fichier "Dtemp20110921091735" J'ai vu que ça provient de mon code c'est yyyymmddhhmmss (date + heure) de la connection. ça semble faire un fichier temporaire. Quelqu'un peut m'aider a comprendre ce code? Il semble chercher ce fichier mais comment pourra-t'il le trouvé s'il est créé toute les fois que je fais un refresh all??? Peut-être faut'il que j'enlève une partie du code pour ne pas qu'il créé ce fichier...???

Sub CreateConnection_client()
    Dim PT As PivotTable
    Dim PC As PivotCache
    Dim strFile As String
    Dim strFileTemp As String
    Dim strPath As String
    Dim arrSheets As Variant
    Dim strSQL As String
    Dim strCon As String
    Dim i As Long
    
    '   Sheets to consolidate
    '*****************************************************************************
    arrSheets = Array("Ventes 10-11", "ventes 09-10", "ventes 08-09", "ventes 07-08")
    '*****************************************************************************
    
    If Val(Application.Version) > 11 Then
        DeleteConnections_client
        CheckFileFormat_client
    Else
        strFileExt = ".xls"
        lngFileFormat = xlNormal
    End If
    
    Application.ScreenUpdating = False
    With ThisWorkbook
        strPath = .Path
        strFile = .FullName
        strFileTemp = strPath & "\DBtemp" & format(Now, "yyyymmddhhmmss") & strFileExt
        ActiveSheet.Cells.Clear
        .Worksheets(arrSheets).Copy
    End With
    
    With ActiveWorkbook
        .SaveAs strFileTemp, lngFileFormat
        .Close
    End With
    
    For i = LBound(arrSheets) To UBound(arrSheets)
        If arrSheets(i) <> ActiveSheet.Name Then
            If strSQL = "" Then
                strSQL = "SELECT * FROM [" & arrSheets(i) & "$]"
            Else
                strSQL = strSQL & " UNION ALL SELECT * FROM [" & arrSheets(i) & "$]"
            End If
        End If
    Next i
    
    strCon = _
        "ODBC;" & _
        "DSN=Excel Files;" & _
        "DBQ=" & strFileTemp & ";" & _
        "DefaultDir=" & strPath & ";" & _
        "DriverId=790;" & _
        "MaxBufferSize=2048;" & _
        "PageTimeout=5"
    
    Set PC = ThisWorkbook.PivotCaches.Add(SourceType:=xlExternal)
    
    With PC
        .Connection = strCon
        .CommandType = xlCmdSql
        .CommandText = strSQL
        Set PT = .CreatePivotTable(TableDestination:=ActiveSheet.Range("A7"))
        PT.Name = "TestPivot"
    End With
        
    With PT.PivotCache
        .Connection = Replace(strCon, strFileTemp, strFile)
    End With
    
    'Clean up
    Kill strFileTemp
    Set PT = Nothing
    Set PC = Nothing
    
End Sub

Merci à l'avance

Bouclesdor

 

[tototiti2008]

Re : Tableau dynamique croisée créé à partir de Codes VBA - code d'erreur

Re,

Oui, visible ton TCD se base sur des données de ton fichier, et le code en crée une copie temporaire de ton fichier (seulement avec les feuilles VENTES) pour le baser sur la copie... pas bien compris pourquoi, sans doute pas possible de faire une requête UNION sur les données d'un même fichier, mais ce n'est qu'une supposition
sinon essaye peut-être à l'ouverture de ton classeur :

Call CreateConnection_client
ThisWorkbook.RefreshAll

 

[bouclesdor]

Re : Tableau dynamique croisée créé à partir de Codes VBA - code d'erreur

Hé malheureusement ton code ne fonctionne pas je sais pas pourquoi ça cré un bug et je pers une de mes tables références!?!

J'ai fait énormémment de tests et je me rends compte que mes TDC deviennent inactifs un moment donné. J'ai pas encore trouvé le pourquoi du comment... mais par exemple lorsque je ferme mon fichier après avoir appuyé sur tous mes boutons macros dans chaque feuille afin de mettre à jour mes TDC, et que je réouvre mon fichier, si je me positionne sur mon TDC afin de faire afficher la barre d'outils des TDC (menu et format) ça ne foncitonne pas. C'est comme si je voyais à l'écran une image de mon TDC et que je ne peux plus le modifier à moins d'appuyer à nouveau sur mes boutons macros et ainsi mettre mes TDC à jour...

quelqu'un sait comment ça et comment résoudre se problème...

Merci,

 

[bouclesdor]

Re : Tableau dynamique croisée créé à partir de Codes VBA - code d'erreur

Après plusieurs heures et mille et une tentative j'ai trouvé une solution à mon problème: (plutôt contourner mon problème)

Sachant que mes codes VBA fonctionne très bien lorsque j'appuie sur mes boutons, j'ai décidé de faire une page d'accuel avec des boutons pour ouvrir les onglets désirés et lié au bouton j'ai mis mon code VBA donc mon info est mis à jour lorsque je demande d'ouvrir ma feuille. problème résolu.

Donc faute de ne pas être en mesure de faire un code VBA qui fait refresh tout simplement car j'ai un problème que je ne peux trouver la source. j'ai eu plusieurs pistes (lorsqu'il se connect il change toujours de # de connection et aussi mes tableaux dynamiques croisés deviennent inactifs et je ne sais trop pourquoi...) bref j'ai pas réussi à résoudre ses problèmes mais en faisant ma page d'accueil je contourne le problème et ça allège mon fichier.

Merci de votre aide
a+
Bouclesdor qui ne se décourage jamais...