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 112 113 114 115 116 117 118 119 120 121 122 123
| 1.Function export_excel_avec_entetes(ByVal nomrequete As String)
2.''ajouter les references Microsoft 12.0 Object Library
3.''ajouter les references Microsoft DAO 3.6 Object Library
4.Dim db As DAO.Database
5.Dim rs As DAO.Recordset
6.Dim objExcel As Excel.Application
7.Dim objWorkBook As Excel.Workbook
8.Dim objFeuille As Excel.Worksheet
9.nomrequete = "paper"
10.Dim i, j As Integer
11.Set objWorkBook = Workbooks.Add
12.'supprimer les feuilles 2 et 3
13.objWorkBook.Sheets(3).Delete
14.objWorkBook.Sheets(2).Delete
15.Set objFeuille = objWorkBook.Sheets(1)
16.'nommer la feuille de l'export cible
17.objFeuille.Name = "Export"
18.Set db = CurrentDb
19.Set rs = db.OpenRecordset("select * from " & nomrequete, dbOpenDynaset)
20.i = 1
21.'renseigner les entêtes de colonnes
22. For j = 0 To rs.Fields.Count - 1
23. objFeuille.Cells(1, i) = rs.Fields(j).Name
24. i = i + 1
25. Next j
26.'renseigner les données
27.objFeuille.Cells(2, 1).CopyFromRecordset rs
28.objWorkBook.Application.Visible = True
29.objWorkBook.Activate
30.''libération de la mémoire
31.Set objWorkBook = Nothing
32.Set objFeuille = Nothing
33.Set db = Nothing
34.Set rs = Nothing
35.End Function
36.Function export_excel_sans_entetes(ByVal nomrequete As String)
37.''ajouter les references Microsoft 12.0 Object Library
38.''ajouter les references Microsoft DAO 3.6 Object Library
39.Dim db As DAO.Database
40.Dim rs As DAO.Recordset
41.Dim objExcel As Excel.Application
42.Dim objWorkBook As Excel.Workbook
43.Dim objFeuille As Excel.Worksheet
44.Set objWorkBook = Workbooks.Add
45.'supprimer les feuilles 2 et 3
46.objWorkBook.Sheets(3).Delete
47.objWorkBook.Sheets(2).Delete
48.Set objFeuille = objWorkBook.Sheets(1)
49.'nommer la feuille de l'export cible
50.objFeuille.Name = "Export"
51.Set db = CurrentDb
52.Set rs = db.OpenRecordset("select * from " & nomrequete, dbOpenDynaset)
53.'renseigner les données
54.objFeuille.Cells(1, 1).CopyFromRecordset rs
55.objWorkBook.Application.Visible = True
56.objWorkBook.Activate
57.''libération de la mémoire
58.Set objWorkBook = Nothing
59.Set objFeuille = Nothing
60.Set db = Nothing
61.Set rs = Nothing
62.End FunctionFunction export_excel_avec_entetes(ByVal nomrequete As String)
''ajouter les references Microsoft 12.0 Object Library
''ajouter les references Microsoft DAO 3.6 Object Library
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim objExcel As Excel.Application
Dim objWorkBook As Excel.Workbook
Dim objFeuille As Excel.Worksheet
nomrequete = "paper"
Dim i, j As Integer
Set objWorkBook = Workbooks.Add
'supprimer les feuilles 2 et 3
objWorkBook.Sheets(3).Delete
objWorkBook.Sheets(2).Delete
Set objFeuille = objWorkBook.Sheets(1)
'nommer la feuille de l'export cible
objFeuille.Name = "Export"
Set db = CurrentDb
Set rs = db.OpenRecordset("select * from " & nomrequete, dbOpenDynaset)
i = 1
'renseigner les entêtes de colonnes
For j = 0 To rs.Fields.Count - 1
objFeuille.Cells(1, i) = rs.Fields(j).Name
i = i + 1
Next j
'renseigner les données
objFeuille.Cells(2, 1).CopyFromRecordset rs
objWorkBook.Application.Visible = True
objWorkBook.Activate
''libération de la mémoire
Set objWorkBook = Nothing
Set objFeuille = Nothing
Set db = Nothing
Set rs = Nothing
End Function
Function export_excel_sans_entetes(ByVal nomrequete As String)
''ajouter les references Microsoft 12.0 Object Library
''ajouter les references Microsoft DAO 3.6 Object Library
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim objExcel As Excel.Application
Dim objWorkBook As Excel.Workbook
Dim objFeuille As Excel.Worksheet
Set objWorkBook = Workbooks.Add
'supprimer les feuilles 2 et 3
objWorkBook.Sheets(3).Delete
objWorkBook.Sheets(2).Delete
Set objFeuille = objWorkBook.Sheets(1)
'nommer la feuille de l'export cible
objFeuille.Name = "Export"
Set db = CurrentDb
Set rs = db.OpenRecordset("select * from " & nomrequete, dbOpenDynaset)
'renseigner les données
objFeuille.Cells(1, 1).CopyFromRecordset rs
objWorkBook.Application.Visible = True
objWorkBook.Activate
''libération de la mémoire
Set objWorkBook = Nothing
Set objFeuille = Nothing
Set db = Nothing
Set rs = Nothing
End Function |
Partager