Se connecter / S'enregistrer

Résolu Importation données Excel vers Access Via une procédure VBA

Solutions (9)
Tags :
  • table
  • Microsoft Excel
  • Programme
  • Programmation
|
Bonjour,

Ds le cadre d'un projet personnel,

Je dois importer des données d'un fichier Excel vers une table Access déjà existante

Nom du fichier Excel : Assainissement
Nom de la table : rapport_asst

Dans un premier temps, Je dois faire une recherche :

Je m'explique : dans ma case B3 de mon fichier Excel il faut mettre le nom d'une commune sachant que mes communes sont listés dans ma table Access :

Je dois donc faire une recherche pour que le programme trouve dans quelle ligne (ligne correspondant a la commune) il doit inserer les données suivantes .

Dans un second temps, Je dois faire en sorte que les données soit transférer ds ma table access à la ligne correspondante.

Le problème c'est que je suis amaateur en Vba que je rame et que je ne sais meme pas où commencer !

Merci d'avance pour votre aide,
Si besoin de plus de renseignements pour m'aider, Hésitez pas ;p

Michoo27. :hello: 
Contenus similaires
Meilleure solution
partage
|
Bonjour,

Que ce soit de l'importation ou de l'exportation de données entre Excel et Access, il faut passer par des composants ADO.

Dans ton cas, il s'agit d'une exportation, donc voici un petit exemple à modifier selon ton besoin :
  1. Sub ADOFromExcelToAccess()
  2. ' exports data from the active worksheet to a table in an Access database this procedure must be edited before use
  3. Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
  4.  
  5. ' connect to the Access database
  6. Set cn = New ADODB.Connection
  7. cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data Source=C:\FolderName\DataBaseName.mdb;"
  8.  
  9. ' open a recordset
  10. Set rs = New ADODB.Recordset
  11. rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable
  12.  
  13. ' all records in a table
  14.  
  15. r = 3 ' the start row in the worksheet
  16. Do While Len(Range("A" & r).Formula) > 0
  17. ' repeat until first empty cell in column A
  18. With rs
  19. .AddNew ' create a new record
  20.  
  21. ' add values to each field in the record
  22. .Fields("FieldName1") = Range("A" & r).Value
  23. .Fields("FieldName2") = Range("B" & r).Value
  24. .Fields("FieldNameN") = Range("C" & r).Value
  25. ' add more fields if necessary...
  26.  
  27. .Update ' stores the new record
  28. End With
  29. r = r + 1 ' next row
  30. Loop
  31.  
  32. rs.Close
  33. Set rs = Nothing
  34. cn.Close
  35. Set cn = Nothing
  36. End Sub


Quelques explications :

Déclarations des variables :
  1. Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long


Définie les variables 'cn' et 'rs' en tant que (respectivement) 'ADODB.Connection' et 'ADODB.Recordset'.

Le premier, 'cn' (ADODB.Connection), est nécessaire pour définir les différents paramètres de connection (la base (ici le fichier mdb), protocole, mot de passe, etc.)
Nous décrirons cela plus tard.

Le second, 'rs' (ADODB.Recordset), permet les intéractions avec la base de données : SELECT, INSERT, etc. C'est l'objet le plus intéressant de ADODB. Nous décrirons l'utilisation ultérieurement.

Configurer la connection :
  1. Set cn = New ADODB.Connection
  2. cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data Source=C:\FolderName\DataBaseName.mdb;"


Donc ligne 1 : on crée un nouvel objet ADODB.Connection
Ligne 2 : on définie la connection avec le "provider" Jet.OLEDB de Microsoft (nécessaire pour Access), et une base dans 'C:\ForderName\' qui se nomme 'DataBaseName.mdb'. Il faut bien sur changer tout ça avec ce qui te convient (pour le second, le 'Provider' n'est pas à modifier).

Configurer le RecordSet :
  1. Set rs = New ADODB.Recordset
  2. rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable


Donc pareil, ligne 1 : on crée un nouvel objet ADODB.Recordset
Ligne 2 : on configure la portée du Record, en l'occurence sur quelle table, avec quelle connection ('cn'), etc.

On peut également ouvrir un recordset avec une requête SQL :
  1. rs.Open "SELECT * FROM " & TableName & " WHERE [FieldName] = 'MyCriteria'", cn, , , adCmdText


Donc il y aura un filtre dans ce cas là (et possibilité de changer 1 champs déjà présent).

Fermeture de la connection ADO :
  1. rs.Close
  2. Set rs = Nothing
  3. cn.Close
  4. Set cn = Nothing


Oui je sais, c'est la fin, mais il me semble important de le voir avant l'utilisation du RecordSet.
Donc ici, il faut bien faire attention à l'ordre de fermeture des objets... En l'occurence, c'est généralement l'inverse de l'ouverture. Donc :
ADODB.Connection.Open > ADODB.Recordset.Open devient ADODB.Recordset.Close > ADODB.Connection.Close

L'affectation à 'Nothing' n'est qu'une manière de détruire l'objet en mémoire... Il est possible de les réunir après la fermeture, peu importe :
  1. rs.Close
  2. cn.Close
  3. Set rs = Nothing
  4. Set cn = Nothing


Attention : Il faut toujours fermer ces connections.

Et maintenent, le plus important, l'utilisation en insertion/exportation :
  1. r = 3 ' the start row in the worksheet
  2. Do While Len(Range("A" & r).Formula) > 0
  3. ' repeat until first empty cell in column A
  4. With rs
  5. .AddNew ' create a new record
  6. ' add values to each field in the record
  7. .Fields("FieldName1") = Range("A" & r).Value
  8. .Fields("FieldName2") = Range("B" & r).Value
  9. .Fields("FieldNameN") = Range("C" & r).Value
  10. ' add more fields if necessary...
  11. .Update ' stores the new record
  12. End With
  13. r = r + 1 ' next row
  14. Loop


Donc, ici je vais parcourir toutes les lignes (à partir de la 3) en fonction de la colonne 'A'. C'est ceci qui le spécifie :
  1. r = 3 ' the start row in the worksheet
  2. Do While Len(Range("A" & r).Formula) > 0
  3. << ... >>
  4. r = r + 1 ' next row
  5. Loop


Donc tant qu'il y a une formule de définie dans la colonne 'A', on crée un nouvel élément dans la base de données :
  1. rs.AddNew

Et on affecte les valeurs des colonnes dans les différents champs :
  1. rs.Fields("FieldName1") = Range("A" & r).Value
  2. rs.Fields("FieldName2") = Range("B" & r).Value
  3. rs.Fields("FieldNameN") = Range("C" & r).Value

Pour finir, on "envoie"/"met à jour" la base de données vis à vis du RecordSet :
  1. rs.Update


Dans notre cas nous avons utilisé la commande 'With' qui permet de ne pas répéter 'rs' devant chaque commande, mais ça revient au même :
  1. With rs
  2. .AddNew
  3. .Fields("FieldName1") = Range("A" & r).Value
  4. .Fields("FieldName2") = Range("B" & r).Value
  5. .Fields("FieldNameN") = Range("C" & r).Value
  6. .Update
  7. End With

ou
  1. rs.AddNew
  2. rs.Fields("FieldName1") = Range("A" & r).Value
  3. rs.Fields("FieldName2") = Range("B" & r).Value
  4. rs.Fields("FieldNameN") = Range("C" & r).Value
  5. rs.Update

C'est pareil, juste 2 lignes en plus avec le 'With' ...


Voila, avec ça tu devrais pouvoir t'en sortir.

Et n'oublie pas : Google est ton ami

Bon courage.
  • Commenter cette solution |
Score
1
òh
òi
|
Bonjour,

Voici mon code actuel :

Citation :
Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long

' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data Source=C:\Users\Mickaël\Documents\Archi_Log_1ere_année\Stage\BDD2_STAGEw.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "rapport_annuel_eau_potable", cn, adOpenKeyset, adLockOptimistic, adCmdTable

' all records in a table
r = 3 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("annee_d_Exercice") = Range("B3").Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub



Quand je compile :



Pour ai-je cette erreur ? ça fait je sais pas combien de temps que j'essaie de la resoudre mais rien ny change :( 

Merci
  • Commenter cette réponse |
Score
1
òh
òi
|
As-tu déjà utilisé une base de données ?

En SQL c'est assez facile, si t'es sur que le département existe :

  1. "UPDATE 'nom de table' SET 'colonne X' = " & Range("C" & r).Value & "WHERE 'département' = " & Range("B" & r).Value


Voila un piti exemple en SQL cumulé avec du Excel.

Et dans du ADO en plus ça donnerais :

  1. Dim cd As New ADODB.Command
  2. cd.ActiveConnection = cn
  3. cd.CommandText = "UPDATE 'nom de table' SET 'colonne X' = " & Range("C" & r).Value & "WHERE 'département' = " & Range("B" & r).Value
  4. cd.Execute



Si je remets ça dans la boucle précédente :

  1. Sub ADOFromExcelToAccess()
  2. ' exports data from the active worksheet to a table in an Access database this procedure must be edited before use
  3. Dim cn As ADODB.Connection, cd As New ADODB.Command, r As Long
  4.  
  5. ' connect to the Access database
  6. Set cn = New ADODB.Connection
  7. cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data Source=C:\FolderName\DataBaseName.mdb;"
  8. rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable
  9. ' set up Command connection
  10. cd.ActiveConnection = cn
  11.  
  12.  
  13. ' all records in a table
  14. r = 3 ' the start row in the worksheet
  15. Do While Len(Range("A" & r).Formula) > 0
  16. ' repeat until first empty cell in column A
  17. cd.CommandText = "UPDATE 'nom de table' SET 'colonne X' = " & Range("C" & r).Value & "WHERE 'département' = " & Range("B" & r).Value
  18. cd.Execute
  19.  
  20. r = r + 1 ' next row
  21. Loop
  22.  
  23. cn.Close
  24. Set cn = Nothing
  25. End Sub


C'est juste un exemple d'utilisation (non testé), mais ça doit pas être trop éloigné de ton but...
  • Commenter cette réponse |
Score
0
òh
òi
|
Meilleure réponse sélectionnée par michoo27.
  • Commenter cette réponse |
Score
0
òh
òi
|
Bonjour,

ça marche enfin ! Merci :) 

Par contre, Faut que je fasse une recherche :

Dans la cellule B7, Il y a le nom de commune à remplir !
Dans la table en question, il y a une 100aine de commune de listées !
Donc ils faut que les données soit importé dans la bonne ligne de la table Access !

Comment fais-t-on ?

Merci d'avance de votre aide,

Bonne journée !
  • Commenter cette réponse |
Score
0
òh
òi
|
-_-

Sous Access ??? J'ai jamais parlé de mettre du VB dans Access, uniquement dans Excel.

Je savais pas ce que tu utilisais, si tu passais par un programme VB complet avec Visual Studio ou pas... D'ailleurs, c'est une bonne question, tu veux que ce soit fais comment ?

J'ai testé mon tout premier code dans une feuille Excel, ça marche niquel...

Donc expliques moi plus en détails ce que tu souhaites, car là avec mon premier message je répondais déjà entièrement au titre "Procédure VBA : copie de données Excel dans Access" mais à partir d'une feuille Excel. Et avec mon second message, tu as les techniques pour le faire en-dehors de Excel... (Visual Studio par exemple).

Dans la feuille excel j'ai fais une procédure VBA avec l'éditeur présent dans les outils Microsoft Office, j'ai ensuite mis un "Bouton" de "Formulaire" (Affiche la barre d'outil des formulaires) et je lui ai attribué la procédure que j'avais données...

Mais comme tu as l'aire paumé, et que moi je comprends pas pourquoi tu l'es ... (j'ai l'impression d'avoir été claire) : Que veux-tu et comment ?
Le plus détaillé possible avec ce que toi tu as essayé de faire ...

Merci d'avance

edit : C'est quoi que tu comprends pas dans :
  1. Do While Len(Range("B" & r).Formula) > 0


"Do While" = Faire ce qui suit tant que ...
"Len()" = Avoir la longueur de la chaine de caractères ( Len("toto") = 4 )
"> 0" = doit être supérieur à 0

Donc en traduction : "Tant que la cellule contient une formule ( donc qu'il y a des caractères dedans ) faire : ..."

Voila, je précise que c'est un exemple.

Tu devrais faire un petit tout sur Google avec "excel vba tutorial" ...
  • Commenter cette réponse |
Score
0
òh
òi
|
Re-Bonjour,


Donc si je comprends Bien Il va y avoir une partie de VBA sous Excel & Sous Access ? Et comment on relie les deux ?

Sinon le Range ici => Do While Len(Range("B" & r).Formula) > 0 => Pose tjr problèmes :/ 

Désolé juste que je suis un peu pommé ...
Merci d'avance :) 
  • Commenter cette réponse |
Score
0
òh
òi
|
Ah, en fait il s'agit d'un code à mettre directement dans le VBA du classeur Excel ("Outils/Macro/VisualBasic editor" ou "ALT-F11").

La propriété Range(zone) permet de sélectionner une zone. La variable zone est une chaine de caractère de la forme "B5:T20" (cellule du coin haut gauche et cellule bas droite séparée par le caractère ':'), il est possible de ne spécifier qu'une seule cellule (pas de ':') et de traiter ainsi son contenu.

Si tu souhaites extraire tout ça en dehors, dans un programme spécifique, il te faudra en plus ouvrir le document Excel :

Il faut définir les variables nécessaires :
  1. 'Déclaration des variables
  2. Dim appExcel As Excel.Application 'Application Excel
  3. Dim wbExcel As Excel.Workbook 'Classeur Excel
  4. Dim wsExcel As Excel.Worksheet 'Feuille Excel
  5.  
  6. 'Ouverture de l'application
  7. Set appExcel = CreateObject("Excel.Application")
  8. 'Ouverture d'un fichier Excel
  9. Set wbExcel = appExcel.Workbooks.Open("C:\MonFichierExcel.xls")
  10. 'wsExcel correspond à la première feuille du fichier
  11. Set wsExcel = wbExcel.Worksheets(1)


Ici on récupère dans wsExcel la première feuille du classeur, il existe différentes méthodes :
  1. 'Récupération de la feuille s'appellant maFeuille
  2. Set wsExcel = appExcel.ActiveWorkbook.Sheets("maFeuille")
  3.  
  4. 'Récupération de la deuxième feuille
  5. Set wsExcel = appExcel.ActiveWorkbook.Sheets(2)
  6.  
  7. 'Récupération de la feuille active
  8. Set wsExcel = appExcel.ActiveWorkbook.ActiveSheet


Ensuite il faut traiter les informations de la feuille :

Pour une cellule en particulier
  1. wsExcel.Cells(3,2).Value

Attention : la fonction "Cells" fonctionne avec le numéro de ligne et de colonne (A = 1, B = 2, ...)

Pour "coller" avec le code précédent :
  1. Do While Len( wsExcel.Cells( r , 1 ).Formula ) > 0
  2. ...
  3. ... = wsExcel.Cells(r, 1).Value
  4. ... = wsExcel.Cells(r, 2).Value
  5. ... = wsExcel.Cells(r, 3).Value
  6. ...
  7. Loop


soit "wsExcel.Cells(r, 3).Value" la valeur de la cellule en colonne 'C' à la ligne r.



Voila, avec ça devrait aller.
  • Commenter cette réponse |
Score
0
òh
òi
|
Bonjour,

Merci de ta réponse si rapide ?!

Juste un truc qui m'interpelle, nulle part ds ton code il y a la référence de du fichier Excel a moins que j'ai mal vu --' ! :??: 

Et aussi le Range correspond a quoi ? Et de plus lorsque l'on compile, Il y a une erreur dessus : "Sub ou Fonction non définie" Pk ?

Merci d'avance :) 
  • Commenter cette réponse |

Ce n'est pas ce que vous cherchiez ?

Tom's guide dans le monde
  • Allemagne
  • Italie
  • Irlande
  • Royaume Uni
  • Etats Unis
Suivre Tom's Guide
Inscrivez-vous à la Newsletter
  • ajouter à twitter
  • ajouter à facebook
  • ajouter un flux RSS