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 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111
|
Set db = DBEngine.OpenDatabase(MA_BASE_ACCESS)
'J'initialise mes variables
Set xlApp = CreateObject("Excel.Application")
'Set xlBook = xlApp.Workbooks.Open(CurrentProject.Path + "\titre.xls")
Set xlWb = xlApp.Workbooks.Add
If xlApp Is Nothing Then
MsgBox "Impossible d'ouvrir excel"
Exit Sub
End If
xlApp.DisplayAlerts = False
sql = " SELECT var1,var2, var3......, FROM table1,table2, ENTITE,..."
rs.Open sql, CurrentProject.Connection, adOpenDynamic, adLockPessimistic
xlSheet.Range("A2:K2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = True
.ReadingOrder = xlContext
.MergeCells = True
End With
xlSheet.Range("A2:K2").Select
ActiveCell.FormulaR1C1 = "FICHE " & rs.Fields(0) & ""
'Quadriller
xlSheet.Range("A4:K13").Select
xlApp.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
xlApp.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With xlApp.Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With xlApp.Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With xlApp.Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With xlApp.Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With xlApp.Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With xlApp.Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
'mettre les en-tête
rs.MoveFirst
While Not rs.EOF
For j = 3 To 11
'mettre les libellés
xlSheet.Cells(j + 1, 1) = rs.Fields(j - 2).name
xlSheet.Range("B" & j + 1, "K" & j + 1).Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = True
.ReadingOrder = xlContext
.MergeCells = True
End With
xlSheet.Range("B" & j + 1, "K" & j + 1).Select
If IsDate(rs.Fields(j - 2)) Then
ActiveCell.FormulaR1C1 = Format(rs.Fields(j - 2), "dd/mm/yyyy")
Else
'ActiveCell.FormulaR1C1 = rs.Fields(j - 2)
ActiveCell.FormulaR1C1 = "REST"
End If
Next
j = j + 1
rs.MoveNext
Wend |
Partager