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
|
'***************************************************
Private Sub Importer_dans_Table_Participant()
'***************************************************
' source onglet: InputWkb1.Worksheets(4) , la récupération des données commence ici à la ligne : row 1 (ligne des entetes )
' Import dans table T_ref_Domaine
'---------------------------------------------------
Dim InputSht1 As Worksheet
Dim Arr1() As Variant
Dim i As Long, j As Long
Dim LastRow As Long, LastCol As Long, strSql As String
Dim myDb As DAO.Database
Dim rs1 As DAO.Recordset
Dim StatusMsg As String, varReturn As Variant, lCount As Long, strTable As String
Dim strRegroupDefaultValue As String
Dim TimerDeBut As Double
TimerDeBut = Timer
strTable = "Participant"
StatusMsg = "Patientez, importation dans " & strTable & " en cours d'exécution..."
'Call GetDbFullpath(strTable) ' retourne la valeur de myDbData
Set myDb = CurrentDb
'strSql = "DELETE * FROM " & strTable
' myDb.Execute strSql, dbSeeChanges
'strSql = "ALTER TABLE " & strTable & " ALTER COLUMN Ref_id COUNTER (1,1)"
' myDb.Execute strSql, dbSeeChanges
'strSql = "INSERT INTO Participant ()"&"Values()"
' myDb.Execute strSql, dbSeeChanges
Set rs1 = myDb.OpenRecordset("SELECT * FROM " & strTable, dbOpenDynaset, dbSeeChanges)
Set InputSht1 = InputWkb1.Worksheets(6)
With InputSht1
LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row-5
LastCol = .UsedRange.Columns(.UsedRange.Columns.Count).Column
Arr1 = .Range(.Cells(B6, G6), .Cells(LastRow, LastCol))
End With
i = 1
For j = 1 To LastCol
Debug.Print "entête_colonne " & j & " : " & UCase(Arr1(1, j))
Next j
' Colonnes de la feuille "Colonnes Import"
' ----------------------------------------
' Info nb ligXL : 45
' Info nb colXL : 6
' entête_colonne 1:
' entête_colonne 2: CIVILITE
' entête_colonne 3: PRENOM
' entête_colonne 4: NOM
' entête_colonne 5: PAYS
' entête_colonne 6: TITRE
' entête_colonne 7: RAISON SOCIAL
' Table Participant
' -------------------
' SELECT IDParticipant, IDSociete, DateCreation, Civilite, Nom, Prenom, DateNaissance, Nationalite, Titre, Branche, GroupeListe, BadgeAmphi, ComplementAdresse, Rang, QualifRang, NbSubordonne, DateArriveeEntreprise, DateArriveeJob, TypeEtude, IDExperiencePrincipale, IDExperienceSecondaire, ExperiencePrincipale, ExperienceSecondaire, Marketing, Ventes, ProductionTechnique, RD, Informatique, ControleGestion, Finance, RH, Planning, Qualite, Autres, Langue1, QualifLangue1, Langue2, QualifLangue2, Langue3, QualifLangue3, TelephonePro, FaxPro, TelephoneMobile, EMail, password, AdressePersonnelle, TelephonePerso, TelephonePerso, VillePerso, PaysPerso, Section, Fonction, Commentaire, RaisonSociale, AdresseSociete, CodePostalSociete, VilleSociete, PaysSociete
Debug.Print "i max : " & UBound(Arr1, 1)
Debug.Print "j max : " & UBound(Arr1, 2)
varReturn = SysCmd(acSysCmdInitMeter, StatusMsg, UBound(Arr1, 1))
For i = 2 To UBound(Arr1, 1)
DoEvents
varReturn = SysCmd(acSysCmdUpdateMeter, i) 'Progression
If IsNothing(Arr1(i, 1)) Then
' 2012-05-25
'debug.print "la ligne " & i & " est vide, donc non importée"
Else
rs1.AddNew
For j = 1 To UBound(Arr1, 2)
'Debug.Print "[" & Nz(arr1(i, j), 0) & "]"
Select Case j
Case 2: rs1.Fields("Civilite") = Left(Trim(Nz(Arr1(i, j))), rs1.Fields("Civilite").Size)
Case 3: rs1.Fields("Prenom") = Left(Trim(Nz(Arr1(i, j))), rs1.Fields("Prenom").Size)
Case 4: rs1.Fields("Nom") = Left(Trim(Nz(Arr1(i, j))), rs1.Fields("Nom").Size)
Case 6: rs1.Fields("Titre") = Left(Trim(Nz(Arr1(i, j))), rs1.Fields("Titre").Size)
Case 7: rs1.Fields("RaisonSociale")= Left(Trim(Nz(Arr1(i, j))), rs1.Fields("RaisonSociale").Size)
End Select
Next j
rs1.Update
End If
Next i
debug.print DCount("*", strTable) & " records importés dans " & strTable
Exit_0:
varReturn = SysCmd(acSysCmdRemoveMeter)
varReturn = SysCmd(acSysCmdClearStatus)
Set rs1 = Nothing
Set myDb = Nothing
Set InputSht1 = Nothing
End Sub |
Partager