Dim Cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Sub TableTemp()
On Error Resume Next
Dim mySQL As String
On Error Resume Next
mySQL = "Drop table tblTemp"
Cn.Execute mySQL
mySQL = "SELECT TK.STT, TK.ACCOUNT_ID, TK.CR_NAME, TK.CR_AMOUNT, TK.DB_AMOUNT, " & _
"Partition([stt],1,(select top 1 stt from tk order by stt DESC)," & txtREC & ") AS DK INTO tblTemp " & _
"FROM TK;"
rs.Open mySQL, Cn, 1, 3
rs.Close
End Sub
Private Sub cmdNapList_Click()
On Error Resume Next
Dim mySQL As String
mySQL = "SELECT tblTemp.DK From tblTemp GROUP BY tblTemp.DK"
TableTemp
lstDK.Clear
If rs.State = 1 Then rs.Close
rs.Open mySQL, Cn, 3, 3
If rs.RecordCount = 0 Then
Exit Sub
Else
Do While Not rs.EOF
lstDK.AddItem rs!dk
rs.MoveNext
Loop
End If
rs.Close
End Sub
Private Sub cmdTachSheet_Click()
On Error Resume Next
Dim oApp As New Excel.Application, oBook As Excel.Workbook, oSheet As Excel.Worksheet
Dim i As Integer, iNumCols As Integer, i1 As Integer, mySQL As String, strDK As String
Set oBook = oApp.Workbooks.Add
For i1 = 0 To lstDK.ListCount - 1
If lstDK.Selected(i1) = True Then
lstDK.ListIndex = i1
strDK = lstDK.Text
mySQL = "SELECT tblTemp.STT, tblTemp.ACCOUNT_ID, tblTemp.CR_NAME, tblTemp.CR_AMOUNT, tblTemp.DB_AMOUNT " & _
"FROM tbltemp " & _
"WHERE dk like '" & strDK & "';"
rs.Open mySQL, Cn, 1, 3
iNumCols = rs.Fields.Count
oBook.Sheets.Add
Set oSheet = oBook.ActiveSheet
oSheet.Name = "Dom" & Replace(Trim(strDK), ":", "-")
For i = 1 To iNumCols
With oSheet
.Cells(5, i).Value = rs.Fields(i - 1).Name
.Cells(5, i).Font.Bold = True
.Cells(5, i).Font.ColorIndex = 5
.Cells(5, i).Interior.ColorIndex = 34
End With
Next
oSheet.Range("A6").CopyFromRecordset rs
oSheet.Columns.AutoFit
lstDK.Selected(i1) = False
rs.Close
End If
Next i1
For Each sh In oBook.Sheets
If Left(sh.Name, 3) <> "Dom" Then sh.Delete
Next
oApp.Visible = True
End Sub
Private Sub Form_Load()
strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Dom.mdb"
Cn.Open strcon
cmdNapList_Click
End Sub
Private Sub chkAll_Click()
Dim i As Integer
If chkAll.Value = 1 Then
chkAll.Caption = "Bo chon tat ca"
For i = lstDK.ListCount - 1 To 0 Step -1
lstDK.ListIndex = i
lstDK.Selected(i) = True
Next i
Else
chkAll.Caption = "Tat ca"
For i = lstDK.ListCount - 1 To 0 Step -1
lstDK.ListIndex = i
lstDK.Selected(i) = False
Next i
End If
End Sub
em đành làm theo cách củ chuối vậy. gọi form access trong vb6 ...
