[word] Fill or populate ListBox2 from closed .xlsx dependant on selection in ListBox1

[dreojs16]

Dear reader,

Struggling to find a solution for my dual problem: loading data from an external, closed, excel file into a listbox AND defining what needs to be loaded from the selection in another listbox.

This is my UserForm1



PART 1 Loading data from an external, closed, preferrably staying unvisible to user, excle workbook

As I am building my UserForm in msWord i think it would be best to use ADO to load my excel 'database' data.

So I have found this code, but it keeps telling me it needs "expected instruction end"...

Private Sub Document_Open()

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
With cn
    .Provider = Microsoft.ACE.OLEDB.12.0;Data Source=S:\Postmappen\Sjoerd\SH_Product_informatie.xlsx;
Extended Properties="Excel 12.0 Xml;HDR=YES";
    .Open

End With
End Sub

Any clue why i keep bugging?


PART 2 Loading arrays depending on selection in Listbox1

Now I need to have a certain array loaded into ListBox2 depending on the selection in ListBox1(where I have a static three choices)

Private Sub UserForm_Initialize()
   
    ListBox3.MultiSelect = 2     'Making sure we can have multiple selections in ListBox3
    
'Filling ListBox1 with three choices
   
      With ListBox1
      .AddItem "A    Straatmeubilair"
      .AddItem "B    Boomproducten"
      .AddItem "C    Bruggen en dekken"
   End With
   End Sub

Then when one of three is selcted I need to load either array A, array B, or array C. But this needs to be searched for in my excel file. So if in column B a cell contains the letter A, I need to populate ListBox2 with the cells on the same row as columnF_containing_letter_A in column A.
And the same for letter B and letter C

This is what I have

 Set sht1 = wb.Sheets("Korte tekst")
    Set sht2 = wb.Sheets("Bestelinfo")

    Dim rng0 As Range
    Set rng0 = sht2.Range("A;H")   'Alle benodigde kolommen op blad Bestelinfo
    Dim rng1 As Range
    Set rng1 = sht2.Range("F;F")   'ProductFamilie op blad Bestelinfo
    Dim rng2 As Range
    Set rng2 = sht2.Range("A;A")   'ProductCode op blad Bestelinfo
    Dim rng3 As Range
    Set rng3 = sht1.Range("A;A")   'ProductCode op blad Korte tekst
    Dim rng4 As Range
    Set rng4 = sht1.Range("A;B")   'ProductCode met Korte tekst behorende bij product
    Dim rng5 As Range
    Set rng5 = sht2.Range("C;C")   'Prijs op blad Bestelinfo
    Dim rng6 As Range
    Set rng6 = sht2.Range("B;B")   'bladzijde nummer op blad Bestelinfo, om te zoeken of het product onder A(straatmeubilair), B(boomproducten), of C(bruggen&dekken) hoort
      
    Dim ListItems As Variant, i As Integer
   
'zoeken naar de geselcteerde waarde uit ListBox1 op de juiste waarden in ListBox2 weer te geven.
   
    Set fndA = rng6.Find("A")
    Set fndB = rng6.Find("B")
    Set fndC = rng6.Find("C")
    
    Dim iii As Integer
      For iii = 0 To ListBox1.ListCount - 1
    Set ABC = ListBox1.Selected(iii)
    
'zorgen dat de listbox2 dependant is op de geselcteerde waarde in listbox1
   
   With ListBox1.Selected
   
    If ListBox1.Select = 0 Then
    ListItems = rng0.ColumnWidth(0, 0, 0, 0, 0, "", 0, 0)
    'hier moet code die zorgt dat alleen productfamilie die naast fndA weergegeven wordt
    'deze items mogen niet dubbel weergegeven worden

End Sub

Extra ranges (from a different sheet even) have been added to make sure my last wish can be made to come true.

Once a final choice has been made in ListBox3 (I will tackle dependance sometime else...) they will be inserted into my word document
using this code

Private Sub CommandButton3_Click() 'adding items in ListBox4 to the word document at the position of the cursor
  Dim ii As Integer
             
        For ii = 0 To ListBox4.ListCount - 1
                Selection.Text = ListBox4.List(ii)
                Selection.MoveRight
                Selection.TypeParagraph
        Next ii
    Application.ScreenRefresh
    ListBox4.Clear
        
End Sub

PART 3 Final wish

When the contents of ListBox4 are written to my word document, I want to have extra lines from my excel document added tot the text. Now it only writes product codes, but my excel contains product discriptions, prices, etc that should be added next to my productcode in my word document, preferably sperated by a 'tab'.


I hope I have inspired many of you with my strange wishes to help me out!
Thanks in advance!