Liste déroulante de classeur fermés sans passer par ADO

erics83 · 9 Septembre 2015

Bonjour,

J'ai beaucoup été aidé par BOISGONTIER (merci ! trop super !!!!) dans la lecture et l'écriture dans des classeurs fermés. Mais J'ai un problème : les serveurs de mon boulot ne permettent pas d'utiliser la méthode ADO.....😡

50 utilisateurs utiliseront le fichier, donc je ne peux pas faire une ouverture de classeur, pour lire le contenu....😡

Ce que je cherche à faire : admettons que j'ai une liste de référence dans un classeur fermé, nommé "BD Produits". Qu'en colonne A, j'ai une liste de référence de produits. En colonne B, les fournisseurs.

Je cherche à mettre dans un Userform (de mon classeur "recherche.xls)", je sélectionne une référence (listbox1), et dans la listbox2, s'affiche tous les fournisseurs de ce produit... = lecture des fournisseurs dans le classeur fermé...

J'ai essayé avec equiv/index, mais je n'arrive pas à avoir la liste...equiv/index étant à ;0, la formule s'arrête au premier fournisseur, sans me donner la liste...

Une petite aide ?

En vous remerciant,

job75 · 9 Septembre 2015

Re : Liste déroulante de classeur fermés sans passer par ADO

Bonsoir erics83,

Il faudrait déposer les 2 fichiers...

Mais pour commencer il faut reconstituer dans le classeur "recherche.xls" la liste du classeur "BD Produits.xls".

Pour cela ouvrez les 2 classeurs puis :

- nommez A la liste du classeur "BD Produits.xls"

- dans "recherche.xls" sélectionnez une plage de même dimension et dans la barre de formule entrez :

Code:

='BD Produits.xls'!A

- validez matriciellement cette formule par Ctrl+Maj+Entrée

- nommez B cette nouvelle liste

- vous pouvez fermer "BD Produits.xls".

Maintenant avec votre UserForm c'est facile, nombreux exemples de ListBox sur le forum.

A+

erics83 · 9 Septembre 2015

Re : Liste déroulante de classeur fermés sans passer par ADO

Merci job75,

J'ai peut-être du faire une fausse manip, mais cela ne fonctionne pas....et/ou n'ai pas compris....

merci pour votre aide,

A+

job75 · 9 Septembre 2015

Re : Liste déroulante de classeur fermés sans passer par ADO

Re,

Si vous ne suivez pas strictement ce qu'on vous dit de faire vous n'irez pas loin 🙄

Voyez les fichiers joints.

Le fichier "Recherche" étant destiné à recevoir des macros je l'ai enregistré en .xlsm.

Nota : pour les 2 plages nommées A et B ont peut inclure des lignes vides en cas d'ajout de textes dans le fichier source.

A+

erics83 · 9 Septembre 2015

Re : Liste déroulante de classeur fermés sans passer par ADO

Merci job75,

Mais j'ai un problème : j'ai copié/collé vos fichiers dans un dossier, puis ouvert "recherche" (j'ai essayé sous Excel2003 et Excel2011 Mac), et lorsque j'ouvre "recherche", j'ai la même liste que "BD produits"....

Je souhaitais, dans le classeur "Recherche", si je sélectionne E1, le nom de la référence, que j'ai "Fournisseur1","Fournisseur8", "Fournisseur15" qui s'affichent en F1, F2, F3 ou plus s'il y a plus de fournisseurs....(Pour les références, je peux les figer : ce sera toujours A1, A2, A3, etc..A7, mais il faut que les fournisseurs soient "cherchés" dans BD Produits".... )

J'ai dû mal m'exprimer, excusez moi....

En vous remerciant pour votre aide,

Merci

job75 · 10 Septembre 2015

Re : Liste déroulante de classeur fermés sans passer par ADO

Re,

Avant d'aller dormir j'ai ajouté l'USF avec ce code :

Code:

Private Sub Combobox1_Change()
Dim i&, h&
ListBox1.Clear
If ComboBox1.ListIndex = -1 Then Exit Sub
i = Application.Match(ComboBox1, [B].Offset(, 3).Columns(1), 0)
h = Application.CountIf([B].Columns(1), ComboBox1)
ListBox1.List = [B].Offset(i - 1, 4).Resize(h).Value
End Sub

Private Sub UserForm_Initialize()
Dim t, d As Object, i&
With [B].Offset(, 3)
  .Value = [B].Value
  .Sort .Columns(1), xlAscending, Header:=xlNo 'tri
  t = .Value 'matrice, plus rapide
End With
Set d = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(t)
  If t(i, 1) <> 0 Then d(t(i, 1)) = ""
Next
If d.Count Then ComboBox1.List = d.keys
End Sub

C'est bien sûr une ComboBox qui est utilisée pour la recherche du produit.

Edit : pour mieux tester j'ai ajouté en liste A un produit n'ayant qu'un fournisseur ainsi que 2 lignes vides.

Fichiers joints.

Bonne nuit.

BOISGONTIER · 10 Septembre 2015

Re : Liste déroulante de classeur fermés sans passer par ADO

Bonjour,

Crée une liaison avec le fichier source( crée une formule matricielle)
Sur l'exemple, on suppose que le fichier source est dans le même répertoire que le programme.

Code:

Sub LiaisonFichier()
     Chemin = ThisWorkbook.Path     ' Adapter  (chemin="c:\mesfichiers" par exemple)
     Fichier = "Article4.xls"       ' Adapter
     onglet = "Feuil1"              ' Adapter
     ChampAlire = "A1:E100"         ' Adapter
     ChampOuCopier = "A1:E100"      ' Adapter
     LitChamp ChampOuCopier, Chemin, Fichier, onglet, ChampAlire
End Sub

Sub LitChamp(ChampOuCopier, Chemin, Fichier, onglet, ChampAlire)
  Range(ChampOuCopier).FormulaArray = "='" & Chemin & "\[" & Fichier & "]" & onglet & "'!" & ChampAlire
End Sub

JB

erics83 · 10 Septembre 2015

Re : Liste déroulante de classeur fermés sans passer par ADO

Super merci job75,

cela fonctionne parfaitement, et je pense avoir compris la logique de votre code, même si pour l'instant, je suis incapable d'écrire aussi finement...Bravo.

Par contre, j'ai fait 2-3 petits essais et je bloque ... : j'ai rajouté des valeurs dans BD produits, modifié la plage A. Idem dans "recherche", j'ai modifié la plage B.

Mais cela ne prend pas les nouvelles valeurs et je ne comprends pas pourquoi....? cela s'arrête à la ligne 24, donc je me suis dit que c'était les déclarations de plages modifiées qui posaient problème, mais je ne vois pas où je fais l'erreur....

merci pour votre aide,

erics83 · 10 Septembre 2015

Re : Liste déroulante de classeur fermés sans passer par ADO

JB,

Les messages se sont croisés...

Extraordinaire !!!!

vous êtes arrivé à refaire tous les essais que nous avions vu ensemble, sans passer par ADO....et en utilisant le code de LitChamp....Extraordinaire !!! Merci,

Pensez-vous que pour la saisie intuitive dont nous avons déjà échangé, on puisse faire pareil ?

De même, vous utilisez "ChampOuCopier", cela veut-il dire que l'on pourrait modifier le commentaire ?

Par contre, j'ai toujours le souci de mon fichier "Article4" qui est écrasé toutes les semaines avec de nouvelles données, et donc la plage "BD" sera écrasée...y aurait-il une autre solution ? : sans utiliser "BD" ?

En vous remerciant pour votre aide,

Merci,

job75 · 10 Septembre 2015

Re : Liste déroulante de classeur fermés sans passer par ADO

Re, salut JB,

erics83 à dit:
Mais cela ne prend pas les nouvelles valeurs et je ne comprends pas pourquoi....? cela s'arrête à la ligne 24, donc je me suis dit que c'était les déclarations de plages modifiées qui posaient problème, mais je ne vois pas où je fais l'erreur....

De nouveau vous n'avez pas suivi les instructions de mon post #2.

La plage validée matriciellement est la plage A2:B24 alors qu'il faut valider matriciellement la plage A2:B100.

A+

BOISGONTIER · 10 Septembre 2015

Re : Liste déroulante de classeur fermés sans passer par ADO

Il n'y a pas de champ nommé BD

Code:

Sub auto_open()
     Chemin = ThisWorkbook.Path      ' Adapter  (chemin="c:\mesfichiers" par exemple)
     Fichier = "Article4.xls"                 ' Adapter
     onglet = "Feuil1"                        ' Adapter
     ChampAlire = "A1:E100"             ' Adapter
     ChampOuCopier = "A1:E100"      ' Adapter
     LitChamp ChampOuCopier, Chemin, Fichier, onglet, ChampAlire
End Sub

Sub LitChamp(ChampOuCopier, Chemin, Fichier, onglet, ChampAlire)
  Range(ChampOuCopier).FormulaArray = "='" & Chemin & "\[" & Fichier & "]" & onglet & "'!" & ChampAlire
  'Range(ChampOuCopier) = Range(ChampOuCopier).Value    ' Supprime liaison
End Sub

JB

erics83 · 10 Septembre 2015

Re : Liste déroulante de classeur fermés sans passer par ADO

Super merci JB,

J'ai testé les 2 fichiers. Si j'ai bien compris, vous avez mis : ADOinterro4Liaison pour décomposer le code et me montrer le fonctionnement, et ADOinterro4intuitif pour me monter le fonctionnement global....MERCI

J'ai essayé de modifier le code, pour que l'on puisse avoir une recherche sur 2 colonnes (comme dans mon précédent post avec ADO), j'ai donc fait :

Code:

For Each c In f.Range("A2:B" & f.[b65000].End(xlUp).Row)

cela fonctionne, par contre cela ne rapatrie pas les données ....

De même, je n'arrive pas à modifier le commentaire....je saisi un nouveau commentaire, mais il ne modifie pas dans "Article4"....

En vous remerciant pour votre aide,

BOISGONTIER · 10 Septembre 2015

Re : Liste déroulante de classeur fermés sans passer par ADO

- intuitif sur 2 colonnes :Voir PJ
- sans Ado, on ne peut faire que de la consultation.

JB

erics83 · 10 Septembre 2015

Re : Liste déroulante de classeur fermés sans passer par ADO

Génial !!!

Merci JB, je vois où j'ai fait mon erreur....Merci, c'est super et résout mon problème.

On arrive au même résultat sans passer par ADO....

Et concernant la modification/mise à jour du commentaire, c'est impossible ?

En vous remerciant,

merci pour votre aide, vous trouvez toujours les choses qui me semblaient impossibles et inenvisageables....Merci,

erics83 · 10 Septembre 2015

Re : Liste déroulante de classeur fermés sans passer par ADO

JB,

Comme vous l'aviez remarqué dans mes précédents post, mon fichier "Article4" à 10000 lignes et va jusqu'à la colonne X.

J'ai adapté votre code, et comme on est sous matrice, cela met un peu de temps afin de mettre à jour.

En faisant des essais, j'ai vu que je pouvais récupérer le N° de ligne

Code:

Me.TextBox6=c.row

(oui, je l'ai mis en textbox6 pour mes essais.

Donc, je pense faire une recherche sur les 3 premières colonnes (très rapide), et lors du Combobox1_Click, faire tourner votre code LitChamp2 et cibler sur la ligne :

Code:

ChampAlire="A1:C"&C.row

En fait, j'envisage d'ouvrir le fichier Article4, aller sur la ligne repérée grace à C.row , mettre les nouvelles données, le refermer, et faire tourner sub auto_open..ainsi je peux modifier mes données....puisque je ne peux utiliser ADO

Qu'en pensez-vous ?

pour l'instant, je récupère dans une feuille "BD2" en ligne 2, les données sélectionnées. Il me suffirait d'ouvrir le classeur "Article4" et de copier sur le même numéro de ligne les informations contenues sur la ligne 2 de la BD2...

Code:

“Private Sub ComboBox1_Click()
  For Each c In f.Range("a2:a" & f.[a65000].End(xlUp).Row)
     If c = Me.ComboBox1 Then
       Me.TextBox3 = c.Offset(, 4)
       Me.TextBox4 = c.Offset(, 1)
       Me.TextBox5 = c.Offset(, 2)
       Me.TextBox6 = c.Row 'c.Offset(, 3)
       
       ChDir (ThisWorkbook.Path)
    ChDir ".."
     Sheets("BD2").Select
     Cheminn = CurDir 'ThisWorkbook.Path
     Fichierr = "R58 Promotion profils.xls"       ' Adapter
     onglett = "R58"              ' Adapter
     ChampAliree = "A" & c.Row & ":X" & c.Row ' 20000"         ' Adapter
     ChampOuCopierr = "A2:x2" '      ' Adapter



 
     
     LitChampp ChampOuCopierr, Cheminn, Fichierr, onglett, ChampAliree
       
     End If
  Next c
End Sub
Private Sub B_raz_Click()
  Me.TextBox3 = ""
  Me.TextBox4 = ""
  Me.TextBox5 = ""
  Me.TextBox6 = ""
  Me.TextBox5.SetFocus
End Sub
Sub LitChampp(ChampOuCopierr, Cheminn, Fichierr, onglett, ChampAliree)
  Range(ChampOuCopierr).FormulaArray = "='" & Cheminn & "\[" & Fichierr & "]" & onglett & "'!" & ChampAliree
  'Range(ChampOuCopierr) = Range(ChampOuCopierr).Value    ' Supprime liaison
End Sub

En vous remerciant par avance,

Merci pour votre aide,

Liste déroulante de classeur fermés sans passer par ADO

erics83

XLDnaute Impliqué

job75

XLDnaute Barbatruc

erics83

XLDnaute Impliqué

Pièces jointes

job75

XLDnaute Barbatruc

Pièces jointes

erics83

XLDnaute Impliqué

Pièces jointes

job75

XLDnaute Barbatruc

Pièces jointes

BOISGONTIER

XLDnaute Barbatruc

Pièces jointes

erics83

XLDnaute Impliqué

Pièces jointes

erics83

XLDnaute Impliqué

job75

XLDnaute Barbatruc

BOISGONTIER

XLDnaute Barbatruc

Pièces jointes

erics83

XLDnaute Impliqué

BOISGONTIER

XLDnaute Barbatruc

Pièces jointes

erics83

XLDnaute Impliqué

erics83

XLDnaute Impliqué

Discussions similaires

Liste déroulante de classeur fermés sans passer par ADO

XLDnaute Impliqué

XLDnaute Barbatruc

XLDnaute Impliqué

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Impliqué

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Impliqué

Pièces jointes

XLDnaute Impliqué

XLDnaute Barbatruc

XLDnaute Barbatruc

Pièces jointes

XLDnaute Impliqué

XLDnaute Barbatruc

Pièces jointes

XLDnaute Impliqué

XLDnaute Impliqué

Discussions similaires

Privacy & Transparency

Privacy & Transparency