Discussion :

[Notsofar]

Bonjour à tous,

Je suis nouvelle sur ce forum, mais je m'inspire assez souvent de vos créations… Débutante en VBA, je viens tout juste de commencer à travailler avec ACCESS, alors la combinaison des deux me reste assez nébuleuse…

J'ai conçu une base access (regroupant requêtes actions, formulaire, intéraction avec EXCEL,…) destinée (entre autres) a incrémenter un fichier EXCEL déjà existant, nommé ESSAI.xls.
J'ai donc, d'un côté, une table " FcAS ". Cette table est composée des champs [CODE], [LIBELLE] & [MONTANT].
De l'autre côté, j'ai un fichier EXCEL avec une colonne dans laquelle je trouve des "codes" qui sont identiques à ceux de mon champs [CODE].
J'aimerais faire une recherche sur chaque "code" de mon fichier EXCEL afin d'aller chercher dans ma table " FcAS " le [MONTANT] correspondant et l'incrémenter dans la cellule située 3 colonnes plus loin dans mon fichier EXCEL.

Dans ma tête, cela donne un truc du genre :
Pour chaque "code" de mon fichier EXCEL,
je recherche le [CODE] correspondant dans ma table "FcAS"
Si je ne le trouve pas, je passe au [CODE] suivant jusqu'à que j'ai fait le tour de tous les [CODES] de ma table " FcAS"
Si je le trouve, je vais chercher le [MONTANT] de ma ligne de [CODE] et je le reporte dans mon fichier EXCEL, 3 colonnes plus loin que ma cellule active
Ensuite je passe au "code" suivant et je recommence depuis le début.

Concrêtement, voici ce que j'ai fait…

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Dim xlApp As Excel.Application
Dim xlSheet As Excel.Worksheet
Dim xlBook As Excel.Workbook
Dim i As Long
Dim rec1 As Recordset
Dim DerLigne As Long
 
'Lancement des deux requêtes actions
DoCmd.SetWarnings False
DoCmd.OpenQuery ("Détail")
DoCmd.OpenQuery ("Liste")
DoCmd.SetWarnings True
 
Set rec1 = CurrentDb.OpenRecordset("FcAS", dbOpenSnapshot)
 
'Initialisations
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(Filename:="P:\Mes documents\ESSAI.xls")
 
xlBook.Activate
 
DerLigne = Range("B65536").End(xlUp).Row
 
i = 6
For i = 6 To DerLigne
If Cells(i, 3) = rec1.Fields(0) Then
Cells(i, 3) = DLookup("AN", "FcAS", rec1.Fields(0) = Cells(i, 1))
rec1.MoveNext
End If
rec1.MoveNext
Next
 
'Fermeture & libération des objets
xlApp.DisplayAlerts = False
ActiveWorkbook.Close savechanges:=True
End With
xlApp.DisplayAlerts = True
xlApp.Quit
rec1.Close
Set rec1 = Nothing
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

1er problême : En utilisant F8, je me rends compte que le résultat de Rec1.field(0) est "3318" alors que le résultat de cells(i,1) est 3318. Il me faut 3318. Je pense que c'est parce que Rec1.field(0) est de type "texte" et non pas "numérique".
1ère question : quel code VBA pourrais-je écrire afin de changer cette propriété (je ne peux directement la changer dans la table puisque celle-ci est écrasée chaque fois que je lance la macro) ?

2ème problème : Il faut que je boucle sur tous les "codes" de mon fichier et qu'à chaque fois je boucle sur le champs [CODE] de ma table " FcAS" jusqu'à ce que je trouve le bon code… Ce que ma macro ne fait pas…
2ème question : Auriez-vous une idée de ce qui cloche dans ma procédure (surement plein de choses me direz-vous mais bon je suis preneuse de toute amélioration )

Je vous remercie par avance de votre aide !!!

[Robert1957]

Bonjour Notsofar,

J'ai donc, d'un côté, une table " FcAS ". Cette table est composée des champs [CODE], [LIBELLE] & [MONTANT].
De l'autre côté, j'ai un fichier EXCEL avec une colonne dans laquelle je trouve des "codes" qui sont identiques à ceux de mon champs [CODE].
J'aimerais faire une recherche sur chaque "code" de mon fichier EXCEL afin d'aller chercher dans ma table " FcAS " le [MONTANT] correspondant et l'incrémenter dans la cellule située 3 colonnes plus loin dans mon fichier EXCEL.

Pour répondre à cette question voici une fonction qui fait cela:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
Public Function fuExcel()
 
    Dim oApp As Excel.Application
    Dim oWkb As Excel.Workbook
    Dim oWSht As Excel.Worksheet
    Dim i As Integer
    Dim strFeuille As String, strChemin As String, strLink As String
    Dim doResult As Double
 
    strFeuille = "Feuil1"  'Inscrire le nom de la feuille de ton classeur
    strChemin = "P:\Mes documents\ESSAI.xls"  'Inscrire le chemin plus le nom du classeur
 
    Set oApp = CreateObject("excel.application")
    Set oWkb = oApp.Workbooks.Open(strChemin)
    Set oWSht = oWkb.Worksheets(strFeuille)
 
    i = 1 'Première ligne à mettre à jour, ici j'ai choisi la ligne # 1
    'On parcours le fichier Excel tant qu'il y a de l'information
    'Ici la colonne qui fait le lien donc code dans le fichier Excel
    While oWSht.Range("A" & i).Value <> ""
      'On récupère la valeur de code dans le fichier Excel
      strLink = oWSht.Cells(i, 1)
      'On récupère la valeur de Montant dans la table, si pas trouvé ici j'ai mis 0 à toi de voir ce que tu veux faire
      doResult = Nz(DLookup("Montant", "FcAS", "[Code]=" & Chr(34) & strLink & Chr(34)), 0)
      oWSht.Cells(i, 4) = doResult
      i = i + 1
    Wend
    'On enregistre les modifications
    oWkb.Save
    'On ferme
    oWkb.Close
    'On libère les objets
    Set oWSht = Nothing
    Set oWkb = Nothing
    Set oApp = Nothing
 
End Function

Pour ce qui est de ton code je ne suis pas certain de comprendre ce que tu veux faire....

Bonne journée

[MickaelKael]

Bonjour,

pour ta question 1, je ne suis pas sur d'avoir bien compris mais tu peux utiliser une fonction de conversion du type CInt ou CLng pour convertir ton texte en numérique. et si c'est le numérique que tu veux en texte c'est CStr.
Si ta colonne Excel est au format "standard", il risque de transformer ton "3318" en 3318 automatiquement et du coup il peut être utile de rajouter un apostrophe simple histoire d'être sûr qu'il reste en texte (Cells(i, 3) = "'" & DLookup...)

Pour la question 2,
Là en fait il y a un mélange de 2 manières de faire => parcourir les différents recordset de ta table / faire un DlookUp

si tu veux parcourir les enregistrement de ta table, il faut être sur que les identifiants sont les même et trié dans le même ordre. S'il y a un doute là dessus, à chaque fois que tu cherches une nouvelle occurrence de ton fichier excel, il faut repartir à la ligne 1 de ta table Access.
ca devrait ressembler à

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
For i = 6 To DerLigne 'pour chaque ligne de mon excel
  rec1.movefirst  'je commence par remettre à la ligne 1 ma table acces
  while not Cells(i, 3) = rec1.Fields(0) or rec1.EOF 'tant que je n'ai pas le code dans excel et access identique (ou que je suis arrivé au bout de ma table access)
    rec1.movenext 'je descends d'une ligne dans ma table access
  Wend
  Cells(i, 3) = DLookup("AN", "FcAS", rec1.Fields(0) = Cells(i, 1)) 'la théoriquement je n'ai pas besoin de faire un dlookup vu que je suis dans la ligne qui correspond à mon code, du coup je devrais directement pouvoir appeler le champs que je veux => cells(i,3)=rec1.fields(x)
'si le code dans la table excel n'a pas été trouvé dans la base Access, tu vas avoir une erreur
Next

Vu que le DlookUp va chercher dans une table en fonction de critères, tu pouvais simplement mettre

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
For i = 6 To DerLigne 'pour chaque ligne de mon excel
  Cells(i, 3) = DLookup("AN", "FcAS", rec1.Fields(0) = Cells(i, 1))
Next

Le Code de Robert est probablement plus propre et évite les erreurs, mais j'ai essayé de t'éclaircir sur celui que tu avais commencé et sur les différentes boucles possibles (avec mon modeste niveau de connaissance...).

Cordialement.

[Notsofar]

Vraiment Merci à vous deux pour vos retours !

Pour l'instant j'essaye de reprendre le code de Robert1957... J'ai un bug sur la ligne suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

doResult = Nz(DLookup("Montant", "Fonds cotés AS", "rec1.Fields(0) =" & Chr(34) & strLink & Chr(34)), 0)

avec le message Erreur de synthaxe (opératur absent) dans l'expression '="3318"
Quand je relis l'expression (et si je la comprends bien), j'ai l'impression qu'il manque quelque chose devant le "="... J'ai essayé avec mon champs [CODE], rec1.Fields(0) mais ça ne marche pas

J'ai une question de compréhension (si vous avez le temps d'y répondre) :
- Pourquoi mettre la fonction NZ et les Chr(34) ?

Merci en tous cas... Je continuerais de creuser demain et je vous reviens pour vous dire ce qu'il en est !

Bonne soirée !

[Robert1957]

Bonjour Notsofar,

Je crois que tu mélanges un peu les choses.

La fonction n'a pas besoin de record de ta table. Ce que cette fonction fait, elle parcours les lignes de ton fichier Excel tant qu'il y a des données dans cette colonne.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

While oWSht.Range("A" & i).Value <> ""

Cette partie du code parcours la colonne A du fichier Excel, à adapter selon tes besoins.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

doResult = Nz(DLookup("Montant", "FcAS", "[LeNomDeTonChampCode]=" & Chr(34) & strLink & Chr(34)), 0)

Cette partie du code récupère la valeur du champ "Montant" de ta table "FcAS" ou le code est égale au code de ton fichier Excel.

Cette ligne est une fonction imbriquée dans une autre fonction qui retourne la valeur du champ Montant ou 0.

1. Première partie: DLookup est une fonction à 3 arguments. Le premier est le champ que l'on veut récupérer, le second est le domaine (table ici) dans lequel est le champ et finalement le troisième est le critère qui devrait se lire tel qu'écrit dans mon autre post. Donc si dans ta table FcAS, de ce que j'ai compris il y a un champ qui a pour nom "Code" tu dois donc récupérer le champ Montant pour lequel le champ Code est = au code de ton fichier Excel.
2. Deuxième partie: Nz est une fonction à 2 arguments. Le premier la valeur a tester et le second la valeur à retourner si nul. Si la fonction DLookup ne trouve pas d'équivalence qu'elle est la valeur que tu veux retourner sachant qu'une valeur nulle va faire planter le code. C'est ce que fait la fonction Nz. Tu peux choisir ce que tu veux comme retour mais dans les limites de la déclaration de la variable. Comme j'ai mis un "Double" comme variable ça nous prend du numérique. Tu peux ajuster comme tu veux.

En aucun temps on a besoin de rec1.Fields(0)

Bonne journée

[MickaelKael]

Chr(34) c'est pour mettre un apostrophe sans que cela soit interprété par vba comme un apostrophe lorsqu'il va lire la fonction (sinon il va avoir 2 apostrophes de suite et il l'interprète de travers.

En gros c'est pour qu'il considère la valeur de ta cellule excel (le "strLink") comme un texte vu qu'on met entre guillemets avec les chr(34). Les vrai guillemet présent avant son propre à la fonction DlookUp pour laquelle il faut fournir la condition (le 3ème paramètre) entre guillemets pour ne pas qu'ils les considère comme une variable.

[Notsofar]

Bonjour MickaelKael & Robert1957,

Un grand Merci à tous les deux... Après quelques essais infructueux dûs à mon manque d'expérience, ça marche enfin (je me suis concentrée sur ta procédure Robert1957) !!!
Grâce à vos explications, je comprends mieux maintenant comment marche cette fonction DLOOKUP et l'ajout du NZ est top parce que, justement, je me demandais comment faire si elle ne me renvoyait rien !!!

J'hésitais à poster... Ca fait un an que je galère sur différentes macros en visionnant les forums mais j'ai toujours eu peur de passer pour une "bleue"... Ce que je suis...
En tous cas c'est très intéressant de "tomber" sur des gens qui peuvent enfin m'expliquer les subtilités de ce langage... Apprendre par soi-même à ses limites !!!

Encore Merci & Bonne journée !