-
May 21st, 2015, 10:37 AM
#1
Thread Starter
Junior Member
[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
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!
-
May 21st, 2015, 04:34 PM
#2
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|