afhankelijk dropboxen in Word vullen vanuit Excelsheet

Status
Niet open voor verdere reacties.
Plotinus

Plotinus

Gebruiker
Lid geworden
25 mrt 2007
Berichten
649
Ik ben bezig met een standaardbrief waarbij ik fouten in de adressering zoveel mogelijk wil voorkomen. Om dit te bereiken beschik ik over een Excelsheet met (foutloze) plaatsnamen en gemeentes. Wat ik nu wil is dat in één combobox (CB1) de plaatsnamen worden ingelezen en in de tweede combobox (CB2) dan de gemeente wordt weergegeven. Dus als in CB1 een plaatsnaam wordt ingetikt die niet voorkomt in de lijst, dan blijft CB2 leeg en is daar een gemeentenaam handmatig in te vullen. Maar als de plaatsnaam wel voorkomt, dan geeft CB2 dus de gemeentenaam weer.

De eerste combobox vullen lukt, maar de tweede geeft een foutmelding. Ik heb code gebruikt die ik bij een voorbeeld op dit forum ('van elkaar afhankelijke comboboxen in een userform'; 17 mei 2015) heb gevonden als alles binnen Excel gebeurt. Hier gaat het echter om een brief plaats/gemeent-gegevens die uit Excel geïmporteerd worden.

Iemand een idee hoe dit op te lossen?

(in bijgevoegd bestand doet de inhoud van de brief er even niet toe)
 

Bijlagen

  • gemeenten en plaatsen Nederland.zip
    53,7 KB · Weergaven: 36
Is het een mogelijkheid een twee dimensionale array in Word vanuit Excel (2 kolommen) in te lezen, vervolgens binnen de array (1e kolom) te zoeken naar het geselecteerde item in de combobox (plaatsnaam, toegewezen aan een variabele) en vervolgens het naastliggende item in de array (2e kolom: gemeente) op te halen?

Ik ben al even aan het prutsen geweest, (zie module1, 'testarray'). Vooralsnog stoot ik op het probleem dat ik enkel 1 kolom (1 dimensie) in kan lezen. Dus de plaatsnamen heb ik, maar niet de bijbehorende gemeentes.

Iemand een idee?
 

Bijlagen

  • Standaardbrief.zip
    56 KB · Weergaven: 35
Ik ben er min of meer uit; voor wie het interesseert zie bijlage.

Het enige dat nog wringt is dat ik het aantal rijen wil kunnen bepalen. Nu is het er 'hard' ingezet, en dat is uiteraard niet fraai.
 

Bijlagen

  • gemeenten en plaatsen Nederland3.zip
    59,6 KB · Weergaven: 39
Laat dit soort fratsen weg als je hier bestanden plaatst. Er zit niemand op te wachten dat Excel ineens verdwenen is.
Code: 
'xlApp.Visible = False

Maak van jouw gegevensbereik in Excel een echte tabel.
Voor het dynamisch vullen van de combobox is dit dan voldoende
Code: 
ComboBox1.List = xlBook.sheets(1).listobjects(1).databodyrange.Value

Voor het wijzigen gebruik je dan
Code: 
Private Sub ComboBox1_Change()
  If ComboBox1.ListIndex > 0 Then TextBox1 = ComboBox1.Column(1) Else TextBox1.Value = ""
End Sub
 
Dank VenA: het werkt en de code is weer een stukje korter en krachtiger.

Wat ik echter niet snap is waarom er een foutmelding komt als je ... listobjects(2).data... invult: 'Fout 9 tijdens uitvoering; Het subscript valt buiten het bereik.' Kan ik niet gewoon de kolom kiezen waar ik de dat uit haal, of moet het altijd kolom 1 zijn?
 
De eerste kolom van een Combobox is Column(0), de tweede is (1) enz.
 
Daarnaast als je maar 1 tabel in het blad hebt staan dan is dat altijd listobjects(1). Alle gegevens uit deze tabel kan je dan inlezen in een combobox en verder gebruiken in het formulier. Je zal wel de column.count moeten aanpassen omdat anders niet alle kolommen ingeladen worden.
 
Hartelijk dank HSV en VenA. Inmiddels ben ik met jullie hulp alweer een stukje verder.
 
VenA zei:
Je zal wel de column.count moeten aanpassen omdat anders niet alle kolommen ingeladen worden.
Klik om te vergroten...

Dat zit iets anders: de gehele range wordt in de combobox geladen (check bijv. met msgbox ubound(combobox1.list,2)); ze worden echter pas zichtbaar als je de eigenschap .listount aanpast.
 
helder snb Een reden dus om de range zo beperkt mogelijk te houden om het geheel snel en soepel te houden. Ik kan daar dus nog wel wat in bereiken zie ik.

Nog een klein vraagje:
Ik heb het nu zo ingericht dat, nadat de nodige zaken zijn opgehaald uit een Excelbestand, alle Excelbestande worden gesloten. Mijn gebruikers vinden dat niet zo leuk (VenA had het hier al over). Ik kan dit eenvoudig oplossen om de betreffende code (xlApp.Quit) af te zetten, maar dan blijft het bestand op de achtergrond (niet zichtbaar) open staan of in gebruik (ik weet niet hoe je dat precies uitdruk), zodat je het alleen via 'alleen lezen' kan openen. Ook niet echt ideaal, als je iets wilt wijzigen bijvoorbeeld. Vraag is dus, kan ik selectief het bestand sluiten?

De betreffende relevante code is als volgt:

Code: 
 Dim xlBook As Object
    Dim xlApp As Object
      
     Const strWorkBookName As String = "I:\Brieven\\Hulpbestand standaardbrieven.xlsb"
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err Then
        Set xlApp = CreateObject("Excel.Application")
    End If
    On Error GoTo 0
    Set xlBook = xlApp.Workbooks.Open(FileName:=strWorkBookName)
    CBPlSloop.List = xlBook.sheets("Woonplaatsen").listobjects(1).Databodyrange.Value
    

(allerlei niet relevante handelingen)

  
    Set xlBook = Nothing
   [B] xlApp.Quit[/B]
    Set xlApp = Nothing
 
Zo (zonder overbodige objectvariabelen) zou de code moeten zijn in plaats van de volledige code (14 regels) die jij plaatste.

Code: 
Private Sub Userform_Initialize()
  with getobject("I:\Brieven\Hulpbestand standaardbrieven.xlsb")
    CBPlSloop.List = .sheets("Woonplaatsen").listobjects(1).Databodyrange.Value
    .close 0
  end with
End With

Maar het kan natuurlijk veel eenvoudiger als je de gegevens uit het Excelbestand gewoon permanent in het Worddocument in een documentvariabele zet.
Dan is dat hele verband met Excel niet nodig als je met het Wordbestand werkt.

Ik heb het maar even voor je gedaan in het bijgevoegde bestand.
 

Bijlagen

  • __Standaardbrief.docm
    51,8 KB · Weergaven: 48
Laatst bewerkt:
Perfect! Gelijk geïmplementeerd:
- Minder omslachtige code;
- Wekt aantoonbaar sneller
- Excelsheet sluit netjes af.

Hartelijk dank!
 
Kijk ook eens naar de Wordoplossing zonder Excel.
 
Excuses snb, ik had zo ver nog net gekeken.

Dit heeft inderdaad de charme van de eenvoud, is minder kwetsbaar en hoogstwaarschijnlijk nog sneller ook. Echter, ik weet niet hoe ik een hele tabel in een keer in een Documentvariabele moet pompen. Heb je een link naar een duidelijke uitleg?

Daarnaast maak ik inmiddels gebruik van (steeds) meer tabellen die ik bovendien in Excel gemakkelijk kan aanvullen/aanpassen, zoals een groeiend aantal gebruikers (met naam, e-mailadres, tel...). Als het geheel is uitontwikkeld kan wellicht de stap naar 'inbakken in Word' gezet worden. Enige studie en speurwerk zal dat nog wel vragen.

Hartelijk dank voor deze tip!
 
In het Worddocument vulde ik de documentvariabele 'plaats' met deze code:

Code: 
Sub M_snb()
  With GetObject("G:\OF\gemeenten en plaatsen Nederland.xlsb")
     sn = .sheets(1).Cells(1).currentregion
     .close 0
  end with

  For j = 2 To UBound(sn)
    If sn(j, 3) <> "-" Then c00 = c00 & Chr(0) & sn(j, 3) & Chr(0) & "_" & sn(j, 2)
  Next

  ThisDocument.Variables("plaats") = Mid(c00, 2)
End Sub
 
Heel mooi! Ik heb het kunnen reproduceren. Nu nog binnenkort bestuderen wat er precies gebeurt en hoe.

Hartelijk dank.
 
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan