Results 1 to 2 of 2

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

  1. #1

    Thread Starter
    Junior Member
    Join Date
    May 2015
    Posts
    17

    [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
    Name:  UserForm1.jpg
Views: 261
Size:  14.4 KB


    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!

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [word] Fill or populate ListBox2 from closed .xlsx dependant on selection in List

    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

    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 & "'"
    Last edited by westconn1; May 22nd, 2015 at 04:59 AM. Reason: fix syntax for rs.find
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width