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
| Sub ImportEtTraitementFichierCSV()
Dim Fic As String
Dim Dossier As String
Dim Nom_Tbl As String
Dim StrSql As String
Dim Nom_Tbl1 As String, Nom_Tbl2 As String, Nom_Tbl3 As String, Nom_Tbl4 As String
Dossier = "C:\Documents and Settings\MyDocuments\"
Fic = Dir(Dossier & "*.csv", vbNormal)
Import = "Import"
FileSystem = "Alpha"
VolumGroup = "Beta"
PhysicalVolum = "Gamma"
DetailsSan = "Delta"
On Error GoTo Erreur
Do While Fic <> ""
MsgBox Dossier & vbCrLf & Fic
' Supprimer tabe liée "Import"
If DCount("*", "MSysObjects", "[Type] In (1,6) And [Name]='Import'") > 0 Then
DoCmd.DeleteObject acTable, "Import"
End If
'Importation du fichier csv selectionné
DoCmd.TransferText acLinkDelim, "spec", "Import", Dossier & Fic, True
If DCount("*", "MSysObjects", "[Type] In (1,6) And [Name]='Import', And [Champ]='Alpha'") = True Then
'Insertion de toutes les informations concernant les Alpha
StrSql = "INSERT INTO [ " & Nom_Tbl1 & " ] (Champ1, Champ2, Champ3, Champ4, Champ5)"
StrSql = StrSql & " SELECT champ1, Champ2, Champ3, Champ4, Champ5 "
StrSql = StrSql & " FROM Import"
StrSql = StrSql & " WHERE Import.Champ4='Alpha'"
DoCmd.RunSQL StrSql
ElseIf DCount("*", "MSysObjects", "[Type] In (1,6) And [Name]='Import', And [Champ]='Beta'") = True Then
'Insertion de toutes les informations concernant les Beta
StrSql = "INSERT INTO [ " & Nom_Tbl2 & " ] (Champ1, Champ2, Champ3, Champ4, Champ5)"
StrSql = StrSql & " SELECT champ1, Champ2, Champ3, Champ4, Champ5 "
StrSql = StrSql & " FROM Import"
StrSql = StrSql & " WHERE Import.Champ4='Beta'"
DoCmd.RunSQL StrSql
ElseIf DCount("*", "MSysObjects", "[Type] In (1,6) And [Name]='Import', And [Champ]='Gamma'") = True Then
'Insertion de toutes les informations concernant les Gamma
StrSql = "INSERT INTO [ " & Nom_Tbl3 & " ] (Champ1, Champ2, Champ3, Champ4, Champ5)"
StrSql = StrSql & " SELECT champ1, Champ2, Champ3, Champ4, Champ5 "
StrSql = StrSql & " FROM Import"
StrSql = StrSql & " WHERE Import.Champ4='Gamma'"
DoCmd.RunSQL StrSql
ElseIf DCount("*", "MSysObjects", "[Type] In (1,6) And [Name]='Import', And [Champ]='Delta'") = True Then
'Insertion de toutes les informations concernant les Delta
StrSql = "INSERT INTO [ " & DetailsSan & " ] (Champ1, Champ2, Champ3, Champ4, Champ5)"
StrSql = StrSql & " SELECT champ1, Champ2, Champ3, Champ4, Champ5 "
StrSql = StrSql & " FROM Import"
StrSql = StrSql & " WHERE Import.Champ4='Delta' AND Import.Champ5='IS NOT NULL'"
DoCmd.RunSQL StrSql
End If
Fic = Dir()
Loop
Exit Sub
Erreur: |
Partager