Conserver le format de données d'une table de données après copie

%3CLINGO-SUB%20id%3D%22lingo-sub-3022158%22%20slang%3D%22fr-FR%22%3EPreserve%20the%20data%20format%20of%20a%20data%20table%20after%20copying%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3022158%22%20slang%3D%22fr-FR%22%3E%3CP%3EGood%20morning%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20work%20with%20Excel%20Office%20365%2C%20version%202102%2C%20on%20PC.%3C%2FP%3E%3CP%3EI%20create%20a%20dashboard%20by%20copying%20(via%20VBA)%20graphs%20from%20different%20source%20files.%3C%2FP%3E%3CP%3EMy%20problem%20is%20that%20the%20format%20of%20the%20copied%20graphics%20(and%20underlying%20data)%20is%20not%20kept%20after%20copying%3A%20another%20color%20set%20(accessory)%20but%20especially%20formats%20of%20the%20axis%20and%20the%20lost%20data%20table!!%20(see%20code%20and%20illustrations%20below)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%20for%20your%20help%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20VBA%20code%20used%3A%3C%2FP%3E%3CP%3E%3CSPAN%3EActiveSheet.ChartObjects(%22Evol_taux%22).Activate%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EActiveChart.Relative.Delete%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EWorkbooks.Open%20Filename%3A%3D%20_%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%22%3CA%20href%3D%22https%3A%2F%2Fcmcbe.sharepoint.com%2Fsites%2F00000376%2FShared%2520Documents%2FTableaux%2520de%2520suivi%2FEtudes%2520-%2520Reportings%2Fstats_affil_Evol_31dec.xlsx%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fcmcbe.sharepoint.com%2Fsites%2F00000376%2FShared%2520Documents%2FTableaux%2520de%2520suivi%2FEtudes%2520-%2520Reportings%2Fstats_affil_Evol_31dec.xlsx%3C%2FA%3E%22%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EActiveSheet.ChartObjects(%22Evol_taux%22).Activate%20ActiveChart.ChartArea.Copy%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EWindows(%22Fonts.xlsm%20Dashboard%22).Activate%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3ERange(%22N3%22).Select%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EActiveSheet.Paste%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EActiveSheet.ChartObjects(%22Evol_taux%22).Activate%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EWith%20ActiveSheet.ChartObjects(%22Evol_taux%22)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E.Left%20%3D%20Range(%22N3%3Ax21%22).Left%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E.Top%20%3D%20Range(%22N3%3Ax21%22).Top%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E.Width%20%3D%20Range(%22N3%3Ax21%22).Width%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E.Height%20%3D%20Range(%22N3%3Ax21%22).Height%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EEnd%20With%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EActiveChart.DataTable.Select%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EActiveChart.Axes(xlCategory).Select%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3ESelection.TickLabels.NumberFormat%20%3D%20%22dd-mm-y%22%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EWindows(%22stats_affil_Evol_31dec.xlsx%22).Activate%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EActiveWindow.Close%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EHere%20is%20an%20image%20of%20the%20original%20graph%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MurielW_1-1638356435651.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F330858i58C9132F385CF2D9%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MurielW_1-1638356435651.png%22%20alt%3D%22MurielW_1-1638356435651.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EAnd%20the%20result%20after%20copying%20and%20closing%20the%20source%20file%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MurielW_0-1638356378125.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F330857i27952A55354C1ED5%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MurielW_0-1638356378125.png%22%20alt%3D%22MurielW_0-1638356378125.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3022158%22%20slang%3D%22fr-FR%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

Bonjour, 

 

Je travaille avec Excel Office 365, version 2102,  sur PC.

Je crée un tableau de bord en copiant (via VBA) des graphiques de différents fichiers sources.

Mon problème est que le format des graphiques (et données sous-jacentes) copiés n'est pas conservé après copie : autre set de couleur (accessoire) mais surtout formats de l'axe et de la table de données perdus!! (voir code et illustrations ci-dessous)

 

D'avance merci pour votre aide

 

Voici le code VBA utilisé :

    ActiveSheet.ChartObjects("Evol_taux").Activate

    ActiveChart.Parent.Delete

    Workbooks.Open Filename:= _

"https://cmcbe.sharepoint.com/sites/00000376/Shared%20Documents/Tableaux%20de%20suivi/Etudes%20-%20Re..."

    ActiveSheet.ChartObjects("Evol_taux").Activate    ActiveChart.ChartArea.Copy

    Windows("Tableau de Bord Polices.xlsm").Activate

    Range("N3").Select

    ActiveSheet.Paste

    ActiveSheet.ChartObjects("Evol_taux").Activate

    With ActiveSheet.ChartObjects("Evol_taux")

        .Left = Range("N3:x21").Left

        .Top = Range("N3:x21").Top

        .Width = Range("N3:x21").Width

        .Height = Range("N3:x21").Height

    End With

    ActiveChart.DataTable.Select

    ActiveChart.Axes(xlCategory).Select

    Selection.TickLabels.NumberFormat = "jj-mm-aaaa"

    Windows("stats_affil_Evol_31dec.xlsx").Activate

    ActiveWindow.Close

 

Voici une image du graphe d'origine :

MurielW_1-1638356435651.png

 

Et le résultat après copie et fermeture du fichier source : 

MurielW_0-1638356378125.png

 

 

 

 

 

0 Replies