mise en forme automatique d'un fichier excel extrait d'une base de données excel

Question

Je copie le r&#233;sultat d'un &#233;tat d'access vers excel &#224; partir d'une macro access. Par contre je voudrais avoir par d&#233;faut une mise en forme &#224; l'ouverture du fichier excel r&#233;sultant. J'avais d&#233;ja cr&#233;&#233; la macro Excel pour cette mise en page mais comme mon fichier est &#233;cras&#233; &#224; chaque fois, il faudrait id&#233;alement que je rajoute ce code dans ma macro access. J'ai esay&#233; mais cela ne fonctionne pas...
Voici mon code excel que j'aimerai faire apparaitre

Sub mef_extracts()
'
' mef_extracts Macro
' Macro enregistr&#233;e le 04/07/2005 par MSCHALL
'
' Touche de raccourci du clavier: Ctrl+Maj+M
'
    Rows("1:1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 90
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Cells.Select
    Cells.EntireColumn.AutoFit
    Rows("1:1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Replace What:="_", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.RowHeight = 25
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="0"
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .ColorIndex = 2
    End With
    With Selection.FormatConditions(1).Borders(xlLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.FormatConditions(1).Borders(xlRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.FormatConditions(1).Borders(xlTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.FormatConditions(1).Borders(xlBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.FormatConditions(1).Interior.ColorIndex = 41
    With Selection.Font
        .Name = "Arial"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Selection.AutoFilter
    Range("D2").Select
    ActiveWindow.FreezePanes = True
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = "$A:$C"
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = "&8&D &T"
        .CenterHeader = "&8&F" & Chr(10) & "&""Arial,Gras""&A"
        .RightHeader = "&8ADT"
        .LeftFooter = "&8Direction Technique"
        .CenterFooter = ""
        .RightFooter = "&""Arial,Normal""&8Page &P / &N"
        .LeftMargin = Application.InchesToPoints(0.4)
        .RightMargin = Application.InchesToPoints(0.4)
        .TopMargin = Application.InchesToPoints(0.6)
        .BottomMargin = Application.InchesToPoints(0.24)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.1)
        .PrintHeadings = False
        .PrintGridlines = True
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
    End With
End Sub

fred.toto · Answer

Sur Office 2003 en mettant l'autofilter en commentaire ca marche.
Il faut voir ce que tu veux mettre comme param&#232;tre &#224; ton autofilter si tu souhaites le conserver 
Fred.

orobert01 · Answer

Ou se situe l'autofilter dans ce code? et &#224; quoi sert'il vraiment (mise a part a filtrer automatiquement...)?

Olivier

Mise en forme automatique d'un fichier excel extrait d'une base de données excel

2 réponses

Discussions similaires