针对你这种情况,比较快的是将Recordset 直接导出为Excel,实际是CopyFromRecordset这个方法的应用 Dim Myexcel As New Excel.Application Dim Mybook As New Excel.Workbook Dim Mysheet As New Excel.Worksheet dim i as long Set Mybook = Myexcel.Workbooks.Add '添加一个新的BOOK Set Mysheet = Mybook.Worksheets.Add '添加一个新的SHEET Myexcel.Visible = True Rs.Open SQL, Cn With Mysheet .Range("A2").CopyFromRecordset Rs '这里就是具体的应用 .PageSetup.PrintGridlines = True .PrintPreview End With Rs.close Set Rs = Nothing
Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet Dim i As Integer Dim j As Integer Dim k As IntegerSet xlApp = New Excel.Application Set xlBook = xlApp.Workbooks.Add Set xlSheet = xlBook.Worksheets(1) xlSheet.Columns.AutoFit Me.MousePointer = 11 For k = 0 To DataGrid1.Columns.Count - 1 xlSheet.Cells(1, k + 1) = DataGrid1.Columns(k).Caption Next If DataGrid1.ApproxCount = 0 Then MsgBox "请查询后再导出!" Me.MousePointer = 0 Exit Sub Else DataGrid1.Scroll 0, -DataGrid1.FirstRow DataGrid1.Row = 0 For i = 0 To DataGrid1.ApproxCount - 1 xlSheet.Columns("A").AutoFit xlSheet.Columns("b").AutoFit xlSheet.Columns("c").AutoFit xlSheet.Columns("d").AutoFit xlSheet.Columns("e").AutoFit xlSheet.Columns("f").AutoFit xlSheet.Columns("g").AutoFit xlSheet.Columns("h").AutoFit xlSheet.Columns("i").AutoFit For j = 0 To DataGrid1.Columns.Count - 1 DataGrid1.Col = j xlSheet.Cells(i + 2, j + 1) = DataGrid1.Text Next If i < DataGrid1.ApproxCount - 1 Then DataGrid1.Row = DataGrid1.Row + 1 End If Next Me.MousePointer = 0 MsgBox "导出成功!", vbOKOnly + vbInformation, "提示" xlApp.Visible = True Set xlApp = Nothing Set xlBook = Nothing Set xlSheet = Nothing End If
可以把 Excel 当作 Jet Engine 的外部数据库,用 SQL 语句从 Access 或 SQL 数据库直接导入记录集。
Dim Mybook As New Excel.Workbook
Dim Mysheet As New Excel.Worksheet
dim i as long Set Mybook = Myexcel.Workbooks.Add '添加一个新的BOOK
Set Mysheet = Mybook.Worksheets.Add '添加一个新的SHEET
Myexcel.Visible = True
Rs.Open SQL, Cn
With Mysheet
.Range("A2").CopyFromRecordset Rs '这里就是具体的应用
.PageSetup.PrintGridlines = True
.PrintPreview
End With
Rs.close
Set Rs = Nothing
xlsSheet .QueryTables.Add(Rst, xlSheet.Range("a1")) 'Rst为记录集
前者可用程序设置excel隐藏。
后者可按要的字段和条件重新查询并导出(也可试着用rst.Filter筛选后再导出)。
有没有办法直接把表格控件中的东西直接复制出来或者在原来的记录集里面再select一下?
重新查询一边的话有点麻烦
另一种办法,对recordset断开记录集,然后删除掉code PY字段。
再一种办法,将记录集内容转换成二维数组,然后赋给excel。
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim i As Integer
Dim j As Integer
Dim k As IntegerSet xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
xlSheet.Columns.AutoFit
Me.MousePointer = 11
For k = 0 To DataGrid1.Columns.Count - 1
xlSheet.Cells(1, k + 1) = DataGrid1.Columns(k).Caption
Next
If DataGrid1.ApproxCount = 0 Then
MsgBox "请查询后再导出!"
Me.MousePointer = 0
Exit Sub
Else
DataGrid1.Scroll 0, -DataGrid1.FirstRow
DataGrid1.Row = 0
For i = 0 To DataGrid1.ApproxCount - 1
xlSheet.Columns("A").AutoFit
xlSheet.Columns("b").AutoFit
xlSheet.Columns("c").AutoFit
xlSheet.Columns("d").AutoFit
xlSheet.Columns("e").AutoFit
xlSheet.Columns("f").AutoFit
xlSheet.Columns("g").AutoFit
xlSheet.Columns("h").AutoFit
xlSheet.Columns("i").AutoFit
For j = 0 To DataGrid1.Columns.Count - 1
DataGrid1.Col = j
xlSheet.Cells(i + 2, j + 1) = DataGrid1.Text
Next
If i < DataGrid1.ApproxCount - 1 Then
DataGrid1.Row = DataGrid1.Row + 1
End If
Next
Me.MousePointer = 0
MsgBox "导出成功!", vbOKOnly + vbInformation, "提示"
xlApp.Visible = True
Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = Nothing
End If
xlsSheet .Columns("E:G").Delete '删除E-G列
xlsSheet .Columns("E").Delete '删除E列