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
|
Dim ssql As String
Dim rs1 As DAO.Recordset
Dim ExcelApp As Excel.Application
Dim activsheet As Excel.Worksheet
Dim activworkbook As Excel.Workbook
Dim i As Integer
Dim premiere_ligne_tableau As Integer
Dim derniere_ligne_tableau As Integer
Dim derniere_colonne_tableau as Integer
Dim premiere_colonne_tableau As Integer
Dim tableau as variant()
Tableau= array(« France », « Allemagne », « suisse », »Espagne »)
For i = 0 to ubound(tableau)
Set ExcelApp = New Excel.Application
ExcelApp.Visible = True
ExcelApp.Workbooks.Add
Set activworkbook = ExcelApp.ActiveWorkbook
activworkbook.SaveAs CurrentProject.Path + "\" & tableau(i) & ".xls"
Set activsheet = activworkbook.Sheets(1)
activsheet.Name = recap
activsheet.Activate
premiere_ligne_tableau = 7
derniere_ligne_tableau = premiere_ligne_tableau
premiere_colonne_tableau=1
derniere_colonne_tableau = 5
activsheet.Cells(premiere_ligne_tableau, premiere_colonne_tableau) = "nom"
activsheet.Cells(premiere_ligne_tableau, premiere_colonne_tableau + 1) ="prénom"
activsheet.Cells(premiere_ligne_tableau, premiere_colonne_tableau + 2) = "age"
activsheet.Cells(premiere_ligne_tableau, premiere_colonne_tableau + 3) = "adresse"
activsheet.Cells(premiere_ligne_tableau, premiere_colonne_tableau + 4) = "ville"
ssql = "select nom, prénom, age, adresse, ville " + _
" from COORD" + _
" where pays='" + tableau(i) & "'"
Set rs1 = CurrentDb.OpenRecordset(ssql)
i = 1
While Not rs1.EOF
activsheet.Cells(premiere_ligne_tableau + i, premiere_colonne_tableau) = rs1!nom
activsheet.Cells(premiere_ligne_tableau + i, premiere_colonne_tableau + 1) = rs1!prénom
activsheet.Cells(premiere_ligne_tableau + i, premiere_colonne_tableau + 2) = rs1!age
activsheet.Cells(premiere_ligne_tableau + i, premiere_colonne_tableau + 3) = rs1!adresse
activsheet.Cells(premiere_ligne_tableau + i, premiere_colonne_tableau + 4) = rs1!ville
i = i + 1
rs1.MoveNext
Wend
derniere_ligne_tableau = premiere_ligne_tableau + i - 1
Set rs1 = Nothing
activsheet.Range(Cells(premiere_ligne_tableau, 1), Cells(derniere_ligne_tableau, derniere_colonne_tableau)).Borders(xlEdgeLeft).LineStyle = xlContinuous
activsheet.Range(Cells(premiere_ligne_tableau, 1), Cells(derniere_ligne_tableau, derniere_colonne_tableau)).Borders(xlEdgeTop).LineStyle = xlContinuous
activsheet.Range(Cells(premiere_ligne_tableau, 1), Cells(derniere_ligne_tableau, derniere_colonne_tableau)).Borders(xlEdgeBottom).LineStyle = xlContinuous
activsheet.Range(Cells(premiere_ligne_tableau, 1), Cells(derniere_ligne_tableau, derniere_colonne_tableau)).Borders(xlEdgeRight).LineStyle = xlContinuous
activsheet
.Range(Cells(premiere_ligne_tableau, 1), Cells(derniere_ligne_tableau, derniere_colonne_tableau)).Borders(xlInsideVertical).LineStyle = xlContinuous
activsheet
.Range(Cells(premiere_ligne_tableau, 1), Cells(derniere_ligne_tableau, derniere_colonne_tableau)).Borders(xlInsideHorizontal).LineStyle = xlContinuous
activworkbook.save
activworkbook.Close
ExcelApp.Visible = False
Set activsheet = Nothing
Set activworkbook = Nothing
Set ExcelApp = Nothing
Next
End sub |
Partager