PDA

View Full Version : Combobox from external database, not working



ArnoD1995
05-11-2016, 11:24 PM
Dear all,


I'm walking into some problems while executing a macro.
The macro runs on the 'UserForm_Initialize' event, and should fill a combobox (combobox1) with data/choices, from a list in an external database.
Problem is that it runs into different errors, i tried to fix this, solved some errors, but right now i'm getting stuck with error 440, automation error.


Sidenote is that i made some kinda 'dummydatabase' in which only 7 cells contain data (B2:B8).


In addition, when finished, the next step would be to not let the macro use a hardcoded file/filename, but the most recent/last modified file in folder. But, let's first make the current macro working, then i'll try to add the functionality of picking the most recent/last modified file in folder.


Well, here's the macro: (And yes, i did a lot of copy-catting ;) )



Option Explicit




Private Sub UserForm_Initialize()
Dim ListItems As Variant
Dim i As Integer
Dim SourceWB As Workbook
Dim listVal As Range
Dim srcLastRow As Long


'for testing purposes
Dim srcName As String
srcName = "X:\SolidWorks\Solidworks System Files\Databases\Klanten-Database NAV\Database.xlsx"
On Error GoTo exit_proc
With Me.ComboBox1
'Set the number of columns by code
.ColumnCount = 2
.Clear
Application.ScreenUpdating = False
Set SourceWB = Workbooks.Open(srcName, False, True)
'find the last row of data to prevent searching 1468 rows unnecessarily
'srcLastRow = SourceWB.Sheets(1).Cells(Rows.Count, "B").End(xlUp).Row
For Each listVal In SourceWB.Sheets("Blad1").Range("B2:B8") '& srcLastRow)
.AddItem listVal.Value
'Offset(0,-1) gets second column of data from cell to the left
.List(.ListCount - 1, 1) = listVal.Offset(0, -1).Value
Next listVal
SourceWB.Close False
Set SourceWB = Nothing
Application.ScreenUpdating = True
.ListIndex = -1
End With

exit_proc:
SourceWB.Close False ' close the source workbook without saving changes
Set SourceWB = Nothing
Application.ScreenUpdating = True

End Sub


I hope you can help me find the errors and/or mistakes in this code..


Thanks in advance,
Best regards,
Arno

jonh
05-12-2016, 02:33 AM
Private Sub UserForm_Initialize()
GetListFromFile "X:\SolidWorks\Solidworks System Files\Databases\Klanten-Database NAV\Database.xlsx"
End Sub




Sub GetListFromFile(strFile As String)
ComboBox1.Clear
ComboBox1.ColumnCount = 2


Dim cn
Dim rs

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

With cn
.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0 Xml;HDR=NO;';Data Source=" & strFile
With rs
.Open "SELECT * FROM `blad1$B1:C8`", cn
Do Until .EOF
ComboBox1.AddItem .Fields(0)
ComboBox1.List(ComboBox1.ListCount - 1, 1) = .Fields(1)
.MoveNext
Loop
.Close
End With

.Close
End With
End Sub

ArnoD1995
05-12-2016, 05:35 AM
This worked amazingly good!
At first it got stuck with an error 3265 at the row just above '.MoveNext'.
ComboBox1.List(ComboBox1.ListCount - 1, 1) = .Fields(1) . I commented this part out, and now it works like a charm. Can you tell me what this part exactly does?
Thanks in advance.

Arno

jonh
05-12-2016, 08:10 AM
Glad to help.

That bit sets the value for the the second column. I copied it from your own code. There may be a better way of doing it but I'm not an expert in Excel.

aaand I just noticed you used a minus column offset...

.List(.ListCount - 1, 1) = listVal.Offset(0, -1).Value

...which probably explains the error.

So change blad1$B1:C8 to blad1$A1:B8

and switch the fields around

ComboBox1.AddItem .Fields(1)
ComboBox1.List(ComboBox1.ListCount - 1, 1) = .Fields(0)