excel vba从sql选择查询填充工作表中的组合框 [英] excel vba to populate combobox in worksheet from sql select query

问题描述

在Excel中，我与sqlDb的连接良好(在Module-public function中).

I have a good connection in Excel to the sqlDb (in a Module - public function).

我已将此连接与简单的Select查询(在Sheet1代码中)配合使用，效果很好.根据以下代码:

I have used this connection with a simple Select query (in Sheet1 code) which works nicely. Per below code:

Private Sub Selection()

Dim rs As Object
Set rs = CreateObject("ADODB.Recordset")

Dim sqlstr As String
sqlstr = "Select * from Selection"

Call connectDatabase
rs.Open sqlstr, DBCONT

If rs.RecordCount > 0 Then
    For i = 1 To rs.RecordCount
          With ThisWorkbook.Sheets("Sheet1")
            .Cells(i, 1).Value = rs(0)
            .Cells(i, 2).Value = rs(1)
          End With
          rs.movenext
     Next i
 Else
    MsgBox "FFS"

 End If
 rs.Close
 Set rs = Nothing
 Call closeDatabase

 End Sub

现在而不是将这些结果输出到Sheet1的单元格(1,1)和(1,2)中，我希望以上结果将我的Combobox1填充到Sheet1中，并取消输出.我看过很多解决方案，但是我很挣扎.

Now rather than output these results into cells (1,1) and (1,2) of Sheet1, I want above results to populate my Combobox1 on Sheet1 and do away with the output. I have looked at a lot of solutions but I am struggling.

如果能帮助或指导我找到最佳答案，将不胜感激.

Grateful if you can help or direct me to the best answer.

谢谢.

Dasal

推荐答案

Private Sub Selection()

Dim rs As Object

Set rs = CreateObject("ADODB.Recordset")

Dim sqlstr As String
sqlstr = "Select * from Selection"

Call connectDatabase
rs.Open sqlstr, DBCONT

ComboBox1.Clear
If rs.RecordCount > 0 Then
    For i = 1 To rs.RecordCount

        ComboBox1.AddItem rs(1)
          rs.movenext
     Next i
 Else
    MsgBox "FFS"

 End If
 rs.Close
 Set rs = Nothing
 Call closeDatabase

 End Sub

这篇关于excel vba从sql选择查询填充工作表中的组合框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！