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
|
Private Sub Form_Open(Cancel As Integer)
Dim wks As OWC11.Spreadsheet
Dim fld As DAO.Field, rst As DAO.Recordset
Dim strSql As String
Dim i As Integer, j As Integer
' ===== affectation =====
i = 1
j = 1
Set wks = Me.SpreadMFC.Object
strSql = "SELECT NOM_COULEUR, R_COULEUR, G_COULEUR, B_COULEUR, DETAIL_COULEUR FROM TB_COULEUR;"
Set rst = CurrentDb.OpenRecordset(strSql)
With wks
' ===== préparation de l'aspect =====
.DisplayToolbar = False ' on désactive la barre d'outils
With .Windows(1)
.DisplayHorizontalScrollBar = False ' on désactive la barre de défilement horizontale
.DisplayWorkbookTabs = False ' on désactive la visualisation des onglets
.DisplayColumnHeadings = False ' on désactive les entêtes de colonnes
.DisplayRowHeadings = False ' on désactive les entêtes de lignes
End With
.Windows(1).FreezePanes = False
' ===== mettre les entêtes de colonnes =====
For Each fld In rst.Fields
.Cells(i, j).Value = fld.Name
j = j + 1
Next fld
' ===== formatage de l'entête =====
.Range(.Cells(i, 1), .Cells(i, j - 1)).Interior.Color = RGB(192, 192, 192)
' ===== on remplit maintenant la feuille =====
i = i + 1
While Not rst.EOF
j = 1
For Each fld In rst.Fields
.Cells(i, j).Value = fld.Value
j = j + 1
Next fld
.Cells(i, j).Interior.Color = RGB(rst("R_COULEUR"), rst("G_COULEUR"), rst("B_COULEUR"))
i = i + 1
rst.MoveNext
Wend
' ===== formatage de la feuille =====
With .Range("A1:E" & wks.Range("A1").End(xlDown).Row)
.Columns.AutoFit
.Borders.LineStyle = xlContinuous
End With
End With
' ===== libération =====
rst.Close
Set rst = Nothing
Set wks = Nothing
End Sub |
Partager