Gegevens zoeken uit ander excel sheet

[joskjos]

Hallo,

Ik ben op zoek naar een VBA code die het volgende kan:
- Met een commandbutton een ander willekeurige Excel bestand kiezen en openen.
- In het gekozen bestand moet gezocht worden op een criteria welke is: naam
- Als het bestand is gekozen dan moet er een popup-venster komen met de aantal gevonden criteria. (alleen de tekst achter het woord naam moet worden getoond middels een selectievakje in het popup-venster)
Zoals het voorbeeld bestand zou er in het popup-venster komen te staan middels selectievakjes:
Selectievakje1: data-abc
Selectievakje2: data-def
Selectievakje3: data-ghi
- Als men de selectievakjes selecteert dan moeten de gegevens daarvan worden getoond in het bestand: Gegevens zoeken - Blad1 - kolom A.
Zijn bijvoorbeeld alleen selectievakje1 en selectievakje2 geselecteerd dan mogen alleen die worden getoond in kolom A achtereenvolgens.

Bekijk bijlage Gegevens zoeken.xlsm
Bekijk bijlage wisselendbestand.xlsm

Wie kan mij met deze VBA code helpen?

Gr,
Jos

 

[VenA]

Is het wisselend bestand altijd zo'n chaos of staan de gegevens netje aaneengesloten? Staat de naam altijd in rij 5?

 

[joskjos]

De naam staat niet altijd in rij 5. in 1 kolom komt naam ook maar 1x voor.
In de kolommen staan lege cellen etc. vandaar dat mijn opzet er als een chaos uitziet. Maar er kan wel gezocht worden naar het woord naam die staat altijd in een kolom.

 

[VenA]

Zonder lege kolommen kom ik tot zoiets.

Private Sub CommandButton1_Click()
    ChDrive "C:\"
    FileToOpen = Application.GetOpenFilename _
    (Title:="Please select Design Summary", _
    FileFilter:="Excel Files *.xlsm (*.xlsm),")
If FileToOpen = False Then
    MsgBox "No file specified!", vbExclamation, "LOOK OUT !"
    Exit Sub
  Else
    Workbooks.Open Filename:=FileToOpen, UpdateLinks:=0
    With ActiveWorkbook
        ar = .Sheets(1).UsedRange
        .Close 0
    End With
End If
For j = 1 To UBound(ar, 1)
    For jj = 1 To UBound(ar, 2)
        If Left(ar(j, jj), 5) = "naam:" Then c00 = c00 & "|" & Replace(ar(j, jj), "naam: ", "")
    Next jj
Next j
ComboBox1.List = Split(Mid(c00, 2), "|")
End Sub

Met ipv selectievakjes een combobox

Private Sub ComboBox1_Change()
For j = 1 To UBound(ar)
    c01 = c01 & "|" & ar(j, ComboBox1.ListIndex + 1)
Next j
Sheets(1).[A2].Resize(UBound(Split(Mid(c01, 2), "|")) + 1, 1) = Application.Transpose(Split(Mid(c01, 2), "|"))
End Sub

 

[joskjos]

De eerste code is wat ik bedoel.
De tweede code met combobox moet echt met selectievakjes worden en bij het aan/uit vinken van de vakjes worden alle resultaten onder elkaar geplakt in kolom A.
Zoals je de code nu hebt moet ik steeds wisselen.

Ook komen alle mogelijke tekens voor in het wisselendbestand zoals , . / = in een cel. Deze moeten ook gekopieerd worden. Ik kreeg nu hier een foutcode op.

 

[VenA]

Dan zou ik maar met een representatief voorbeeld komen. Er wordt nl niets gekopieerd. De gegevens worden ingelezen in een array en obv de keuze uit de combobox in A2 en verder gezet. Hoe je het voor ogen hebt met vakjes aanvinken en uitvinken en hoe je dan de gegevens onder elkaar wil krijgen mag ook wel wat meer uitleg.

 

[joskjos]

Zie hier het aangepaste bestand.
Ik heb overal bijgezet wat zo ongeveer de bedoeling is. Hopelijk is het wat duidelijker geworden?

Bekijk bijlage wisselendbestand.xlsm
Bekijk bijlage Gegevens zoeken1.xlsb

 

[VenA]

Vertel eens wat meer over jouw complete project. In #5 geef je aan dat er iets misgaat met bepaalde leestekens. In een ander draadje vraag je om een counttif zonder bepaalde leestekens? Terwijl deze weer niet voorkomen in het gevraagde representatieve voorbeeldje.

Van formulieren weet ik te weinig om je verder te helpen. Het nut en doel van het geheel ontgaat mij ook. Maar mogelijk dat een andere helper het oppakt

 

[HSV]

Test het maar eens.
Je kan maar een keer de checkbox aanvinken; dat wordt opgeheven bij het sluiten van het form.

 

[joskjos]

@HSV, Bedankt dit is wat ik ongeveer bedoel.

Is het mogelijk dat er wel meerdere checkboxen tegelijk kunnen worden aangevinkt?
En kan de gevonden criteria dus in dit geval data-abc automatisch als titel achter de checkbox komen.
Dus is de gevonden criteria data-jkl dat deze als titel verschijnt achter de checkbox?

Wat alleen nog niet werkte is dat alle tabbladen worden doorzocht en als speciale tekens = voorkomt in een cel kreeg ik een foutmelding.

 

[HSV]

1). Dit werkt al in het bestand wat ik plaatste.
2). begrijp ik niet; in de caption staat dit toch al? moet dat 2x.
3).

 ReDim ar2(0 To UBound(ar), 0)[COLOR="#FF0000"]As String [/COLOR]

 

[joskjos]

1. Je had gelijk. Dit werkte al. Alleen in andere tabbladen zoeken werkt nog niet.
2. In het userform staan nu standaard 3 checkboxen met titels erachter. Wat de bedoeling is: Als er in het zoekbestand gezocht wordt alles na de : te tonen als titel achter de checkbox. Ook als er bijvoorbeeld 5 criteria zijn gevonden dat er dan automatisch 5 checkboxen worden aangemaakt. Wordt er 1 criteria gevonden dan wordt er maar 1 checkbox aangemaakt. Is dit ook mogelijk?
3. Werkt nu bedankt!

Gr,
Jos

 

[HSV]

Hier over meerdere bladen.
Max 1000 rijen; 100 kolommen, 3 bladen.
Wat je met die extra aangemaakt checkboxen bedoelt ontgaat me.
Misschien pikt iemand anders het wel weer verder op.

Edit: 'Gegevens zoeken3' is eenvoudiger geschreven.

 

[joskjos]

Bedankt ook meerdere tabbladen werkt nu prima!

Ik zal hieronder proberen de zoekfunctie uitteleggen:
- in het zoekbestand moet worden gezocht naar" naam:"
- Alles achter ":"is de titel bijvoorbeeld "data-abc". Dit kan bij een ander zoekbestand ook anders zijn bijvoorbeeld "data-123"
- de zoekcriteria "naam:" komt altijd voor dus dat is nu goed verwerkt in het bestand.
- Het doel is als er wordt gevonden data-123 dat dit de titel wordt en er een checkbox wordt aangemaakt. Komt ook data-456 voor in het zoekbestand dan wordt hiervoor ook automatisch een checkbox aangemaakt.
Nu staat in de userform achter de checkbox data-abc en daar wordt nu opgezocht want als ik in het te doorzoeken bestand achter naam: data-123 zet dan wordt deze niet gevonden.

Hopelijk is het iets duidelijker voor je?

Gr,
Jos

 

[VenA]

In #4 staat een methode hoe je de namen kan opvragen. Deze zal je dan aan de caption van de checkboxen kunnen toewijzen en vervolgens de code van HSV erop los laten. Je geeft veel te weinig informatie over hoe het daadwerkelijk in elkaar steekt om er iets voor te maken. Hoeveel van die vinkvakken zijn er maximaal? Moet het formulier elke keer opnieuw opgebouwd worden? Wat is het nut van deze toepassing? Waarom heb je nog niet gereageerd op jouw andere vraag?

 

[joskjos]

@VenA, Ik zal je even tekst en uitleg geven:

Ik heb meerdere excel bestanden met adresgegevens. In elk excel bestand staat per kolom een naam met alle adresgegevens etc. dit verspreid zich over meerdere tabbladen. Het ene bestand heeft 3 tabbladen en het andere bestand meer als 10 tabbladen.

Nu wil ik in het Gegevenszoeken bestand dat er in kolom A enkel de namen en adressen komen welke ik selecteer middels checkboxen. Het formulier zal dus elke keer opnieuw opgebouwd moeten worden. De ene keer 20 checkboxen met bijbehorende naam en de andere keer maar 3 checkboxen met bijbehorende naam. Het licht er net aan in welk bestand ik dus de namen zoek.

Ik kan vervolgens heel makkelijk enkel de namen en adressen afdrukken en hoef dus niet alle excel bestanden handmatig te doorzoeken op de namen.

Het feit dat ik voor een userform kies is dat ik zelf een keuze wil maken welke namen ik wil afdrukken. Ik kan dat dan voor diverse doeleinden gebruiken vandaar. Met een combobox weet ik niet of deze optie ook kan en zal lastiger zijn.

Wellicht heb je nu een beter beeld van het geheel?

 

[VenA]

Dan zou ik maar met een representatief voorbeeld komen.

Of al het andere wat je inmiddels is toegereikt proberen in te passen in jouw eigen bestand. En dan zien we wel waar waar je nog tegen aan loopt.

 

[HSV]

Een combobox vullen is inderdaad veel handiger dan tig chexkboxen plaatsen op een formulier.
Helaas begrijp ik de achterliggende gedachte niet van je verhaal met de gehele code.
De code is als volgt.
Je opent een bestand waarvan je niet weet welke namen erin staan.
Door nu een checkbox aan te vinken weet je dus niet of het in het juiste bestand staat wat je net hebt geopend.
Als je niet steeds wil blijven zoeken is dit een geheel verkeerde aanpak.
Hier staan codes genoeg op het forum om al je bestanden te verzamelen in één werkblad.
Vandaar uit zoek je met een combobox met meerdere kolommen naar de personen.

Een Listbox is ook een overweging.

 

[joskjos]

Ik heb weinig kennis van VBA dus vandaar mijn vragen.

@VenA, zoals jij de eerste opzet code had gemaakt was goed behalve lege kolommen en meerdere tabbladen.
Ik zag toen in de combobox de zoekwaarden staan. Het zou mooi zijn als dit werkt in een Listbox. Ik kan dan neem ik aan meerdere keuze tegelijk maken?
Ik kan niet een veel beter voorbeeldbestand geven i.v.m. privacy adresgegevens.
Toch voor je beeldvorming heb ik het wisselendbestand iets aangepast. Dit is zo als ik de bestanden aangeleverd krijg. Deze worden weer uit een ander programma geëxporteerd.

Bekijk bijlage wisselendbestand.xlsm

@HSV, ik weet wel in welke bestand de namen staan. Alleen hoef ik niet van elk bestand alle namen eruit te hebben en in het zoekbestand te hebben voor mijn resultaat. Dus heel selectief een aantal namen uit een bestand halen.

Een Listbox is misschien dan ook een betere oplossing.

 

[joskjos]

Ik heb nu de volgende code aangepast alleen nu wordt de combobox niet meer gevuld. Wat gaat hier mis:

Private Sub CommandButton1_Click()
    ChDrive "C:\"
    FileToOpen = Application.GetOpenFilename _
    (Title:="Please select Design Summary", _
    FileFilter:="Excel Files,")
If FileToOpen = False Then
    MsgBox "No file specified!", vbExclamation, "LOOK OUT !"
    Exit Sub
  Else
    Workbooks.Open Filename:=FileToOpen, UpdateLinks:=0
    With ActiveWorkbook
     On Error Resume Next
         For jjj = 1 To 6
        sn = .Sheets(jjj).UsedRange
For jj = 1 To UBound(sn, 2)
    For j = 1 To UBound(sn)
     If Mid(sn(j, jj), jjj) = "naam:" Then c00 = c00 & "|" & Replace(sn(j, jj), "naam:", "")
    Next j
Next jj
Next jjj
 On Error GoTo 0
        .Close 0
End With
ComboBox1.List = Split(Mid(c00, 2), "|")
End If
End Sub

Private Sub ComboBox1_Change()
For j = 1 To UBound(ar)
    c01 = c01 & "|" & ar(j, ComboBox1.ListIndex + 1)
Next j
Sheets(jjj).[A2].Resize(UBound(Split(Mid(c01, 2), "|")) + 1, 1) = Application.Transpose(Split(Mid(c01, 2), "|"))
End Sub

Misschien kan iemand mij helpen om dit in een listbox te zetten middels code?

Gr,
Jos