1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88
| private sub FormaterExcel()
const CHEMIN_FICHIER as string = "X:\TonChemin\"
const TEMPLATE_FICHIER as string="*.xls*"
Dim xlApp As object :Set xlApp = CreateObject("Excel.Application") 'Excel.Application
xlApp.visible=true 'Rendre Excel visible semble faciliter sa fermeture et sa désintanctiation après.
Dim xlBook As object 'Excel.Workbook
Dim xlSheet As object 'Excel.WorkSheet
dim nomFic as string:nomFic=dir(CHEMIN_FICHIER & TEMPLATE_FICHIER)
do while nomFic<>""
set xlBook=xlApp.Workbooks.open(CHEMIN_FICHIER & momFic)
set xlSheet=xlBook.Worksheets(1)
xlBook.activate
xlSheet.activate
xlSheet.Cells.Select
xlSheet.Cells.EntireColumn.AutoFit
With xlSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
xlSheet.PageSetup.PrintArea = ""
xlSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.708661417322835)
.RightMargin = Application.InchesToPoints(0.708661417322835)
.TopMargin = Application.InchesToPoints(0.748031496062992)
.BottomMargin = Application.InchesToPoints(0.748031496062992)
.HeaderMargin = Application.InchesToPoints(0.31496062992126)
.FooterMargin = Application.InchesToPoints(0.31496062992126)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With
ActiveWindow.SelectedSheets.PrintPreview 'il faudra peut-être adapter cela
Range("E9").Select
ActiveWindow.View = xlPageBreakPreview 'il faudra peut-être adapter cela
xlBook.Save
xlBook.close
set xlSheet=nothing
set xlBook=nothing
nomFic=dir() 'trouve le prochain fichier
loop
xlApp.quit:set xlApp=nothing 'ou xlApp.close je ne me souviens plus.
end sub |
Partager