Simple Excel to connect to the database and display filtered data.

需求：

A1中放一个Dropdownlist，B1放一个button，点击button后从数据库读取数据绑定到Dropdownlist，选择任一item后，再去数据库读取对应内容展示出来

 

Sub btnList_Click()

    ActiveSheet.Range("5:65536").ClearContents
    
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim cmd As New ADODB.Command
    Dim sqlText As String
    Dim Row As Long
    Dim Findex As Long
    
    conn.Open "DSN=Oracle;;DBQ=ORAPRDPIM;......"
    cmd.ActiveConnection = conn
    cmd.CommandType = adCmdText
    sqlText = "SELECT DISTINCT ult_parent_name FROM pm_own.hy_master_exposure_sdb e, pm_own.sec_risk_measures srm WHERE srm.ssm_id = e.cusip AND    e.comp_sec_type_code NOT IN ('ABS','TSY') ORDER BY ult_parent_name "
    cmd.CommandText = sqlText
    
    Set rs = cmd.Execute
    
    On Error GoTo ErrorHandler
    
    ActiveSheet.DropDowns("ddlUltParent").Delete
    
ErrorHandler:
    'do nothing
Resume Next

    ActiveSheet.DropDowns.Add(Range("A1").Left, Range("A1").Top, 240, 18).Select
    With Selection
        .Name = "ddlUltParent"
        .OnAction = "ddlUltParent_SelectionChanged"
        
        Do While Not rs.EOF
        Row = Row + 1
        .AddItem rs.Fields(0).Value, Row
        rs.MoveNext
        Loop
        .DropDownLines = 50
        
    End With
    
    ActiveSheet.Range("A1").Select
    
    conn.Close
End Sub

Public Sub ddlUltParent_SelectionChanged()
    
    'Sheets("Sheet1").Range("5:65536").ClearContents
    ActiveSheet.Range("5:65536").ClearContents
  
    Dim selectIndex As Integer
    Dim selectText As String
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim cmd As New ADODB.Command
    Dim sqlText As String
    Dim data As Worksheet
    Dim dd As DropDown
    Set dd = ActiveSheet.DropDowns("ddlUltParent")

    'Set data = Sheets("Sheet1")
    Set data = ActiveSheet
    selectIndex = ActiveSheet.DropDowns("ddlUltParent").Value
    selectText = dd.List(selectIndex)

    conn.Open "DSN=Oracle;;DBQ=ORAPRDPIM;......"
    cmd.ActiveConnection = conn
    cmd.CommandType = adCmdText
    
    sqlText1 = "SELECT e.acct_no, e.acct_name, e.mgr_name, e.cusip, e.isin, e.description,"
    sqltext2 = "e.coupon, e.maturity, e.price, e.quantity, e.bond_exposure,"
    sqltext3 = "AND    e.ult_parent_name = '" + selectText + "'"
    
    sqlText = sqlText1 + sqltext2 + sqltext3
    cmd.CommandText = sqlText
    Set rs = cmd.Execute

    Row = 5
    For X = 1 To rs.Fields.Count
    data.Cells(Row, X).Value = rs.Fields(X - 1).Name
    Next

    Do While Not rs.EOF
    Row = Row + 1
    For Findex = 0 To rs.Fields.Count - 1
    data.Cells(Row, Findex + 1).Value = rs.Fields(Findex).Value
    Next Findex
    rs.MoveNext
    Loop

End Sub

 

转载于:https://www.cnblogs.com/davisping/p/3400035.html