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
| Private Sub importer_Click()
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(Me.NomChamp)
Set oWSht = oWkb.Worksheets(1)
'premier ligne ou on commence l'import
i = 16
'pour éviter les messages lors de l'ajout des enregistrements
DoCmd.SetWarnings False
'si la table existe déjà il y a un msgbox (module qui va avec)
If fExistTable(oWSht.Name) Then
MsgBox "Attention !! Cette Table a déjà été importer dans la Base de Donnée"
Else
DoCmd.RunSQL "CREATE TABLE " & oWSht.Name & "(ConfID Integer, HostEmail String, Duration Integer, Attendees Integer, Mois Integer, Année Integer, Sigle String);"
'tant que la cellule n'est pas vide
While oWSht.Range("A" & i).Value <> ""
If DCount("*", oWSht.Name, "[HostEmail] LIKE '" & oWSht.Cells(i, 7) & "*'") = 0 Then
cSQL = "insert into " & oWSht.Name & " ( [ConfID],[HostEmail],[Duration],[Attendees],[Mois],[Année]) values (" & Chr(34) & oWSht.Cells(i, 1) & Chr(34) & "," & Chr(34) & oWSht.Cells(i, 7) & Chr(34) & "," & Chr(34) & oWSht.Cells(i, 18) & Chr(34) & "," & Chr(34) & oWSht.Cells(i, 19) & Chr(34) & "," & Chr(34) & Right(oWSht.Name, 2) & Chr(34) & "," & Chr(34) & Left(oWSht.Name, 4) & Chr(34) & ")"
'exécute la requète
DoCmd.RunSQL cSQL
End If
i = i + 1
Wend
End If
DoCmd.SetWarnings True
End Sub
Private Sub executer_Click()
Dim db As DAO.Database, rst As DAO.Recordset, fld As DAO.Field
Dim sSQL As String
' Ouverture de la base de données
Set db = CurrentDb
sSQL = "SELECT HostEmail, Sigles FROM HostEmail et sigles ;"
' Ouverture du recordset
Set rst = db.OpenRecordset(sSQL, dbOpenForwardOnly, dbReadOnly)
While Not rst.EOF
DoCmd.RunSQL "UPDATE oWSht.Name set Sigle='" & rst(1) & "' WHERE HostEmail='" & rst(0) & "'"
rst.MoveNext
Wend
' Fermeture du Recordset
rst.Close
End Sub |
Partager