Private Sub Command3_Click()
Dim sql As String
Dim msgtext As String
Dim objrst1 As ADODB.Recordset
Dim objrst As ADODB.Recordset
Dim report1() As String
Dim report2() As String
Dim i, j As Integer
sql = "select * from dianlishichang where time='" & Format$(DTPicker1.Value) & "'"
Set objrst = ExecuteSQL(sql, msgtext) While Not objrst.EOF
ReDim Preserve report1(0 To objrst.RecordCount - 1, 0 To 16) report1(i, 0) = objrst.Fields(3)
report1(i, 1) = objrst.Fields(4)
report1(i, 2) = objrst.Fields(5)
report1(i, 3) = objrst.Fields(6)
report1(i, 4) = objrst.Fields(7)
report1(i, 5) = objrst.Fields(8)
report1(i, 6) = objrst.Fields(9)
report1(i, 7) = objrst.Fields(10)
report1(i, 8) = objrst.Fields(11)
report1(i, 9) = objrst.Fields(12)
report1(i, 10) = objrst.Fields(13)
report1(i, 11) = objrst.Fields(14)
report1(i, 12) = objrst.Fields(15)
report1(i, 13) = objrst.Fields(16)
report1(i, 14) = objrst.Fields(17)
report1(i, 15) = objrst.Fields(18)
report1(i, 16) = objrst.Fields(19) i = i + 1
objrst.MoveNext
Wend
sql = "select * from dianchangshijian where time='" & Format$(DTPicker1.Value) & "'"
Set objrst1 = ExecuteSQL(sql, msgtext)
While Not objrst1.EOF
ReDim Preserve report2(0 To objrst1.RecordCount - 1, 0 To 10)
report2(i, 0) = objrst1.Fields(2)
report2(i, 1) = objrst1.Fields(3)
report2(i, 2) = objrst1.Fields(4)
report2(i, 3) = objrst1.Fields(5)
report2(i, 4) = objrst1.Fields(6)
report2(i, 5) = objrst1.Fields(7)
report2(i, 6) = objrst1.Fields(8)
report2(i, 7) = objrst1.Fields(9)
report2(i, 8) = objrst1.Fields(10)
report2(i, 9) = objrst1.Fields(11)
report2(i, 10) = objrst1.Fields(12)
i = i + 1
objrst1.MoveNext
Wend
If Dir(App.Path & "\Temp\excel.bz") = "" Then '判断EXCEL是否打开
Set xlApp = CreateObject("Excel.Application") '创建EXCEL应用类
xlApp.Visible = True '设置EXCEL可见
Set xlBook = xlApp.Workbooks.Open(App.Path & "\temp\agc2.xls") '打开EXCEL工作簿
Set xlsheet = xlBook.Worksheets(1) '打开EXCEL工作表
xlsheet.Activate '激活工作表
xlsheet.Cells(1, 2) = Format$(DTPicker1.Value, "yyyy年mm月dd日")
xlsheet.Cells(1, 4) = WeekdayName(Weekday(DTPicker1.Value))
For i = 0 To objrst.RecordCount - 1 xlsheet.Cells(i + 3, 1) = report1(i, 0)
xlsheet.Cells(i + 3, 2) = report1(i, 1)
xlsheet.Cells(i + 3, 3) = report1(i, 2)
xlsheet.Cells(i + 3, 8) = report1(i, 3)
For j = 4 To 16
xlsheet.Cells(i + 22, j - 3) = report1(i, j)
Next j
Next i
For i = 0 To objrst1.RecordCount - 1
For j = 0 To 10
xlsheet.Cells(i + 8, j + 1) = report2(i, j)
Next j
Next i
'给单元格1行驶列赋值
xlBook.RunAutoMacros (xlAutoOpen) '运行EXCEL中的启动宏
Else
MsgBox ("EXCEL已打开")
End If
End Sub
相似问题,日期和星期都能往EXECL里填,但是记录集中的东西却存不到EXECL里头,如果把其中一个记录集如(objrst1)注释掉,另一个记录集里的数据则能存里头,是不是不能用两个数组往一个EXECL里导数据呢!
Dim sql As String
Dim msgtext As String
Dim objrst1 As ADODB.Recordset
Dim objrst As ADODB.Recordset
Dim report1() As String
Dim report2() As String
Dim i, j As Integer
sql = "select * from dianlishichang where time='" & Format$(DTPicker1.Value) & "'"
Set objrst = ExecuteSQL(sql, msgtext) While Not objrst.EOF
ReDim Preserve report1(0 To objrst.RecordCount - 1, 0 To 16) report1(i, 0) = objrst.Fields(3)
report1(i, 1) = objrst.Fields(4)
report1(i, 2) = objrst.Fields(5)
report1(i, 3) = objrst.Fields(6)
report1(i, 4) = objrst.Fields(7)
report1(i, 5) = objrst.Fields(8)
report1(i, 6) = objrst.Fields(9)
report1(i, 7) = objrst.Fields(10)
report1(i, 8) = objrst.Fields(11)
report1(i, 9) = objrst.Fields(12)
report1(i, 10) = objrst.Fields(13)
report1(i, 11) = objrst.Fields(14)
report1(i, 12) = objrst.Fields(15)
report1(i, 13) = objrst.Fields(16)
report1(i, 14) = objrst.Fields(17)
report1(i, 15) = objrst.Fields(18)
report1(i, 16) = objrst.Fields(19) i = i + 1
objrst.MoveNext
Wend
sql = "select * from dianchangshijian where time='" & Format$(DTPicker1.Value) & "'"
Set objrst1 = ExecuteSQL(sql, msgtext)
While Not objrst1.EOF
ReDim Preserve report2(0 To objrst1.RecordCount - 1, 0 To 10)
report2(i, 0) = objrst1.Fields(2)
report2(i, 1) = objrst1.Fields(3)
report2(i, 2) = objrst1.Fields(4)
report2(i, 3) = objrst1.Fields(5)
report2(i, 4) = objrst1.Fields(6)
report2(i, 5) = objrst1.Fields(7)
report2(i, 6) = objrst1.Fields(8)
report2(i, 7) = objrst1.Fields(9)
report2(i, 8) = objrst1.Fields(10)
report2(i, 9) = objrst1.Fields(11)
report2(i, 10) = objrst1.Fields(12)
i = i + 1
objrst1.MoveNext
Wend
If Dir(App.Path & "\Temp\excel.bz") = "" Then '判断EXCEL是否打开
Set xlApp = CreateObject("Excel.Application") '创建EXCEL应用类
xlApp.Visible = True '设置EXCEL可见
Set xlBook = xlApp.Workbooks.Open(App.Path & "\temp\agc2.xls") '打开EXCEL工作簿
Set xlsheet = xlBook.Worksheets(1) '打开EXCEL工作表
xlsheet.Activate '激活工作表
xlsheet.Cells(1, 2) = Format$(DTPicker1.Value, "yyyy年mm月dd日")
xlsheet.Cells(1, 4) = WeekdayName(Weekday(DTPicker1.Value))
For i = 0 To objrst.RecordCount - 1 xlsheet.Cells(i + 3, 1) = report1(i, 0)
xlsheet.Cells(i + 3, 2) = report1(i, 1)
xlsheet.Cells(i + 3, 3) = report1(i, 2)
xlsheet.Cells(i + 3, 8) = report1(i, 3)
For j = 4 To 16
xlsheet.Cells(i + 22, j - 3) = report1(i, j)
Next j
Next i
For i = 0 To objrst1.RecordCount - 1
For j = 0 To 10
xlsheet.Cells(i + 8, j + 1) = report2(i, j)
Next j
Next i
'给单元格1行驶列赋值
xlBook.RunAutoMacros (xlAutoOpen) '运行EXCEL中的启动宏
Else
MsgBox ("EXCEL已打开")
End If
End Sub
相似问题,日期和星期都能往EXECL里填,但是记录集中的东西却存不到EXECL里头,如果把其中一个记录集如(objrst1)注释掉,另一个记录集里的数据则能存里头,是不是不能用两个数组往一个EXECL里导数据呢!
intRow = objrst.recordcount
intCol = objrst.Fields.count
xlsheet.Range(Cell(1, 1), Cell(intRow, intCol)).CopyFromRecordset