Combo Box loaded with SQL using (Db2 - IBM database)
The IBM Database (Db2) is already in Excel, so it is possible to create SQL queries in Excel. The data is processed in RAM,
which streamlines processes.In this example we are going to load 2 validation lists with data from another spreadsheet, using SQL to filter and group the data,
the first row of the spreadsheet will be used to generate the field in the SQL record.
state
Sao Paulo
Sao Paulo
Bahia
Bahia
Bahia
Bahia
Sao Paulo
Minas Gerais
Minas Gerais
Minas Gerais
Minas Gerais
Minas Gerais
Sao Paulo
Sao Paulo
Sao Paulo
Sao Paulo
Bahia
Bahia
Minas Gerais
City
Mairiporã
Maua
Feira de Santana
Serrinha
Itaberaba
Bruma
Osasco
Varginha
Belo Horizonte
Pirapóra
Seven lagoons
Three Marys
Sorocaba
Saints
Cotia
Campinas
Victory of the Conquest
Caculé
Ipatinga
Download Example
Paste the code below into the module you created.
'------------------------------------------------- ----------------------------
'Include reference:' Microsoft DAO 3.6 Object Library '
'------------------------------------------------- ----------------------------
Sub Load_Drop_down1 ()
Dim Db2 As Database
Dim RSt2 As Recordset
Worksheets ("Main"). Shapes ("Drop_down1"). ControlFormat.RemoveAllItems' Clear combo
Set Db2 = OpenDatabase (ThisWorkbook.Path & "\" & ThisWorkbook.Name, False, False, "Excel 8.0")
Set RSt2 = Db2.OpenRecordset ("SELECT State FROM [Data $] GROUP BY State;")
Call Carregar_Drop_down2 (RSt2 ("Status"))
While Not RSt2.EOF
Worksheets ("Main"). Shapes ("Drop_down1"). ControlFormat.AddItem Format (RSt2 ("State"), "dd / mm / yy")
RSt2.MoveNext
Wend
Worksheets ("Main"). Shapes ("Drop_down1"). ControlFormat.ListIndex = 1 'Insert first line in the combo window
MsgBox "Base loaded successfully !!", vbInformation, "Information"
End Sub
Sub Drop_down_1 () 'pick up selected item
With Worksheets ("Principal"). Shapes ("Drop_down1"). ControlFormat
Call Carregar_Drop_down2 (.List (.Value))
End With
End Sub
Sub Load_Drop_down2 (Drop_down1) 'load comb2 with selected item in combo1
Worksheets ("Main"). Shapes ("Drop_down2"). ControlFormat.RemoveAllItems' Clear combo
'SQL-DB2
Set Db2 = OpenDatabase (ThisWorkbook.Path & "\" & ThisWorkbook.Name, False, False, "Excel 8.0")
Set RSt2 = Db2.OpenRecordset ("SELECT State, City FROM [Data $] WHERE State like (" & "'" & Drop_down1 & "'" & ") GROUP BY State, City;")
While Not RSt2.EOF
Worksheets ("Main"). Shapes ("Drop_down2"). ControlFormat.AddItem RSt2 ("City")
RSt2.MoveNext
Wend
Worksheets ("Main"). Shapes ("Drop_down2"). ControlFormat.ListIndex = 1 'Insert first line in the combo window
RSt2.Close
Db2.Close
End Sub
