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
|
Private Sub cmdImportData_Click()
On Error GoTo Err_
Dim sPath As String, sFileXls As String, sSQL As String, sMsg As String, sErrDesc As String, sFileType As String
Dim sTable As String, sInfo As String, sStatut As String
Dim bError As Boolean
Dim i As Long, lNbrRecord As Long, lErrNbr As Long
Dim dMaj As Date
'Définit le répertoire contenant les fichiers
sPath = Me.txtChemin 'Récupéré depuis le formulaire
sFileType = "xlsx"
'Boucle sur tous les fichiers xls du répertoire.
sFileXls = Dir(sPath & "*." & sFileType)
'Options de traitement des erreurs
DoCmd.SetWarnings True
Application.SetOption "Confirm Action Queries", False
Do While Len(sFileXls) > 0
bError = False
Set wrkCurrent = DBEngine.Workspaces(0)
wrkCurrent.BeginTrans
sSQL = "SELECT * FROM tImportFichierXls WHERE ficherExcel= '" & sFileXls & "'"
Set recSet = CurrentDb.OpenRecordset(sSQL)
If Not recSet.EOF Then
sTable = recSet!Table
'Supprime tous les enrgistrements de la table master
sSQL = "DELETE * FROM " & recSet!Table
CurrentDb.Execute sSQL, dbFailOnError
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel8, sTable & sFileType, sPath & sFileXls, True
sSQL = "INSERT INTO " & recSet!Table & " SELECT * FROM " & sTable & sFileType
CurrentDb.Execute sSQL, dbFailOnError
If bError = False Then 'Si pas d'erreur de chargement
wrkCurrent.CommitTrans
Else 'Si erreur de chargement
wrkCurrent.Rollback
End If
wrkCurrent.Close
Set wrkCurrent = Nothing
sSQL = "DROP TABLE " & sTable & sFileType
CurrentDb.Execute sSQL, dbFailOnError
sSQL = "SELECT * FROM tImportFichierXls WHERE table= '" & sTable & "'"
Set recSet = CurrentDb.OpenRecordset(sSQL)
If Not recSet.EOF Then
recSet.Edit
recSet!DateLastMaj = Now()
If bError = False Then 'Si pas d'erreur de chargement
recSet!Statut = "Ok"
recSet!info = ""
Else 'Si erreur de chargement
recSet!Statut = "Echec"
recSet!info = sErrDesc
End If
recSet.Update
End If
recSet.Close
Set recSet = Nothing
End If
sFileXls = Dir() 'Récpére le fichier Excel suivant
Loop
sSQL = "SELECT * FROM tImportFichierXls WHERE statut = 'Echec'"
Set recSet = CurrentDb.OpenRecordset(sSQL)
If Not recSet.EOF Then
sMsg = "Fin du traitement d'importation" & Chr(13) & "Erreur(s) de chargement! Voir l'état du statut des importations"
MsgBox sMsg, vbCritical
Else
sMsg = "Fin du traitement d'importation" & Chr(13) & "Opération réussie"
MsgBox sMsg, vbExclamation
End If
recSet.Close
Set recSet = Nothing
Exit_Err_:
Exit Sub
Err_:
bError = True
sErrDesc = sTable & " n'a pas été mise à jour. " & Err.Description & " " & Err.Number
lErrNbr = Err.Number
MsgBox sErrDesc, vbCritical
Resume Next
End Sub |
Partager