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

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
Attachment 126827


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"...

Code:

---

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)

Code:

---

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

Code:

---

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

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!

Quote:

---

Any clue why i keep bugging?

---

you are missing some " for the extended properties in your connection string

Code:

---

.ConnectionString = "Data Source=" & "somepath\mybook.xls" & ";" & _
"Extended Properties=""Excel 8.0;hdr=yes;IMEX=1;"""

---

it can all be a single string as you have posted, but you need the additional " in extended properties

for the connection (cn) to be available to all procedures (for future use), it needs to be dimensioned in the general section (at the top) of the code page, not within the procedure

Quote:

---

I want to have extra lines from my excel document added tot the text.

---

if you extend the number of fields in the recordset to fill listbox3 to include other details and the recordset variable is still in scope (dimension rs in the general section of the code page and remove the rs.close from the procedure that creates the recordset for listbox3) then just add the fields like

Code:

---

rs.move first
rs.find "[product code] ='" & ListBox4.List(ii) & "'"
Selection.Text = rs("product code") & vvbtab & rs("description") & vbtab & rs("price") & vbtab & rs("etc")

---

chage field names as required

all the fields would need to be added to the sql to generate the list for listbox3,
or you could just add all fields to the recordset by using * like

Code:

---

Sql = "Select * from [blad1$] where page like '" & listbox1 & "%' and [product family] ='" & listbox2 & "'"

---