Exécuter une macro dans un autre classeur

Question

Bonjour,
J'ai créé une macro Format_Tab_Se qui met en forme un tableau. Mon programme crée un nouveau classeur(Mes_Menus.xlsm), nomme les feuilles, les remplit, mais je n'arrive pas à reproduire la mise en forme avec ma macro sur ces feuilles. Au lieu de ça il met en forme une feuille de mon classeur actif (Projet.xlsm). 

Voilà mon code:
Sub AddNewWorkbook()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

'On crée l'objet Excel
    Set xlApp = CreateObject("Excel.Application")
    'On définit le nombre d'onglets (ici 5)
    xlApp.SheetsInNewWorkbook = 5
    'On ajoute un classeur
    Set xlBook = xlApp.Workbooks.Add
    'On donne un nom au classeur
    'xlBook.SaveAs (Workbooks(ActiveWorkbook.Name).Path & Application.PathSeparator & "Mes Menus.xls")
    xlBook.SaveAs Filename:=(Workbooks(ActiveWorkbook.Name).Path & Application.PathSeparator & "Mes_Menus.xlsm"), FileFormat:= _
xlOpenXMLWorkbookMacroEnabled
    'On rend le classeur visible
    xlApp.Visible = True
    'On créer l'objet onglet dans le nouveau classeur créé
    Set xlSheet = xlBook.Worksheets(1)
    'On affecte un nom aux l'onglets
    xlSheet.Name = "Semaine1"
    'xlSheet.Activate
    xlSheet.Range("A1:V22").Value = ThisWorkbook.Worksheets("EdT").Range("A1:V22").Value
    xlSheet.Range("A3:V22").Select
    Application.Run "Projet.xlsm!Format_Tab_Se"
    'on libère l'objet onglet pour pouvoir en créer un nouveau ... etc
    
    Set xlSheet = Nothing
    '
    Set xlSheet = xlBook.Worksheets(2)
    xlSheet.Name = "Semaine2"
    xlSheet.Range("A1:V22").Value = ThisWorkbook.Worksheets("EdT").Range("A1:V22").Value
    Set xlSheet = Nothing
    '
    Set xlSheet = xlBook.Worksheets(3)
    xlSheet.Name = "Semaine3"
    xlSheet.Range("A1:V22").Value = ThisWorkbook.Worksheets("EdT").Range("A1:V22").Value
    Set xlSheet = Nothing
    
    Set xlSheet = xlBook.Worksheets(4)
    xlSheet.Name = "Semaine4"
    xlSheet.Range("A1:V22").Value = ThisWorkbook.Worksheets("EdT").Range("A1:V22").Value
    Set xlSheet = Nothing
    
    Set xlSheet = xlBook.Worksheets(5)
    xlSheet.Name = "Mois"
    Set xlSheet = Nothing
    'On remet la propriété de l'application à 3 (par défaut)
    xlApp.SheetsInNewWorkbook = 3
    'On ferme l'application
    xlApp.Quit

End Sub


Quelqu'un sait-il pourquoi ça n'applique pas ma macro à la feuille Semaine1 du classeur Mes_Menus?

2ème question, faut-il que mon classeur soit xlsm pour que je puisse lui appliquer la macro?

Merci d'avance,
Claire

jordane45 · Answer

Bonjour,

faut-il que mon classeur soit xlsm pour que je puisse lui appliquer la macro? 


Soit XLS ( format 2003 )
Soit XLSM pour Office >= 2007



Pour ce qui est de ta "macro" ....
Pourquoi passes tu par du  Excel.Application  ???
Tu as bien écrit ta macro depuis un classeur Excel non ?
Ou bien.. tu exécutes le code que tu nous as posté depuis une application ?

Et puis.. le code que tu nous donnes... sert juste à "créer" ton nouveau classeur...
par contre.. il lance la macro :  Application.Run "Projet.xlsm!Format_Tab_Se"

Et donc.. c'est dans cette macro que ta mise en forme se fait non ?
C'est donc dans ce code qu'il faut regarder ( et nous montrer)

Claire.Iz · Answer

Le code qui crée le classeur je l'ai trouvé sur internet et il marche donc j'avoue que je n'ai pas cherché plus loin. Je ne sais pas à quoi correspond Excel.Application, j'ai supposé que c'était la syntaxe qu'il fallait pour créer un nouveau classeur. Je ne crois pas que le problème vienne de la macro Format_Tab_Se car elle marche très bien (mais pas dans la bonne feuille). 
Mais voici le code:

Option ExplicitSub Format_Tab_Se()'' Format_Tab_Se Macro' Met en forme les emplois du temps pour une semaine''    ActiveWindow.ScrollRow = 10    ActiveWindow.ScrollRow = 9    ActiveWindow.ScrollRow = 8    ActiveWindow.ScrollRow = 7    ActiveWindow.ScrollRow = 6    ActiveWindow.ScrollRow = 5    ActiveWindow.ScrollRow = 4    ActiveWindow.ScrollRow = 3    ActiveWindow.ScrollRow = 2    Range("B3:V3").Select    Selection.Font.Bold = True    With Selection.Font        .Name = "Calibri"        .Size = 16        .Strikethrough = False        .Superscript = False        .Subscript = False        .OutlineFont = False        .Shadow = False        .Underline = xlUnderlineStyleNone        .ThemeColor = xlThemeColorLight1        .TintAndShade = 0        .ThemeFont = xlThemeFontMinor    End With    With Selection.Font        .Name = "Calibri"        .Size = 18        .Strikethrough = False        .Superscript = False        .Subscript = False        .OutlineFont = False        .Shadow = False        .Underline = xlUnderlineStyleNone        .ThemeColor = xlThemeColorLight1        .TintAndShade = 0        .ThemeFont = xlThemeFontMinor    End With    Range("A5:A22").Select    Selection.Font.Bold = True    With Selection.Font        .Name = "Calibri"        .Size = 18        .Strikethrough = False        .Superscript = False        .Subscript = False        .OutlineFont = False        .Shadow = False        .Underline = xlUnderlineStyleNone        .ThemeColor = xlThemeColorLight1        .TintAndShade = 0        .ThemeFont = xlThemeFontMinor    End With    Range("B3:D4").Select    With Selection        .HorizontalAlignment = xlGeneral        .VerticalAlignment = xlBottom        .WrapText = False        .Orientation = 0        .AddIndent = False        .IndentLevel = 0        .ShrinkToFit = False        .ReadingOrder = xlContext        .MergeCells = True    End With    Range("E3:G4").Select    With Selection        .HorizontalAlignment = xlGeneral        .VerticalAlignment = xlBottom        .WrapText = False        .Orientation = 0        .AddIndent = False        .IndentLevel = 0        .ShrinkToFit = False        .ReadingOrder = xlContext        .MergeCells = True    End With    Range("H3:J4").Select    With Selection        .HorizontalAlignment = xlGeneral        .VerticalAlignment = xlBottom        .WrapText = False        .Orientation = 0        .AddIndent = False        .IndentLevel = 0        .ShrinkToFit = False        .ReadingOrder = xlContext        .MergeCells = True    End With    ActiveWindow.ScrollColumn = 2    ActiveWindow.ScrollColumn = 3    ActiveWindow.ScrollColumn = 4    ActiveWindow.ScrollColumn = 5    ActiveWindow.ScrollColumn = 6    ActiveWindow.ScrollColumn = 7    ActiveWindow.ScrollColumn = 8    ActiveWindow.ScrollColumn = 7    ActiveWindow.ScrollColumn = 6    Range("K3:M4").Select    With Selection        .HorizontalAlignment = xlGeneral        .VerticalAlignment = xlBottom        .WrapText = False        .Orientation = 0        .AddIndent = False        .IndentLevel = 0        .ShrinkToFit = False        .ReadingOrder = xlContext        .MergeCells = True    End With    Range("N3:P4").Select    With Selection        .HorizontalAlignment = xlGeneral        .VerticalAlignment = xlBottom        .WrapText = False        .Orientation = 0        .AddIndent = False        .IndentLevel = 0        .ShrinkToFit = False        .ReadingOrder = xlContext        .MergeCells = True    End With    ActiveWindow.ScrollColumn = 7    ActiveWindow.ScrollColumn = 8    ActiveWindow.ScrollColumn = 9    ActiveWindow.ScrollColumn = 10    ActiveWindow.ScrollColumn = 11    ActiveWindow.ScrollColumn = 12    ActiveWindow.ScrollColumn = 13    Range("Q3:S4").Select    With Selection        .HorizontalAlignment = xlGeneral        .VerticalAlignment = xlBottom        .WrapText = False        .Orientation = 0        .AddIndent = False        .IndentLevel = 0        .ShrinkToFit = False        .ReadingOrder = xlContext        .MergeCells = True    End With    Range("T3:V4").Select    With Selection        .HorizontalAlignment = xlGeneral        .VerticalAlignment = xlBottom        .WrapText = False        .Orientation = 0        .AddIndent = False        .IndentLevel = 0        .ShrinkToFit = False        .ReadingOrder = xlContext        .MergeCells = True    End With    ActiveWindow.ScrollColumn = 12    ActiveWindow.ScrollColumn = 11    ActiveWindow.ScrollColumn = 10    ActiveWindow.ScrollColumn = 8    ActiveWindow.ScrollColumn = 6    ActiveWindow.ScrollColumn = 3    ActiveWindow.ScrollColumn = 2    ActiveWindow.ScrollColumn = 1    Range("A5:A10").Select    With Selection        .HorizontalAlignment = xlGeneral        .VerticalAlignment = xlBottom        .WrapText = False        .Orientation = 0        .AddIndent = False        .IndentLevel = 0        .ShrinkToFit = False        .ReadingOrder = xlContext        .MergeCells = True    End With    Range("A11:A16").Select    With Selection        .HorizontalAlignment = xlGeneral        .VerticalAlignment = xlBottom        .WrapText = False        .Orientation = 0        .AddIndent = False        .IndentLevel = 0        .ShrinkToFit = False        .ReadingOrder = xlContext        .MergeCells = True    End With    Range("A17:A22").Select    With Selection        .HorizontalAlignment = xlGeneral        .VerticalAlignment = xlBottom        .WrapText = False        .Orientation = 0        .AddIndent = False        .IndentLevel = 0        .ShrinkToFit = False        .ReadingOrder = xlContext        .MergeCells = True    End With    ActiveWindow.ScrollRow = 11    ActiveWindow.ScrollRow = 10    ActiveWindow.ScrollRow = 9    ActiveWindow.ScrollRow = 8    ActiveWindow.ScrollRow = 7    ActiveWindow.ScrollRow = 6    ActiveWindow.ScrollRow = 5    ActiveWindow.ScrollRow = 3    ActiveWindow.ScrollRow = 2    ActiveWindow.ScrollRow = 1    Range("B5:V22").Select    Selection.Borders(xlDiagonalDown).LineStyle = xlNone    Selection.Borders(xlDiagonalUp).LineStyle = xlNone    With Selection.Borders(xlEdgeLeft)        .LineStyle = xlContinuous        .ColorIndex = 0        .TintAndShade = 0        .Weight = xlThin    End With    With Selection.Borders(xlEdgeTop)        .LineStyle = xlContinuous        .ColorIndex = 0        .TintAndShade = 0        .Weight = xlThin    End With    With Selection.Borders(xlEdgeBottom)        .LineStyle = xlContinuous        .ColorIndex = 0        .TintAndShade = 0        .Weight = xlThin    End With    With Selection.Borders(xlEdgeRight)        .LineStyle = xlContinuous        .ColorIndex = 0        .TintAndShade = 0        .Weight = xlThin    End With    With Selection.Borders(xlInsideVertical)        .LineStyle = xlContinuous        .ColorIndex = 0        .TintAndShade = 0        .Weight = xlThin    End With    With Selection.Borders(xlInsideHorizontal)        .LineStyle = xlContinuous        .ColorIndex = 0        .TintAndShade = 0        .Weight = xlThin    End With    Range("B10:V10").Select    Selection.Borders(xlDiagonalDown).LineStyle = xlNone    Selection.Borders(xlDiagonalUp).LineStyle = xlNone    With Selection.Borders(xlEdgeLeft)        .LineStyle = xlContinuous        .ColorIndex = 0        .TintAndShade = 0        .Weight = xlThin    End With    With Selection.Borders(xlEdgeTop)        .LineStyle = xlContinuous        .ColorIndex = 0        .TintAndShade = 0        .Weight = xlThin    End With    With Selection.Borders(xlEdgeBottom)        .LineStyle = xlContinuous        .ColorIndex = 0        .TintAndShade = 0        .Weight = xlMedium    End With    With Selection.Borders(xlEdgeRight)        .LineStyle = xlContinuous        .ColorIndex = 0        .TintAndShade = 0        .Weight = xlThin    End With    With Selection.Borders(xlInsideVertical)        .LineStyle = xlContinuous        .ColorIndex = 0        .TintAndShade = 0        .Weight = xlThin    End With    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone    Selection.Copy    Range("B16:V16").Select    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _        SkipBlanks:=False, Transpose:=False    Application.CutCopyMode = False    Selection.Copy    ActiveWindow.ScrollRow = 2    ActiveWindow.ScrollRow = 3    ActiveWindow.ScrollRow = 4    ActiveWindow.ScrollRow = 5    ActiveWindow.ScrollRow = 6    ActiveWindow.ScrollRow = 7    ActiveWindow.ScrollRow = 8    Range("B22:V22").Select    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _        SkipBlanks:=False, Transpose:=False    Application.CutCopyMode = False    ActiveWindow.ScrollRow = 7    ActiveWindow.ScrollRow = 6    ActiveWindow.ScrollRow = 5    ActiveWindow.ScrollRow = 4    ActiveWindow.ScrollRow = 3    ActiveWindow.ScrollRow = 2    ActiveWindow.ScrollRow = 1    ActiveWindow.ScrollRow = 2    Range("D5:D22").Select    Selection.Borders(xlDiagonalDown).LineStyle = xlNone    Selection.Borders(xlDiagonalUp).LineStyle = xlNone    With Selection.Borders(xlEdgeLeft)        .LineStyle = xlContinuous        .ColorIndex = 0        .TintAndShade = 0        .Weight = xlThin    End With    With Selection.Borders(xlEdgeTop)        .LineStyle = xlContinuous        .ColorIndex = 0        .TintAndShade = 0        .Weight = xlThin    End With    With Selection.Borders(xlEdgeBottom)        .LineStyle = xlContinuous        .ColorIndex = 0        .TintAndShade = 0        .Weight = xlMedium    End With    With Selection.Borders(xlEdgeRight)        .LineStyle = xlContinuous        .ColorIndex = 0        .TintAndShade = 0        .Weight = xlMedium    End With    Selection.Borders(xlInsideVertical).LineStyle = xlNone    Selection.Copy    Range("G5:G22").Select    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _        SkipBlanks:=False, Transpose:=False    Application.CutCopyMode = False    Selection.Copy    Range("J5:J22").Select    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _        SkipBlanks:=False, Transpose:=False    Application.CutCopyMode = False    Selection.Copy    ActiveWindow.ScrollColumn = 2    ActiveWindow.ScrollColumn = 3    ActiveWindow.ScrollColumn = 4    ActiveWindow.ScrollColumn = 5    ActiveWindow.ScrollColumn = 6    ActiveWindow.ScrollColumn = 7    Range("M5:M22").Select    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _        SkipBlanks:=False, Transpose:=False    Application.CutCopyMode = False    Selection.Copy    Range("P5:P22").Select    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _        SkipBlanks:=False, Transpose:=False    Application.CutCopyMode = False    Selection.Copy    ActiveWindow.ScrollColumn = 8    ActiveWindow.ScrollColumn = 9    ActiveWindow.ScrollColumn = 10    ActiveWindow.ScrollColumn = 11    ActiveWindow.ScrollColumn = 12    Range("S5:S22").Select    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _        SkipBlanks:=False, Transpose:=False    Application.CutCopyMode = False    Selection.Copy    Range("V5:V22").Select    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _        SkipBlanks:=False, Transpose:=False    Application.CutCopyMode = False    ActiveWindow.ScrollColumn = 11    ActiveWindow.ScrollColumn = 10    ActiveWindow.ScrollColumn = 9    ActiveWindow.ScrollColumn = 8    ActiveWindow.ScrollColumn = 7    ActiveWindow.ScrollColumn = 6    ActiveWindow.ScrollColumn = 5    ActiveWindow.ScrollColumn = 4    ActiveWindow.ScrollColumn = 3    ActiveWindow.ScrollColumn = 2    ActiveWindow.ScrollColumn = 1    Range("B3:V4").Select    Selection.Borders(xlDiagonalDown).LineStyle = xlNone    Selection.Borders(xlDiagonalUp).LineStyle = xlNone    With Selection.Borders(xlEdgeLeft)        .LineStyle = xlContinuous        .ColorIndex = 0        .TintAndShade = 0        .Weight = xlMedium    End With    With Selection.Borders(xlEdgeTop)        .LineStyle = xlContinuous        .ColorIndex = 0        .TintAndShade = 0        .Weight = xlMedium    End With    With Selection.Borders(xlEdgeBottom)        .LineStyle = xlContinuous        .ColorIndex = 0        .TintAndShade = 0        .Weight = xlMedium    End With    With Selection.Borders(xlEdgeRight)        .LineStyle = xlContinuous        .ColorIndex = 0        .TintAndShade = 0        .Weight = xlMedium    End With    Selection.Borders(xlInsideVertical).LineStyle = xlNone    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone    Range("A5:A22").Select    Selection.Borders(xlDiagonalDown).LineStyle = xlNone    Selection.Borders(xlDiagonalUp).LineStyle = xlNone    With Selection.Borders(xlEdgeLeft)        .LineStyle = xlContinuous        .ColorIndex = 0        .TintAndShade = 0        .Weight = xlMedium    End With    With Selection.Borders(xlEdgeTop)        .LineStyle = xlContinuous        .ColorIndex = 0        .TintAndShade = 0        .Weight = xlMedium    End With    With Selection.Borders(xlEdgeBottom)        .LineStyle = xlContinuous        .ColorIndex = 0        .TintAndShade = 0        .Weight = xlMedium    End With    With Selection.Borders(xlEdgeRight)        .LineStyle = xlContinuous        .ColorIndex = 0        .TintAndShade = 0        .Weight = xlMedium    End With    Selection.Borders(xlInsideVertical).LineStyle = xlNone    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone    Selection.Borders(xlDiagonalDown).LineStyle = xlNone    Selection.Borders(xlDiagonalUp).LineStyle = xlNone    With Selection.Borders(xlEdgeLeft)        .LineStyle = xlContinuous        .ColorIndex = 0        .TintAndShade = 0        .Weight = xlThick    End With    With Selection.Borders(xlEdgeTop)        .LineStyle = xlContinuous        .ColorIndex = 0        .TintAndShade = 0        .Weight = xlThick    End With    With Selection.Borders(xlEdgeBottom)        .LineStyle = xlContinuous        .ColorIndex = 0        .TintAndShade = 0        .Weight = xlThick    End With    With Selection.Borders(xlEdgeRight)        .LineStyle = xlContinuous        .ColorIndex = 0        .TintAndShade = 0        .Weight = xlThick    End With    Selection.Borders(xlInsideVertical).LineStyle = xlNone    With Selection.Borders(xlInsideHorizontal)        .LineStyle = xlContinuous        .ColorIndex = 0        .TintAndShade = 0        .Weight = xlThick    End With    Range("B3:V4").Select    Selection.Borders(xlDiagonalDown).LineStyle = xlNone    Selection.Borders(xlDiagonalUp).LineStyle = xlNone    With Selection.Borders(xlEdgeLeft)        .LineStyle = xlContinuous        .ColorIndex = 0        .TintAndShade = 0        .Weight = xlThick    End With    With Selection.Borders(xlEdgeTop)        .LineStyle = xlContinuous        .ColorIndex = 0        .TintAndShade = 0        .Weight = xlThick    End With    With Selection.Borders(xlEdgeBottom)        .LineStyle = xlContinuous        .ColorIndex = 0        .TintAndShade = 0        .Weight = xlThick    End With    With Selection.Borders(xlEdgeRight)        .LineStyle = xlContinuous        .ColorIndex = 0        .TintAndShade = 0        .Weight = xlThick    End With    With Selection.Borders(xlInsideVertical)        .LineStyle = xlContinuous        .ColorIndex = 0        .TintAndShade = 0        .Weight = xlThick    End With    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone    Range("B3:V4").Select    With Selection.Interior        .Pattern = xlSolid        .PatternColorIndex = xlAutomatic        .ThemeColor = xlThemeColorAccent1        .TintAndShade = 0.399975585192419        .PatternTintAndShade = 0    End With    Selection.Copy    Range("A5:A22").Select    Range("A5:A22").Select    Application.CutCopyMode = False    With Selection.Interior        .Pattern = xlSolid        .PatternColorIndex = xlAutomatic        .ThemeColor = xlThemeColorAccent1        .TintAndShade = 0.399975585192419        .PatternTintAndShade = 0    End With    Range("B5:V22").Select    With Selection.Interior        .Pattern = xlSolid        .PatternColorIndex = xlAutomatic        .ThemeColor = xlThemeColorAccent1        .TintAndShade = 0.799981688894314        .PatternTintAndShade = 0    End With    Range("F9").SelectEnd Sub

En gros je voudrais que ma procédure AddNewWorkbook me crée un nouveau classeur, dans le même dossier que celui où est mon classeur actuel Projet.xlsm, que ce nouveau classeur ait 5 feuilles donc les 4 premières reproduisent la mise en forme donnée par Format_Tab_Se. Sauf que je ne sais pas exécuter une macro depuis un classeur vers un autre classeur donc j'ai fouillé un peu partout sur des forums pour trouver des bouts de code qui faisaient ça. Mais apparemment, tout mis bout à bout ça marche pas ^^

Exécuter une macro dans un autre classeur

2 réponses

Discussions similaires

Newsletters