While depuis un classeur fermé

Bonsoir à tous,

Je travaille actuellement sur un exercice VBA orienté sur un thème facturation.

Dans la procédure ci-jointe j'ai inscrit un code pour vérifier que ne soit pas saisie dans la facture (du classeur ou se trouve saisie la procédure)

parcourir classeur ferme autre dossier

une quantité plus élevée que celle dispo en stock.

Au début je le faisais avec un classeur "catalogue.xlsx" partageant le même dossier que le classeur ou se trouve la feuille facturation.

J'ai souhaité faire la vérfication, mais cette fois en ayant le classeur "catalogue.xlsx" dans un autre répertoire. Et la seule méthode que j'ai pu trouver pour l'instant c'est d'ouvrir ce classeur via la méthode Open.

Y a t-il une solution pour explorer/parcourir le contenu du classeur "catalogue.xlsx", se trouvant dans un autre dossier du disque dur, autre que celle-ci ?

L'idée serait d'effectuer une comparaison avec les données de celui-ci pendant qu'il est fermé.

Merci beaucoup,

bonsoir,

Y a t-il une solution pour explorer/parcourir le contenu du classeur "catalogue.xlsx", se trouvant dans un autre dossier du disque dur, autre que celle-ci ?

L'idée serait d'effectuer une comparaison avec les données de celui-ci pendant qu'il est fermé.

Dans tous les cas pour lire les données qui se trouvent dans un fichier, il faut ouvrir le fichier (que ce soit visible pour toi ou non). Une alternative à Workbooks.open est l'utilisation de l'objet AdoDB, l'accès à l'information se fait alors via une commande SQL. Le classeur ne sera pas ouvert en tant que fichier Excel, mais en tant que fichier dont les accès seront gérés par AdoDB de manière invisible pour toi, ce que l'on peut assimiler à "en gardant le fichier fermé".

Bonjour h2so4

Merci pour ta réponse. Ok je vois :) Est ce que tu pourrais m'indiquer la marche à suivre ou bien y t-il un tuto que tu puisses me recommander ?

Merci à toi !

Bonjour,

voici un exemple :

Public Sub cherchevaleurdanscatalogue()
    Excel_DB = "d:\downloads\catalogue.xlsx"    'Identification du classeur
    Set connexion = CreateObject("ADODB.Connection")

    '--- Connexion ---
    With connexion
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Excel_DB & ";Extended Properties=""Excel 12.0;HDR=YES;"""
        .Open
    End With

    reference = "article 3"
    '----- Requete -----
' valeur est le libellé de la colonne qui contient les valeurs de stock, article le libellé de la colonne qui contient la référence de l'article,
' articles est le nom de la feuille qui contient les données, reference est la variable vba qui contient la référence de l'article recherché

    Texte_SQL = "SELECT [valeur]  FROM [articles$] where [article] = '" & reference & "'"
    Set Requete = connexion.Execute(Texte_SQL)

    With Requete
        If Not .EOF Then ' si trouvé
            valeurstock = Requete.Fields(0).Value
        End If
    End With
    MsgBox "stock pour l'article (" & reference & ") : " & valeurstock
'----- Clôture -----
    connexion.Close
    Set Requete = Nothing
    Set connexion = Nothing
End Sub

Bonjour h2so4

Merci pour ton exemple. Désolé pour le temps de réponse j'ai été en déplacement pro ces derniers temps ..

Je suis en train de le potasser depuis un moment et j'aurais besoin de quelques éclaircissements :

- la partie "valeurstock = Requete.Fields(0).Value" ->le Fields(0) correspond à un champs j'imagine; est ce donc que "Requete" renvoie une table ? (une table à colonne unique pour le coup..)

- la partie Texte_SQL = "SELECT [valeur] FROM [articles$] where [article] = '" & reference & "'" . Je vois que tu as ajouté des apostrophes autour des guillemets de reference. A quoi ce la sert-il ?

- la partie .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Excel_DB & ";Extended Properties=""Excel 12.0;HDR=YES;"""

Saurais- tu m'expliquer ce que signifie cette partie stp ?? Data Source ça je comprends ^^ mais Provider et Extended Properties j'aurais besoin de quelques éclaircissements ^^ ( que signifie HDR ? pourquoi autant de guillemets à ""Excel 12.0;HDR=YES;""" ?)

-ADODB est ce que ça signifie ActiveX Data Object Data Base ?

Bon bien sûr je suis aussi en train de faire mes recherches de mon coté ^^

En te remerciant :)

bonjour,

je ne sais pas où tu en es dans tes recherches, mais voici quelques réponses que tu peux trouver sur le net

la partie "valeurstock = Requete.Fields(0).Value" ->le Fields(0) correspond à un champs j'imagine; est ce donc que "Requete" renvoie une table ? (une table à colonne unique pour le coup..)

requete est un objet qui contient le résultat de la requete SQL. Fields en est une collection d'objet champs. Valeur unique : cela dépend du résultat du SQL.

la partie Texte_SQL = "SELECT [valeur] FROM [articles$] where [article] = '" & reference & "'" . Je vois que tu as ajouté des apostrophes autour des guillemets de reference. A quoi ce la sert-il ?

si réference contient "art001", et que c'est la référence de l'article pour lequel je recherche la valeur, la syntaxe SQL doit être

SELECT [valeur] FROM [articles$] WHERE [article] = 'art001'

ADO va sélectionner toutes les lignes qui contiennent art001 dans la colonne article et renvoyer la liste des valeurs correspondantes, s'il n'y a qu'une seule ligne, il n'y aura qu'une seule valeur.

la partie .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Excel_DB & ";Extended Properties=""Excel 12.0;HDR=YES;"""

ce sont les paramètres qui permettent d'accéder via ADO (et SQL) à un classeur excel(provider et extended properties). HDR=YES signifie qu'il y une ligne d'entête qu'il faut utiliser comme identifiant de colonne. (valeur et article sont des identifiants de colonne).

le doublement du guillemet est nécessaire si l'on veut que le guillemet fasse partie de la chaîne de caractères. =syntaxe VBA

exemple : pour mettre le texte "ceci <"> est un guillemet" guillemets d'ouverture et de fermeture compris

texte = """ ceci <""> est un guillemet"""

ADODB est ce que ça signifie ActiveX Data Object Data Base ?

dans un contexte Microsoft oui, mais peu importe le nom, l'idée est d'avoir un ensemble d'instructions qui permet de donner un accès SQL à différents types d'organisation de données (fichier txt, csv, excel, access,DB SQL, mail, ...)

Bonsoir h2so4

Je te remercie c'est plus clair sur plusieurs points, mais il reste des zones d'ombre ..

Par rapport a cette instruction -> Requete.Fields(0).Value , Je ne comprends pas pourquoi on demande le champs numéro 0 .. Et surtout pourquoi appeler un champs ? Est ce parce que le résultat d'une requete forme forcément une table ?

En gros, ici, si il y avait plusieurs références concordantes la requete SQL aurait créé une table avec une liste de toutes les valeurs correspondantes?

Une autre question que je me pose, et pas des moindres, c'est comment utiliser cette méthode pour, dans mon code, effectuer une comparaison entre les valeurs dans la feuille "facturation" et celles de la feuille "articles" de "catalogue.xlsx" ?

Est ce que je dois lancer le For Each depuis la feuille facturation pour définir une variable nommée "référence" qui contiendra le nom de l'article dont il fuat trouver la valeur en stock dans catalogue? Pour ensuite lancer la requete SQL et si celle-ci trouve bien une correspondance rapporter cette valeur. Et ensuite si cette valeur se trouve être inférieur à celle inscrite dans facturation renvoyer un "test = true" la condition If ?

Je me questionne aussi sur la mise a jour des stock dans le cas ou la valeur trouvé dans catalogue serait inférieure à celle demandée.

Car ici on voit comment consulter, mais cette connexion permet-elle de modifier ??

Est ce que par hasard tu aurais pu me montrer u

7code.txt (3.14 Ko)

n exemple a partir de mon code si cela ne t'est pas trop contraignant? (je le joins dans un fichier texte car le fichier excel est trop lourd..)

Car là je navigue vraiment en terre inconnue ... :/

Merci à toi !

Bonjour,

Je te remercie pour le lien. Je vais le consulter mais je viens déjà d'y passer un petit moment et je vois qu'il y a des concepts encore différents et qui me sont étrangers.

J'ai essayé de comprendre le fields(0) pour la récupération des données de la requête sur le net mais je n'ai pas trouvé d'explication concrète; et cette méthode n'est pas présente dans le lien.

Je pense que je vais rester sur l'exemple que tu m'a donné et essayer de trouver une solution pour bien le comprendre et trouver comment l'intégrer à mon exemple déjà pour lire et pour écrire après si j'y parviens. Mais ça me paraît compliqué avec mon niveau actuel.

Merci à toi,

re-bonjour,

J'ai essayé de comprendre le fields(0) pour la récupération des données de la requête sur le net mais je n'ai pas trouvé d'explication concrète; et cette méthode n'est pas présente dans le lien.

un SELECT SQL renvoie les données demandées sous la forme d'un tableau de données. Comme ta demande consistait à simuler la fonction RECHERCHEV, je t'ai proposé une requête SQL qui renvoie un seul élément. Fields(0) renvoie à la première colonne du tableau (où .value contient la valeur de la première ligne de ce tableau). Si tu as un SQL qui est susceptible de donner plusieurs lignes de réponse, tu peux charger une table vba avec l'instruction .getrows

voici une exemple qui te montre comment lire et exploiter les réponses de plusieurs lignes et te montre comment faire une mise à jour.

Sub aarght_testado2()
    Excel_DB = "dbtestado.xlsx"    'Identification du classeur adapter le chemin
    Set connexion = CreateObject("ADODB.Connection")

    With connexion
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Excel_DB & ";Extended Properties=""Excel 12.0;HDR=YES;"""
        .Open
    End With

    'ref = InputBox("refeference") 'critere de recherche
    texte_sql = "SELECT [article],[quantite] FROM [inventaire$] " 'sélectionner toutes les colonnes article et valeur de l'inventaire
    Set requete = connexion.Execute(texte_sql)

    If Not requete.EOF Then

        For i = 0 To requete.Fields.Count - 1 'les noms de colonnes (=entêtes)
            ligne = ligne & requete.Fields(i).Name & vbTab
        Next i

        reponse = requete.getrows 'mettre la réponse SQL dans une table
        For i = LBound(reponse, 2) To UBound(reponse, 2) 'afficher le contenu de la tablea
            ligne = ligne & vbCrLf
            For j = LBound(reponse) To UBound(reponse)
                ligne = ligne & reponse(j, i) & vbTab
            Next j
        Next i
        MsgBox ligne

        'mettre à jour une cellule du tableau via SQL, ici on met à jour la quantite qui correspond à l'article ayant la référence c
        qu = 10
        ref = "c"
        texte_sql = "update [inventaire$] set [quantite]=" & qu & " where [article]='" & ref & "'"
        Set requete = connexion.Execute(texte_sql)
    Else
        MsgBox "référence " & ref & " non trouvée"
    End If
    connexion.Close
End Sub

fichier de données exemple

12dbtestado.xlsx (8.60 Ko)

Bonsoir h2so4

Merci une fois de plus pour ton aide. J'ai bien continuer à étudier cela. La compréhension progresse mais ya encore du boulot!..

Là je me demandais pourquoi dans les Lbound et Ubound du compteur i il y avait "2" dans l'argument dimension ? est-ce que i correspond au compteur de la dimension des lignes quand j est celui de la dimension des colonnes ?

Du coup je regardais l'indice de j dans l'outils de débug de VBA varier de 0 à 2... donc ça fait trois rangs du coup ... :/

Et cela fait pareil pour i, de 0 à 5.

Le .getrows ne charge bien qu'uniquement la tableau sans les libellés de colonne ? je peine a comprendre ce mouvement ...

bonsoir,

Là je me demandais pourquoi dans les Lbound et Ubound du compteur i il y avait "2" dans l'argument dimension ? est-ce que i correspond au compteur de la dimension des lignes quand j est celui de la dimension des colonnes ?

oui

Du coup je regardais l'indice de j dans l'outils de débug de VBA varier de 0 à 2... donc ça fait trois rangs du coup ... :/

j prend la valeur 2 à la fin de la boucle, ce qui compte ce sont les valeurs de lbound() qui vaut 0 et ubound() qui vaut 1. la boucle varie bien de 0 à 1 l'instruction next fait l'incrémentation du compteur. Si le compteur dépasse la valeur finale, le programme continue après l'instruction next et j a alors la valeur 2. (même raisonnement pour i lbound() 0 ubound() 4 au sortir de la boucle i vaut 5.

Le .getrows ne charge bien qu'uniquement la tableau sans les libellés de colonne ? je peine a comprendre ce mouvement ...

.getrows renvoie le résultat sans les entêtes de colonnes SQL, avec HDR=no, ADO prend comme entêtes de colonne F1,F2,F3,.., Fn qu'il faut alors utiliser dans la commande SQL. Avec HDR=yes, ADO prend comme entêtes de colonne les valeurs trouvées dans la première ligne, il faut alors utiliser ces valeurs dans la commande SQL.

ex HDR=no sur base du classeur excel dbtestado.xlsx

SELECT [F1],[F2] from [inventaire$]

renvoie le tableau suivant avec .getrows (article et quantite sont considérés comme des données)

articlequantite
a1
b2
c3
d4
e5

SELECT [F2] from [inventaire$] where [F1]='c'

renvoie le tableau suivant avec .getrows

3

ex HDR=yes

SELECT [article],[quantite] from [inventaire$]

renvoie le tableau suivant avec .getrows

a1
b2
c3
d4
e5

SELECT [quantite] from [inventaire$] where [article] = 'c'

renvoie le tableau suivant avec .getrows

3

Bonjour,

Merci pour tes explications, je comprends mieux !

Petite question lorsque tu parles du HDR mis sur "No" tu cite comme valeurs d'en-têtes "F1, F2..."; est-ce que c'est pour donner un nom générique ?

avec HDR=no, ADO prend comme entêtes de colonne F1,F2,F3,.., Fn

bonjour,

Petite question lorsque tu parles du HDR mis sur "No" tu cite comme valeurs d'en-têtes "F1, F2..."; est-ce que c'est pour donner un nom générique ?

le language SQL nécessite des noms de colonnes pour pouvoir fonctionner. Dans une base données SQL, on donne un nom aux colonnes au moment de la création des tables. Dans excel, les colonnes ont comme nom une ou plusieurs lettres de l'alphabet, mais ADO ne les utilise pas. Quand on veut accéder un classeur Excel via SQL (et ADO),avec HDR=no, le nom des colonnes est F1 pour la première colonne (F comme Field, je suppose), F2 pour la 2ème, etc .... Fx est bien l'identifiant qu'il faut utiliser dans la commande SQL ADO, dans le cas ou HDR=no.

Bonsoir h2so4,

Petit à petit les choses s'éclaircissent et grâce à tes explications c'est beaucoup plus clair ! Merci :)

J'ai remarqué une chose que je connaissais pas du tout avec ça

ligne = ligne & reponse(j, i) & vbTab

Il est possible de cibler une partie d'une variable en déterminant une adresse dans celle-ci par le biais de "()" qui lui sont accolées et, cet adressage fonctionne à l'identique d'un Range et nom d'un Cells, l'indice de colonne est nommé en premier suivi de celui de la ligne.

Bon j'imagine que cette méthode renvoie quelque chose uniquement lorsque la variable qui en fait l'objet se trouve contenir une table ?! ^^ oO

bonjour,

Il est possible de cibler une partie d'une variable en déterminant une adresse dans celle-ci par le biais de "()" qui lui sont accolées et, cet adressage fonctionne à l'identique d'un Range et nom d'un Cells, l'indice de colonne est nommé en premier suivi de celui de la ligne.

il s'agit simplement d'une variable de type array (tableau), dont on adresse les éléments par une valeur d'indice pour chaque dimension. au départ sans autre précision, toute variable est de type variant (peut être de n'importe quel type) et c'est au moment de son utilisation que VBA lui attribue un type. la méthode .getrows renvoie un tableau et VBA attribue un type array à la variable qui reçoit la réponse. variable dont tu peux accéder les éléments via des indices.

bonjour h2so4,

je suis en train de tranquillement appliquer la méthode pour mon exo de facturation.

Je rencontre quelque chose de curieux :

Comme tu peux le voir sur les captures jointes ma feuille facturation comporte des lignes vides et pour charger les valeurs des références dans la variable reference j'utilise une boucle For

Donc pour la cellule C6 j'obtiens comme valeur stock celle qui correspond à ce qui est dans le catalogue.xlsx mais, pour les cellules C7, C8, C9, j'obtiens ce que l'on peut voir la capture la même valeur que pour C6.

Le cas se reproduit évidemment à l'identique après C10, les cellules de la colonne "Code article" qui suivent affichent le résultat de l'article b008

Comment expliquer cela puisque l'article " " n'existe pas dans mon catalogue... ?

article nul

je crois que tu surestimes fortement mes capacités divinatoires et de lecture de pensée.

Bonsoir,

Ouais c'était pas très clair... En fait je trouvais curieux d'avoir comme résultat de valeur_stock, pour un code article vide, le meme que le dernier code article valide.

Et en fait j'ai tout simplement oublié de réinitialiser la variable valeur_stock au début de la boucle à 0.....

Bonsoir h2so4,

Merci encore pour ton aide,

Je continue à m'entraîner sur cet exercice et j'ai ajouté quelques autres pour tester d'autres conseils reçu dernièrement.

J'étais en train de buter sur la syntaxe de ConnexionString et je me suis dit que ça serait plus évident de retenir si je comprenais à quoi ça fait référence.. oO

Est ce que tu saurais me dire ce que signifie

ACE.OLEDB

12.0, c'est une version d'Excel?

Merci à toi !

Sub connexion_ext()
    Dim lignef As Integer: lignef = 6
    Dim ref_d As String: Dim val_d As Integer
    Dim test As Boolean

    Excel_DB = "D:\Dropbox\YGGD\13. MEDIATHEQUE\FORMATION\VBA\BON BACHE - VBA\LIVRE - Apprendre VBA de façon ludique par la pratique\Sessions\08 facturation client automatisée - 2021 0301\Ressouces\catalogue.xlsx"    'Identification du classeur adapter le chemin
    Set connexion = CreateObject("ADODB.Connection")
    With connexion
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Excel_DB & ";Extended Properties=""Excel 12.0;HDR=YES;"""
        .Open
    End With
    Do While lignef <= 26
        If lignef = 26 And ThisWorkbook.Worksheets("facturation").Cells(lignef, 3).Value = "" And ref_d = "" Then
            MsgBox "la facture ne comporte aucune saisie"
            Exit Do
        Else
            If ThisWorkbook.Worksheets("facturation").Cells(lignef, 3).Value = "" Then
                lignef = lignef + 1
            Else
                ref_d = ThisWorkbook.Worksheets("facturation").Cells(lignef, 3).Value
                val_d = ThisWorkbook.Worksheets("facturation").Cells(lignef, 5).Value
                'MsgBox ref_d & vbCrLf & val_d
                ref_d_list = ref_d_list & ref_d & ";"
                'MsgBox ref_d_list

                texte_sql = "SELECT [Stock] FROM [articles$] WHERE [Code article] = '" & ref_d & "'"
                Set Requete = connexion.Execute(texte_sql)

                With Requete
                    If .EOF Then
                        MsgBox "la référence " & ref_d & " n'existe pas. Corrigez la saisie"
                        Exit Sub
                    Else
                        val_stk = Requete.Fields(0).Value
                    End If
                End With
                If val_d > val_stk Then
                    ref_hs = ref_hs & ThisWorkbook.Worksheets("facturation").Cells(lignef, 3).Value & ", "
                    test1 = True
                End If
                'MsgBox val_stk
                lignef = lignef + 1
            End If
        End If
    Loop
    If test1 = True Then
        MsgBox "les references suivantes sont h_stk :" & vbCrLf & ref_hs
    Else
        reponse = MsgBox("la facture est ok. Souhaitez-vous l'éditer ?", vbYesNo + vbQuestion)
        If reponse = 6 Then
            tab_ref = Split(ref_d_list, ";")
            For i = LBound(tab_ref) To UBound(tab_ref) - 1
                ref_current = tab_ref(i)
                ligne = 6
                While ligne <= 26
                    If ThisWorkbook.Worksheets("facturation").Cells(ligne, 3).Value = ref_current Then
                        val_d = ThisWorkbook.Worksheets("facturation").Cells(ligne, 5).Value
                    End If
                    ligne = ligne + 1
                Wend
                MsgBox tab_ref(i)
                MsgBox val_d
                'Exit Sub
                texte_sql = "SELECT [Stock] FROM [articles$] WHERE [Code article] = '" & ref_current & "'"
                Set Requete = connexion.Execute(texte_sql)
                With Requete
                    If Not .EOF Then
                        val_stk = Requete.Fields(0).Value
                        MsgBox val_stk
                    End If
                End With
                texte_sql = "update [articles$] set [Stock]=" & val_stk - val_d & "where [Code article]='" & ref_current & "'"
                Set Requete = connexion.Execute(texte_sql)
            Next i

        End If
    End If
End Sub
Rechercher des sujets similaires à "while classeur ferme"