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
| Sub Module1()
'Ici l'importation se déclenche en cliquant sur le bouton "Commande1"
'mais on peut aussi mettre ce code à l'ouverture d'un formulaire
Dim oApp As Excel.Application
Dim oWkb As Excel.Workbook
Dim oWSht As Excel.Worksheet
Set oApp = CreateObject("excel.application")
Set oWkb = oApp.Workbooks.Open("C:\Users\toshiba\Desktop\EXCEL_DD_20090127") 'mettez ici le chemin vers votre fichier Excel
Set oWSht = oWkb.Worksheets("EXCEL_DD_20090") 'mettez ici le nom de la feuille qui contient les données à importer
'première ligne ou commence l'import
i = 289
'pour éviter les messages lors de l'ajout des enregistrements
DoCmd.SetWarnings False
'on arrête l'importation lorsque le programme rencontre une case
'vide :
'While oWSht.Range("B" & 2517).Value <> "" '(où B représente la colonne et 2517 la ligne)
'condition de remplissage de la table => eviter les doublons
'si l'enregistrement existe déjà dans la table destination,
'on passe à la ligne suivante sans l'importer
'If DCount("*", "[Prod_globale]", "[nom_du_champ_destination_qui_ne_doit_pas_avoir_de_doublons] LIKE '" & oWSht.Cells(i, 9) & "'") = 0 Then
'le numéro 9 correspond au numéro de la colonne source, tel que : A=1, B=2, C=3 ...
'requète SQL (avec en paramètre la ligne i et le numéro de la colonne comme précisé au-dessus)
cSQL = "insert into [Prod_globale] ( [Energrid])values(" & Chr(34) & oWSht.Cells(i, 1) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [Jour])values(" & Chr(34) & oWSht.Cells(i, 1) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [Heure])values(" & Chr(34) & oWSht.Cells(i, 1) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [Pprod])values(" & Chr(34) & oWSht.Cells(i, 2) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [Pcons])values(" & Chr(34) & oWSht.Cells(i, 3) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [Ppc])values(" & Chr(34) & oWSht.Cells(i, 4) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [Pinj])values(" & Chr(34) & oWSht.Cells(i, 5) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [Pabs])values(" & Chr(34) & oWSht.Cells(i, 6) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [Pc1])values(" & Chr(34) & oWSht.Cells(i, 7) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [Pc2])values(" & Chr(34) & oWSht.Cells(i, 8) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [Pc3])values(" & Chr(34) & oWSht.Cells(i, 9) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [Pc4])values(" & Chr(34) & oWSht.Cells(i, 10) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [Pa])values(" & Chr(34) & oWSht.Cells(i, 11) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [Psi])values(" & Chr(34) & oWSht.Cells(i, 12) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [Pso])values(" & Chr(34) & oWSht.Cells(i, 13) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [Ia])values(" & Chr(34) & oWSht.Cells(i, 14) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [Isi])values(" & Chr(34) & oWSht.Cells(i, 15) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [Iso])values(" & Chr(34) & oWSht.Cells(i, 16) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [Va])values(" & Chr(34) & oWSht.Cells(i, 17) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [Vs])values(" & Chr(34) & oWSht.Cells(i, 18) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [T1])values(" & Chr(34) & oWSht.Cells(i, 19) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [T2])values(" & Chr(34) & oWSht.Cells(i, 20) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [Gi1])values(" & Chr(34) & oWSht.Cells(i, 21) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [Gi2])values(" & Chr(34) & oWSht.Cells(i, 22) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [Ve1])values(" & Chr(34) & oWSht.Cells(i, 23) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [Ve2])values(" & Chr(34) & oWSht.Cells(i, 24) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [Di1])values(" & Chr(34) & oWSht.Cells(i, 25) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [Di2])values(" & Chr(34) & oWSht.Cells(i, 26) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [TMST])values(" & Chr(34) & oWSht.Cells(i, 27) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [R1])values(" & Chr(34) & oWSht.Cells(i, 28) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [R2])values(" & Chr(34) & oWSht.Cells(i, 29) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [R3])values(" & Chr(34) & oWSht.Cells(i, 30) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [R4])values(" & Chr(34) & oWSht.Cells(i, 31) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [R5])values(" & Chr(34) & oWSht.Cells(i, 32) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [R6])values(" & Chr(34) & oWSht.Cells(i, 33) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [R7])values(" & Chr(34) & oWSht.Cells(i, 34) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [R8])values(" & Chr(34) & oWSht.Cells(i, 35) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [R9])values(" & Chr(34) & oWSht.Cells(i, 36) & Chr(34) & ");"
cSQL = "insert into [Prod_globale] ( [R10])values(" & Chr(34) & oWSht.Cells(i, 37) & Chr(34) & ");"
'ici, on dit dans quel table, il inserera quels champs.
'exécute la requète
DoCmd.RunSQL cSQL
'End If
'on incrémente la variable i pour passer à la ligne suivante
i = i + 1
'Wend
'on réactive les messages d'erreurs
DoCmd.SetWarnings True
End Sub
'End Sub |
Partager