2017-02-09 14:07
2017-02-09 14:55
程序代码：
Private Sub ComboTime_GotFocus(Index As Integer)
Dim First As String, Second As String
Dim i As Integer, year As Integer
ComboTime(Index).Clear
Set rs = New ADODB.Recordset
rs.Open "SELECT " + Title(3) + " FROM [" & SheetName & "$] group by " + Title(3), cn, adOpenStatic
For i = 0 To rs.RecordCount - 1
If Index = 0 Then
If i = 0 Then
ComboTime(Index).AddItem " "
First = Mid$(rs.Fields.Item(Title(3)), 1, 4)
ElseIf i = rs.RecordCount - 1 Then
Second = Mid$(rs.Fields.Item(Title(3)), 1, 4)
End If
Else
If ComboTime(0).Text <> "" Then
year = CInt(ComboTime(0).Text)
If i = 0 Then
ComboTime(Index).AddItem " "
End If
If Mid$(rs.Fields.Item(Title(3)), 1, 4) = year Then
ComboTime(Index).AddItem Mid$(rs.Fields.Item(Title(3)), 5)
End If
End If
End If
rs.MoveNext
Next i
If Index = 0 Then
For i = CInt(First) To CInt(Second)
ComboTime(Index).AddItem i
Next i
End If
End Sub
2017-02-09 15:24
程序代码：
Dim cn As New ADODB.Connection, RS As New ADODB.Recordset, DDX As Integer, NYFX() As String, I As Integer
Private Sub Combo1_Click()
Combo2.Clear '清除月份记录
For I = 1 To DDX '循环，循环范围是不重复记录的记录数
If Left(NYFX(I), 4) = Combo1.Text Then '判断不重复记录的记录数的前四个字符是否与年份相同
Combo2.AddItem Right(NYFX(I), 2) '如果相同加载月份数，月份是不重复记录的记录数的后二个字符
End If
Next I
End Sub
Private Sub Form_Load()
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\MONTH.mdb;Persist Security Info=False" '2003数据库连接，如果是其他数据库修改即可
RS.Open "select Distinct MONTHS from MONTHX ", cn, 3, 2 'Distinct 关键字是筛选不重复的记录
DDX = RS.RecordCount '得到不重复记录的记录数
ReDim NYFX(1 To DDX) '设置不重复记录的记录的下标
For I = 1 To DDX
NYFX(I) = RS!MONTHS '将不重复记录的记录放在数组变量NYFX(I)中
RS.MoveNext
Next I
RS.Close
cn.Close
Combo1.Clear
Combo2.Clear
For I = 2012 To 2014 '设置年份，可以修改年份范围
Combo1.AddItem I
Next I
End Sub
2017-02-09 22:04
2017-02-10 10:54
程序代码：Dim cn As New ADODB.Connection, RS As New ADODB.Recordset, DDX As Integer, NYFX() As String, I As Integer
Private Sub A()
RS.Open "SELECT left(" + "MONTH" + ",4) as nian FROM [" & "RawData" & "$] group by left(" + "MONTH" + ",4)", cn, adOpenStatic
ComboTime(0).Clear
While Not RS.EOF
ComboTime(0).AddItem RS("nian")
RS.MoveNext
Wend
Set RS = Nothing
End Sub
Private Sub ComboTime_Click(Index As Integer)
Dim Title(3) As String
Title(3) = "MONTH"
If Index = 0 Then
RS.Open "SELECT right(" + Title(3) + ",2) as yue FROM [" & SheetName & "$] where " + Title(3) + " like " + ComboTime(0).Text + "* group by right(" + Title(3) + ",2)", cn, adOpenStatic
ComboTime(1).Clear
While Not RS.EOF
ComboTime(1).AddItem RS("yue")
RS.MoveNext
Wend
Set RS = Nothing
End If
End Sub
Private Sub Form_Load()
cn.Open "Data Source=" + App.Path + "\11111.xlsx" + ";" + "Provider=" + "Microsoft.ACE.OLEDB.12.0;" + "Extended Properties=" + "'Excel 12.0;" + "HDR=" + "Yes" + "IMEX=" + "1';"
Call A
End Sub
2017-02-15 18:05
adoExcel.rar
(13.09 KB)
程序代码：Dim con As New ADODB.Connection
Private Sub addyue()
'添加年份对应的月份
Dim rs As New Recordset, sql As String
Combo2.Clear
If Trim(Combo1) = "" Then Exit Sub
sql = "select right([month],2) as yue from [sheet1$] where [month] like '" & Combo1 & "%' group by right([month],2)"
rs.Open sql, con, adOpenStatic, adLockOptimistic
If rs.State = 0 Then
MsgBox "记录集打开失败"
End
End If
While Not rs.EOF
Combo2.AddItem rs.Fields("yue")
rs.MoveNext
Wend
If Combo2.ListCount > 0 Then Combo2 = Combo2.List(0)
rs.Close
End Sub
Private Sub Combo1_Click()
'点击选择年份更改combo2中相应年份对应的月份
addyue
End Sub
Private Sub Form_Load()
Dim rs As New ADODB.Recordset, sql As String
'con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & App.Path & "\aaaa.xlsx;Extended Properties='Excel 12.0;HDR=YES;IMEX=1';Persist Security Info=False" '连接2007以上版本用
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\aaaa.xls;Extended Properties='Excel 8.0;HDR=YES;IMEX=1';Persist Security Info=False"
con.CursorLocation = adUseClient
If con.State = 0 Then
MsgBox "excel打开失败！"
End
End If
sql = "Select left([month],4) as nian from [Sheet1$] group by left([month],4)"
rs.Open sql, con, adOpenStatic, adLockOptimistic
If rs.State = 0 Then
MsgBox "记录集打开失败"
End
End If
Combo1.Clear
While Not rs.EOF
Combo1.AddItem rs.Fields("nian") '添加不重复的年份
rs.MoveNext
Wend
If Combo1.ListCount > 0 Then Combo1 = Combo1.List(0)
rs.Close '关闭记录集对象
addyue
End Sub
2017-02-15 20:28
2017-02-16 19:17
2017-02-16 19:56