Mise à jour table Access à partir d'Excel

6 réponses
Avatar
Lunettes_Bleues
Bonjour,
En ann=E9e 1
J'ai plusieurs fichiers Excel, provenant d'organismes diff=E9rents, mais av=
ec une stucture des donn=E9es strictement similaire d'un fichier =E0 l'autr=
e.
J'ai une base de donn=E9es Access, avec une table, qui a la m=EAme structur=
e que les plages de donn=E9es des fichiers Excel.
Pour assembler les donn=E9es des diverses sources, j'int=E8gre =E0 chaque f=
ichier Excel une macro d'exportation vers la table Access. Il s'agit d'ins=
=E9rer des donn=E9es nouvelles (INSERT)
Jusque l=E0 tout va bien.
En ann=E9e 2
Je re=E7ois les fichiers Excel des m=EAmes organismes.
J'ai de nouvelles donn=E9es, mais aussi des donn=E9es de l'ann=E9e 1 pour l=
esquelles certains montants peuvent avoir chang=E9 par rapport =E0 l'ann=E9=
e 1 (M=EAme ligne que ann=E9e 1 avec un =E9l=E9ment =E0 mettre =E0 jour).
C'est pour ces mises =E0 jour que j'ai besoin d'un coup de main expert.
Il faut que j'exporte les nouvelles lignes et que je mettre =E0 jour le cha=
mp "montant" de certaines lignes qui existaient en ann=E9e 1.
Je pr=E9cise que pour identifier les lignes existantes il faut balayer troi=
s champs (trois crit=E8res).
J'ai vu des solutions ici et l=E0 sur des forums mais je n'arrive pas =E0 l=
es adapter.
Je suis sur Office 2003.
Je remercie les personnes qui pourraient m'aider.
Bien =E0 vous toutes et tous.

6 réponses

Avatar
MichD
Bonjour,

Voici une façon de faire en utilisant ADO (Activex Data Object)

Tu dois ajouter au projet VBA du fichier Excel, la référence
suivante : Microsoft Activex Data objects XX library
Sous Excel 2013 XX est 6.1 , Excel 2003 = ?

Aux fins de l'exemple, j'ai supposé que j'avais un
tableau de données en Feuil1 et que je voulais comparer
chacune des lignes de ce tableau à la table déjà existante
dans Access.
Pour ce qui est des conditions de mon exemple, si les 3
premiers champs sont identiques, alors on procède automatiquement
à la mise à jour de la valeur du champ 4 de la table accès
Dans le cas contraire, on ajoute cet enregistrement à la table.

À toi d'adapter :

N.B. Je ne suis pas un adapte d'Access, si ton tableau de donner est
très grand, Il y a peut-être de gurus d'Access qui connaisse une façon
d'écrire des requêtes plus performantes...

'---------------------------------------------------
Sub RequêteAvecADO_Feuille_test()

'Requiert la bibliothèque suivante :
'barre des menus / outils / référence :
'"Microsoft Activex Data objects 2.8 library"

Dim Conn As ADODB.Connection, Rst As New ADODB.Recordset
Dim Requete As String, C As Range
Dim File As String, Rg As Range

With Worksheets("Feuil1") 'Nom Feuille à adapter
'Ligne 1 étant les étiquettes de colonnes
Set Rg = .Range("A2:B" & .Range("A65536").End(xlUp).Row)
End With

'Chemin & Fichier sur lequel se fera la requête :
File = "D:Comptoir.mdb"

Set Conn = New ADODB.Connection
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & File & " "

For Each C In Rg
'La requête qui sera exécutée.
Requete = "SELECT * From Employés Where [N° employé]=" & C.Value & "" &
_
" And Nom Like '" & C.Offset(, 1).Value & "'" & _
" And Prénom Like '" & C.Offset(, 2).Value & "'"

'Exécution de la requête
Rst.Open Requete, Conn, adOpenDynamic, adLockOptimistic

'S'il y a au moins un enregistrement répondant au critère
If Rst.EOF = False Then
'On remplace à coup sûr la valeur du champ 4
Rst(3).Value = C.Offset(, 3).Value
Else
'si l'enregistrement n'est pas trouvé alors on l'ajoute
With Rst
.AddNew
'Dans mon exemple, rst(0) est la clé primaire de la table
'Elle ne peut pas contenir de doublon, il faut trouver un
'moyen d'ajouter une valeur selon la table que l'on a
'sans que ce soit un doublon pour éviter les messages
'd'erreur et du refus d'access d'ajouter le nouvel
'enregitrement
Rst(0) = 20 '<<==== Clé primaire
Rst(1) = C.Offset(, 1)
Rst(2) = C.Offset(, 2)
Rst(3) = C.Offset(, 3)
'On peut y inclure autant de champs que l'on désires...
End With
End If
'Mise à jour de l'enregistrement
Rst.Update
'ferme l'enregistrement
Rst.Close
'on passe à la ligne suivante dans le tableau Excel
Next
'Ferme la connexion à Access
Conn.Close
'Vide l'espace mémoire occupé par les objets
Set Rst = Nothing: Set Conn = Nothing
Set Rg = Nothing: Set C = Nothing
End Sub
'---------------------------------------------


MichD
---------------------------------------------------------------
"Lunettes_Bleues" a écrit dans le message de groupe de discussion :


Bonjour,
En année 1
J'ai plusieurs fichiers Excel, provenant d'organismes différents, mais avec
une stucture des données strictement similaire d'un fichier à l'autre.
J'ai une base de données Access, avec une table, qui a la même structure que
les plages de données des fichiers Excel.
Pour assembler les données des diverses sources, j'intègre à chaque fichier
Excel une macro d'exportation vers la table Access. Il s'agit d'insérer des
données nouvelles (INSERT)
Jusque là tout va bien.
En année 2
Je reçois les fichiers Excel des mêmes organismes.
J'ai de nouvelles données, mais aussi des données de l'année 1 pour
lesquelles certains montants peuvent avoir changé par rapport à l'année 1
(Même ligne que année 1 avec un élément à mettre à jour).
C'est pour ces mises à jour que j'ai besoin d'un coup de main expert.
Il faut que j'exporte les nouvelles lignes et que je mettre à jour le champ
"montant" de certaines lignes qui existaient en année 1.
Je précise que pour identifier les lignes existantes il faut balayer trois
champs (trois critères).
J'ai vu des solutions ici et là sur des forums mais je n'arrive pas à les
adapter.
Je suis sur Office 2003.
Je remercie les personnes qui pourraient m'aider.
Bien à vous toutes et tous.
Avatar
Lunettes_Bleues
On 14 fév, 18:46, "MichD" wrote:
Bonjour,

Voici une fa on de faire en utilisant ADO (Activex Data Object)

Tu dois ajouter au projet VBA du fichier Excel, la r f rence
suivante : Microsoft Activex Data objects XX library
Sous Excel 2013 XX est 6.1 , Excel 2003 = ?

Aux fins de l'exemple, j'ai suppos que j'avais un
tableau de donn es en Feuil1 et que je voulais comparer
chacune des lignes de ce tableau la table d j existante
dans Access.
Pour ce qui est des conditions de mon exemple, si les 3
premiers champs sont identiques, alors on proc de automatiquement
la mise jour de la valeur du champ 4 de la table acc s
Dans le cas contraire, on ajoute cet enregistrement la table.

toi d'adapter :

N.B. Je ne suis pas un adapte d'Access, si ton tableau de donner est
tr s grand, Il y a peut- tre de gurus d'Access qui connaisse une fa on
d' crire des requ tes plus performantes...

'---------------------------------------------------
Sub Requ teAvecADO_Feuille_test()

'Requiert la biblioth que suivante :
'barre des menus / outils / r f rence :
'"Microsoft Activex Data objects 2.8 library"

Dim Conn As ADODB.Connection, Rst As New ADODB.Recordset
Dim Requete As String, C As Range
Dim File As String, Rg As Range

With Worksheets("Feuil1") 'Nom Feuille adapter
    'Ligne 1 tant les tiquettes de colonnes
    Set Rg = .Range("A2:B" & .Range("A65536").End(xlUp).Row)
End With

'Chemin & Fichier sur lequel se fera la requ te :
File = "D:Comptoir.mdb"

Set Conn = New ADODB.Connection
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & File & "  "

For Each C In Rg
    'La requ te qui sera ex cut e.
    Requete = "SELECT * From Employ s Where [N employ ]=" & C.Val ue & "" &
_
    " And Nom Like '" & C.Offset(, 1).Value & "'" & _
    " And Pr nom Like '" & C.Offset(, 2).Value & "'"

    'Ex cution de la requ te
    Rst.Open Requete, Conn, adOpenDynamic, adLockOptimistic

    'S'il y a au moins un enregistrement r pondant au crit re
    If Rst.EOF = False Then
        'On remplace coup s r la valeur du champ 4
        Rst(3).Value = C.Offset(, 3).Value
    Else
    'si l'enregistrement n'est pas trouv alors on l'ajoute
        With Rst
            .AddNew
            'Dans mon exemple, rst(0) est la cl primaire de l a table
            'Elle ne peut pas contenir de doublon, il faut tr ouver un
            'moyen d'ajouter une valeur selon la table que l' on a
            'sans que ce soit un doublon pour viter les messa ges
            'd'erreur et du refus d'access d'ajouter le nouve l
            'enregitrement
            Rst(0) = 20  '<<==== Cl primaire
            Rst(1) = C.Offset(, 1)
            Rst(2) = C.Offset(, 2)
            Rst(3) = C.Offset(, 3)
            'On peut y inclure autant de champs que l'on d si res...
        End With
    End If
    'Mise jour de l'enregistrement
    Rst.Update
    'ferme l'enregistrement
    Rst.Close
    'on passe la ligne suivante dans le tableau Excel
Next
'Ferme la connexion Access
Conn.Close
'Vide l'espace m moire occup par les objets
Set Rst = Nothing: Set Conn = Nothing
Set Rg = Nothing: Set C = Nothing
End Sub
'---------------------------------------------

MichD
---------------------------------------------------------------
"Lunettes_Bleues"  a crit dans le message de groupe de discussion :


Bonjour,
En ann e 1
J'ai plusieurs fichiers Excel, provenant d'organismes diff rents, mais av ec
une stucture des donn es strictement similaire d'un fichier l'autre.
J'ai une base de donn es Access, avec une table, qui a la m me structure que
les plages de donn es des fichiers Excel.
Pour assembler les donn es des diverses sources, j'int gre chaque fichier
Excel une macro d'exportation vers la table Access. Il s'agit d'ins rer d es
donn es nouvelles (INSERT)
Jusque l tout va bien.
En ann e 2
Je re ois les fichiers Excel des m mes organismes.
J'ai de nouvelles donn es, mais aussi des donn es de l'ann e 1 pour
lesquelles certains montants peuvent avoir chang par rapport l'ann e 1
(M me ligne que ann e 1 avec un l ment mettre jour).
C'est pour ces mises jour que j'ai besoin d'un coup de main expert.
Il faut que j'exporte les nouvelles lignes et que je mettre jour le champ
"montant" de certaines lignes qui existaient en ann e 1.
Je pr cise que pour identifier les lignes existantes il faut balayer troi s
champs (trois crit res).
J'ai vu des solutions ici et l sur des forums mais je n'arrive pas les
adapter.
Je suis sur Office 2003.
Je remercie les personnes qui pourraient m'aider.
Bien vous toutes et tous.



Bonjour,

J'ai essayé ta solution et elle ne marche pas pour le moment.
Ni pour les mises à jour, ni pour l'ajout de nouvelles lignes.
J'ai sans arrêt des messages d'erreur.
J'ai rédigé Requete ainsi
Requete = "SELECT * From Tbl_Depense_OC Where Concatener like'" &
C.Value & "'" & _
" And Annee Like '" & C.Offset(, 1).Value & "'" & _
" And Exercice Like '" & C.Offset(, 2).Value & "'"
et je n'ai plus de message d'erreur à ce niveau là, mais la suite
n'avance pas.
Le champ Concatener, concatène dans la base Excel trois champs
différents qui font pour chaque ligne un index unique. Le champ est en
texte dans Excel et dans Access.
Merci pour ton attention,
Avatar
MichD
| Requete = "SELECT * From Tbl_Depense_OC Where Concatener like'" &

**** sur cette ligne, il te manque un espace après "Like"

Si dans ta table Access tu as UN champ unique "Concatener"
qui correspond au contenu des 3 colonnes de la ligne de la
feuille de données Excel, fais comme ceci en tenant compte
de ce tu as mis entre la valeur de chacune des colonnes
de la feuille d'Excel dans ta table Access.

Exemple : Si dans la ligne B1:B3, il y a Toto Toto1 Toto2
Dans le champ "Concatener" de ta base Access, il y a-t-il
un espace? Une virgule? Où un retour à la ligne entre la valeur
des "toto"?

Tu dois reproduire exactement la syntaxe du champ que
tu as mis dans ta table Access. Dans mon exemple, j'ai utilisé
le "VBCRLF" mais cela pourrait être un espace ou une virgule
entre guillemets.

Mon Champ = "'" & C.Value & vbCrLf & _
C.Offset(, 1).Value & vbCrLf & _
C.Offset(, 2).Value & "'"

Requete = "SELECT * From Tbl_Depense_OC Where Concatener like MonChamp

Difficile d'aller plus loin que cela sans avoir les outils sous les yeux!


MichD
---------------------------------------------------------------
Avatar
Lunettes_Bleues
On 18 fév, 16:20, "MichD" wrote:
| Requete = "SELECT * From Tbl_Depense_OC Where Concatener like'" &

**** sur cette ligne, il te manque un espace apr s "Like"

Si dans ta table Access tu as UN champ unique "Concatener"
qui correspond au contenu des 3 colonnes de la ligne de la
feuille de donn es Excel, fais comme ceci en tenant compte
de ce tu as mis entre la valeur de chacune des colonnes
de la feuille d'Excel dans ta table Access.

Exemple :  Si dans la ligne B1:B3, il y a Toto Toto1 Toto2
Dans le champ "Concatener" de ta base Access, il y a-t-il
un espace? Une virgule? O un retour la ligne entre la valeur
des "toto"?

Tu dois reproduire exactement la syntaxe du champ que
tu as mis dans ta table Access. Dans mon exemple, j'ai utilis
le "VBCRLF" mais cela pourrait tre un espace ou une virgule
entre guillemets.

Mon Champ = "'" & C.Value & vbCrLf & _
     C.Offset(, 1).Value & vbCrLf & _
     C.Offset(, 2).Value & "'"

Requete = "SELECT * From Tbl_Depense_OC Where Concatener like  MonCha mp

Difficile d'aller plus loin que cela sans avoir les outils sous les yeux!

MichD
---------------------------------------------------------------


Bonjour,
Je te remercie pour ces précisions et pour la rapidité de ta réponse.
Dès que je peux m'y remettre, je te tiens au courant.
Bonne journée à toi.
Avatar
Lunettes_Bleues
Le mardi 19 février 2013 09:22:49 UTC+1, Lunettes_Bleues a écrit :
On 18 fév, 16:20, "MichD" wrote: > | Requete = "SELECT * From Tbl_Depense_OC Where Concatener like'" & > > **** sur ce tte ligne, il te manque un espace apr s "Like" > > Si dans ta table Access tu as UN champ unique "Concatener" > qui correspond au contenu des 3 colonn es de la ligne de la > feuille de donn es Excel, fais comme ceci en tenant compte > de ce tu as mis entre la valeur de chacune des colonnes > de la fe uille d'Excel dans ta table Access. > > Exemple :  Si dans la ligne B1:B3 , il y a Toto Toto1 Toto2 > Dans le champ "Concatener" de ta base Access, i l y a-t-il > un espace? Une virgule? O un retour la ligne entre la valeur > des "toto"? > > Tu dois reproduire exactement la syntaxe du champ que > tu as mis dans ta table Access. Dans mon exemple, j'ai utilis > le "VBCRLF" m ais cela pourrait tre un espace ou une virgule > entre guillemets. > > Mon Champ = "'" & C.Value & vbCrLf & _ >      C.Offset(, 1).Value & vbC rLf & _ >      C.Offset(, 2).Value & "'" > > Requete = "SELECT * Fr om Tbl_Depense_OC Where Concatener like  MonChamp > > Difficile d'aller p lus loin que cela sans avoir les outils sous les yeux! > > MichD > -------- ------------------------------------------------------- Bonjour, Je te reme rcie pour ces précisions et pour la rapidité de ta réponse. Dès que je peux m'y remettre, je te tiens au courant. Bonne journée à toi.



Bonjour à nouveau,

J'ai finalement adapté un autre code, qui permet soit de mettre à jour les données existantes, soit d'ajouter de nouvelles lignes le cas éch éant.

' MACRO ADAPTÉE


'Référence-VBAPoject : Microsolft DAO 3.6 Object Library
'Excel vers Access
'Module VBA à insérer > Set DBX = OpenDatabase(...)
Sub Mise_A_Jour_Export_Vers_Access()

Dim DB As Database, DBX As Database, sDestination As String, gExpDB, sTable , idxFrom, idxTo, idxFromName, sType, gsSQLDB
Dim RS As DAO.Recordset, TableEnCour As DAO.Recordset
Dim fldLoop As Field, tdfNew As TableDef

Dim Msg, Style, Title, Response, MyString

Calculate

'On Error Resume Next
'Set DBX = OpenDatabase("G:Statistiques CMUCOUTS MOYENS OCOC_2012Enqu etesCM2011_Tableaux.xls", False, gnReadOnly, "Excel 5.0;")
Set DBX = OpenDatabase(ThisWorkbook.Path & "" & ThisWorkbook.Name, False , gnReadOnly, "Excel 5.0;")

Set TableEnCour = DBX.OpenRecordset("Bdd_Bdd_Recap_Poste", dbOpenDynaset, dbSeeChanges, dbOptimistic)

sDestination = "G:Controle des dépenses et connaissance des OCBases A ccessDepense_OCBdd_Depense_OC.mdb"

sConnect = ";pwd="
Set DB = OpenDatabase(sDestination, False, gnReadOnly, sConnect)

Do Until TableEnCour.EOF
'Set RS = DB.OpenRecordset("select * FROM [Tbl_Depense_OC] WHERE ((([Tbl_ Depense_OC].[Concatener]) Like '" & TableEnCour.Fields(0).Value & "'))", db OpenDynaset, dbSeeChanges, dbOptimistic)
Set RS = DB.OpenRecordset _
("select * FROM [Tbl_Depense_OC]" & _
" WHERE (" & _
"(([Tbl_Depense_OC].[Annee]) Like '" & TableEnCour.Fields(0).Value & "')" & _
"AND " & _
"(([Tbl_Depense_OC].[Exercice]) Like '" & TableEnCour.Fields(1).Value & "') " & _
"AND " & _
"(([Tbl_Depense_OC].[Prestation]) Like '" & TableEnCour.Fields(5).Value & " ')" & _
"AND " & _
"(([Tbl_Depense_OC].[Num_Siren]) Like '" & TableEnCour.Fields(8).Value & "' )" & _
")", _
dbOpenDynaset, dbSeeChanges, dbOptimistic)

If RS.RecordCount = 0 Then
RS.AddNew
Else
RS.Edit
End If

' Fields
'Annee (0)
'Exercice (1)
'Forfait (2)
'Ville (3)
'Detail (4)
'Prestation (5)
'Montant (6)
'Nom_Organisme (7)
'Num_Siren (8)
'Date_Saisie (9)

RS.Fields(0).Value = TableEnCour.Fields(0).Value
RS.Fields(1).Value = TableEnCour.Fields(1).Value
RS.Fields(2).Value = TableEnCour.Fields(2).Value
RS.Fields(3).Value = TableEnCour.Fields(3).Value
RS.Fields(4).Value = TableEnCour.Fields(4).Value
RS.Fields(5).Value = TableEnCour.Fields(5).Value
RS.Fields(6).Value = TableEnCour.Fields(6).Value
RS.Fields(7).Value = TableEnCour.Fields(7).Value
RS.Fields(8).Value = TableEnCour.Fields(8).Value
RS.Fields(9).Value = TableEnCour.Fields(9).Value
RS.Update
TableEnCour.MoveNext
Loop

TableEnCour.Close
DB.Close
Set DB = Nothing
Set TableEnCour = Nothing

' Inscription Date et Heure de l'export
With Sheets("Saisie_OC")
.Select
.Unprotect
.Range("G32").Value = Date
.Range("G33").Value = Time
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowUsingPivotTables:=True
End With


ActiveWorkbook.Save



Msg = "Export vers Access effectué" ' Définit le message.
Style = vbOKOnly + vbInformation + vbDefaultButton2 ' Définit les bo utons.
Title = "Mise à jour des données" ' Définit le titre.
' Affiche le message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)



End Sub

Sinon, pour des tables plus importantes, j'ai préparé une requête com mandée par Excel, qui supprime les données existante selon deux critè res (AND) et qui ajoute les nouvelles données ou les nouvelles lignes.
C'est plus rapide que le balayage de la solution ci-dessus.
Il faut voir dans la durée ce que tout cela donne.
À bientôt.

SR
Avatar
MichD
Merci du retour.

Bon travail!


MichD
---------------------------------------------------------------