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
|
Private Sub btnExportStat_Click()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim rs, rs2 As DAO.Recordset
Dim strRequete, strSousRequete As String
Dim strNumPatch, strNumARS, strLibPatch, strLibARS, strPatchLie, strEtape, strStatutPatch, strOrdre As String
Dim strNumGDT, strDateMEP As String
Dim strNomFichier As String
Dim strCheminSave As String
On Error GoTo err_handler
logwrite "(btnExportStat_Click) Lancement export Fichier", "EXPORT"
' On vide la table
DoCmd.RunSQL "DELETE * FROM EXPORT_STAT_3"
' On alimente la table ONGLET_STAT_3
DoCmd.OpenQuery "Alimentation_Table_EXPORT_STAT_3"
' export Excel
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Add
xlBook.Sheets(3).Select
xlBook.Sheets(3).Name = "STAT03"
strRequete = "SELECT * FROM EXPORT_STAT_3 ORDER BY Ordre"
Set rs = CurrentDb.OpenRecordset(strRequete)
xlBook.Sheets("STAT03").Range("A1") = "Version"
xlBook.Sheets("STAT03").Range("B1") = "Ordre"
xlBook.Sheets("STAT03").Range("C1") = "Numéro Patch"
xlBook.Sheets("STAT03").Range("BI1") = "Composants JAR"
xlBook.Sheets("STAT03").Range("BJ1") = "Composants BINAIRE"
xlBook.Sheets("STAT03").Range("BK1") = "Composants SQL"
xlBook.Sheets("STAT03").Range("BL1") = "Composants PACKAGE"
xlBook.Sheets("STAT03").Range("A2").CopyFromRecordset rs
strNomFichier = Format(Date, "yyyymmdd") & "_ExportStat.xls"
strCheminSave = EnregistrerUnFichier(Me.hwnd, "Enregistrer sous", strNomFichier, "C:\")
If strCheminSave <> "" Then
xlBook.SaveAs strCheminSave
' xlApp.Visible = True
Else
xlApp.Quit
Set xlApp = Nothing
Exit Sub
End If
logwrite "(btnExportStat_Click) Fin export Fichier", "EXPORT"
MsgBox "Fichier exporté.", vbInformation, "Export"
rs.Close
Set rs = Nothing
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
Exit Sub
err_handler:
MsgBox "(btnExportStat_Click) Erreur : " & Err.Number & " " & Err.Description, vbCritical, "Erreur"
logwrite "(btnExportStat_Click) Erreur Application", "EXPORT"
' xlApp.Quit
' Set xlApp = Nothing
Exit Sub
End Sub |
Partager