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
| Private Sub bt_exportXLS_Click()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim I As Long
Dim j As Long
Dim rec As Recordset
a = "\\...\Temp\Template_Liste.xlsx"
b = "\\...\Liste_" & Format(Now(), "yyyy" & "mm" & "dd") & "_" & Format(Now(), "hhnn") & ".xlsx"
FileCopy a, b
Set rec = Me.F_sf.Form.RecordsetClone
Set xlApp = Excel.Application
Set xlBook = xlApp.Workbooks.Open(b)
xlApp.Visible = True
Set xlSheet = xlBook.Worksheets("Liste")
'recopie les données à partir de la ligne 2
I = 2
Do While Not rec.EOF
For j = 0 To 4
xlSheet.Cells(I, 1) = rec.Fields("Code")
xlSheet.Cells(I, 2) = rec.Fields("Origine")
xlSheet.Cells(I, 3) = rec.Fields("Projet")
xlSheet.Cells(I, 4) = rec.Fields("Description")
Next j
I = I + 1
rec.MoveNext
Loop
'fermeture et libération des objets
xlBook.Save
rec.Close
Set rec = Nothing
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
End Sub |
Partager