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
| Option Compare Database
Option Explicit
Sub ExportJournalVentes()
Const WORKBOOK_FILENAME = "c:\dbcmanager\Clients-manager.xls"
Const xlEdgeBottom = 9
Const xlEdgeLeft = 7
Const xlEdgeRight = 10
Const xlEdgeTop = 8
Const xlInsideHorizontal = 12
Const xlInsideVertical = 11
Const xlSolid = 1
Const xlCenter = -4108
Dim xlApp As Object 'Excel.application
Dim xlSheet As Object 'Excel.Worksheet
Dim xlBook As Object 'Excel.Workbook
Dim rec As DAO.Recordset
Dim I As Long
Dim J As Long
Dim t0 As Long
Dim t1 As Long
On Error GoTo ExportJournalVentes_Error
t0 = Timer
' Cocher79 a renommer !!!
' If Form_ChoixEditionFiches_Clients.Cocher79 = True Then
' Set rec = CurrentDb.OpenRecordset("Export_clients", dbOpenSnapshot)
' Else
' Set rec = CurrentDb.OpenRecordset("Export_clients_all", dbOpenSnapshot)
' End If
'Initialisations
Set xlApp = CreateObject("Excel.Application")
If Not xlApp Is Nothing Then
With xlApp
.DisplayAlerts = False
End With
Set xlBook = xlApp.Workbooks.Add
'Ajouter une feuille de calcul
Set xlSheet = xlBook.Worksheets.Add
xlSheet.Name = "Tutoriel"
' le titre
' écriture dans la cellule de ligne 1 et de colonne 1
xlSheet.Cells(1, 1) = "Export journal des ventes"
' les entetes
' .Fields(Index).Name renvoie le nom du champ
For J = 0 To rec.Fields.Count - 1
xlSheet.Cells(2, J + 1) = rec.Fields(J).Name
' Nous appliquons des enrichissements de format aux cellules
With xlSheet.Cells(2, J + 1)
.Interior.ColorIndex = 15
.Interior.Pattern = xlSolid
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlEdgeBottom).ColorIndex = xlAutomatic
.HorizontalAlignment = xlCenter
End With
Next J
' recopie des données à partir de la ligne 3
I = 3
Do While Not rec.EOF
For J = 0 To rec.Fields.Count - 1
' .Fields(Index).Type renvoie le type du champ
' si c'est un Texte (dbText) nous insérons "'" pour
' qu'il soit reconnu par Excel comme du Texte
If rec.Fields(J).Type = dbText Then
xlSheet.Cells(I, J + 1) = "'" & rec.Fields(J)
Else
xlSheet.Cells(I, J + 1) = rec.Fields(J)
End If
Next J
I = I + 1
rec.MoveNext
Loop
rec.Close
'Sauvegarde du classeur
If Dir(WORKBOOK_FILENAME, vbNormal) <> "" Then Kill WORKBOOK_FILENAME
With xlBook
.SaveAs WORKBOOK_FILENAME
.saved = True
.Close
End With
' code de fermeture et libération des objets
xlApp.Quit
t1 = Timer
'Debug.Print I & " enregistrements", Format(t1 - t0, "0") & " secondes"
On Error GoTo 0
End If
On Error GoTo 0
ExportJournalVentes_Exit:
Set rec = Nothing
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
Exit Sub
ExportJournalVentes_Error:
MsgBox Err.Description, 48, Err.Source
Resume ExportJournalVentes_Exit
End Sub |
Partager