3种方法: 1.使用FileSystemObject创建文件,用文本方式打开,然后用chr(9)分隔数据,回车分隔行。然后保存为xls文件就可以。这样的速度比较快,但是功能有限。这样的文件其实只是tab分隔的文本文件,能用Excel打开。2.使用odbc,使用"driver={Microsoft Excel Driver (*.xls)};DBQ=C:\temp.xls"这样的连接来进行Excel数据操作,速度同样也很快,但是功能也有限,只能操作数据。3.打开Excel进程,用代码来控制Excel文件。 set xlApp=CreateObject("Excel.Application") set xlWorkbook=xlApp.Workbooks.Add set xlSheet=xlWorkbook.Worksheets(1) xlSheet.Cells(1,1).Value="aaa" xlSheet.Cells("A2").Value="bbb" xlApp.Visible=true '如果要让用户看到Excel,就加上这一句 xlWorkbook.SaveAs "C:\temp.xls" xlApp.Quit set xlApp=nothing set xlWorkbook=nothing set xlSheet=nothing 这种方法速度比较慢,但是优点是可以完成任意的Excel操作。
Sub DataToExcel(byval AdodcForAttendanceCheck as recordset ) '***************************************** '功能说明:将筛选所得数据导出到Excel中 'AdodcForAttendanceCheck 是连接acess 表得到的记录集 Dim lngRow As Long Dim lngCol As Long Dim lngN As Long
Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet
Dim TempRecordset As Recordset Set TempRecordset = AdodcForAttendanceCheck.Clone If TempRecordset.RecordCount = 0 Then Exit Sub '导出数据为空,退出 End If
Set xlApp = CreateObject("Excel.Application") xlApp.WindowState = xlMaximized xlApp.Visible = False Set xlBook = xlApp.Workbooks.Add Set xlSheet = xlBook.Worksheets(1)
''在第一行中写入字段名 j = 1 For i = 1 To lngCol temp = TempRecordset.Fields(i - 1).name xlSheet.Cells(1, j) = temp j = j + 1 Next
'写入数据 TempRecordset.MoveFirst Do While Not TempRecordset.EOF For i = 2 To lngRow k = 1 For j = 1 To lngCol FieldsName = TempRecordset.Fields(j - 1).name temp = TempRecordset(j - 1) xlSheet.Cells(i, k) = temp k = k + 1 Next TempRecordset.MoveNext Next Loop
老兄:请看下面,我正在使用呢,还很新鲜呢: With CRPT1 .ReportFileName = App.Path & "\AAA.rpt" .PrintFileType = crptExcel50 .PrintFileName = "C:\AAA.XLS" .Destination = crptToFile .Action = 1 End With
Dim Irow, Icol As Integer Dim Irowcount, Icolcount As Integer Dim Fieldlen1 As Integer '存字段长度值 Dim Fieldlen() 'Dim xlApp As Excel.Application 'Dim xlBook As Excel.Workbook 'Dim xlSheet As Excel.Worksheet Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Add Set xlSheet = xlBook.Worksheets(1) SSPanel2.Visible = True probar.Value = 0 'On Error GoTo excle With Rs_Temp .MoveLast If .RecordCount < 1 Then MsgBox ("没有记录!") Exit Sub End If '记录总数 Irowcount = .RecordCount '字段总数 Icolcount = .Fields.Count ReDim Fieldlen(Icolcount) .MoveFirst For Irow = 1 To Irowcount + 1
For Icol = 1 To Icolcount Select Case Irow '在Excel中的第一行加标题 Case 1 xlSheet.Cells(Irow, Icol).Value = .Fields(Icol - 1).Name '将数组FIELDLEN()存为第一条记录的字段长 Case 2 If IsNull(.Fields(Icol - 1)) = True Then Fieldlen(Icol) = LenB(.Fields(Icol - 1).Name) '如果字段值为NULL,则将数组Filelen(Icol)的值设为标题名的宽度 Else Fieldlen(Icol) = LenB(.Fields(Icol - 1)) End If xlSheet.Columns(Icol).ColumnWidth = Fieldlen(Icol) 'Excel列宽等于字段长 xlSheet.Cells(Irow, Icol).Value = .Fields(Icol - 1) '向Excel的CellS中写入字段值 Case Else If IsNull(.Fields(Icol - 1)) Then Fieldlen(Icol) = LenB(.Fields(Icol - 1).Name) Else Fieldlen1 = LenB(.Fields(Icol - 1)) End If If Fieldlen(Icol) < Fieldlen1 Then xlSheet.Columns(Icol).ColumnWidth = IIf(Fieldlen1 > 255, 255, Fieldlen1) '表格列宽等于较长字段长 Fieldlen(Icol) = IIf(Fieldlen1 > 255, 255, Fieldlen1) '数组Fieldlen(Icol)中存放最大字段长度值 Else xlSheet.Columns(Icol).ColumnWidth = Fieldlen(Icol) End If xlSheet.Cells(Irow, Icol).Value = .Fields(Icol - 1) End Select Next If Irow > 2 Then If Not .EOF Then .MoveNext End If
If Not .EOF Then If Irow < Irowcount Then probar.Value = probar.Value + 1 End If End If
Next '网格线 With xlSheet .Range(.Cells(1, 1), .Cells(1, Icol - 1)).Font.Name = "黑体" '设标题为黑体字 .Range(.Cells(1, 1), .Cells(1, Icol - 1)).Font.Bold = True '标题字体加粗 .Range(.Cells(1, 1), .Cells(Irow, Icol - 1)).Borders.LineStyle = xlContinuous '设表格边框样式 End With '*!* 页眉、填报单位、报表时间、单位 With xlSheet.PageSetup .LeftHeader = "" & Chr(10) & "&""楷体_GB2312,常规""&10公司名称:" ' & Gsmc .CenterHeader = "&""楷体_GB2312,常规""业务数据综合查询表&""宋体,常规""" & Chr(10) & "&""楷体_GB2312,常规""&10日 期:" .RightHeader = "" & Chr(10) & "&""楷体_GB2312,常规""&10单位:" .LeftFooter = "&""楷体_GB2312,常规""&10制表人:" .CenterFooter = "&""楷体_GB2312,常规""&10制表日期:" .RightFooter = "&""楷体_GB2312,常规""&10第&P页 共&N页" End With '显示表格 Dim ExclFileName As String ExclFileName = App.path & "\业务数据综合查询表.xls" If Dir(ExclFileName) <> "" Then Kill ExclFileName End If xlSheet.SaveAs (ExclFileName) SSPanel2.Visible = False xlApp.Application.Visible = True '交还控制给Excel 'xlSheet.PrintPreview 'xlApp.Quit End With 'excle: ' MsgBox ("您没有安装 Excle2000,请先安装 Excel2000 !")
如果想用程序来实现,又该怎么做呢.
我最终想加一个按钮实现报表内容直接转换到excel表中去,请问这该怎么做呢?
1.使用FileSystemObject创建文件,用文本方式打开,然后用chr(9)分隔数据,回车分隔行。然后保存为xls文件就可以。这样的速度比较快,但是功能有限。这样的文件其实只是tab分隔的文本文件,能用Excel打开。2.使用odbc,使用"driver={Microsoft Excel Driver (*.xls)};DBQ=C:\temp.xls"这样的连接来进行Excel数据操作,速度同样也很快,但是功能也有限,只能操作数据。3.打开Excel进程,用代码来控制Excel文件。
set xlApp=CreateObject("Excel.Application")
set xlWorkbook=xlApp.Workbooks.Add
set xlSheet=xlWorkbook.Worksheets(1)
xlSheet.Cells(1,1).Value="aaa"
xlSheet.Cells("A2").Value="bbb"
xlApp.Visible=true '如果要让用户看到Excel,就加上这一句
xlWorkbook.SaveAs "C:\temp.xls"
xlApp.Quit
set xlApp=nothing
set xlWorkbook=nothing
set xlSheet=nothing
这种方法速度比较慢,但是优点是可以完成任意的Excel操作。
'*****************************************
'功能说明:将筛选所得数据导出到Excel中
'AdodcForAttendanceCheck 是连接acess 表得到的记录集
Dim lngRow As Long
Dim lngCol As Long
Dim lngN As Long
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim TempRecordset As Recordset
Set TempRecordset = AdodcForAttendanceCheck.Clone
If TempRecordset.RecordCount = 0 Then
Exit Sub '导出数据为空,退出
End If
Set xlApp = CreateObject("Excel.Application")
xlApp.WindowState = xlMaximized
xlApp.Visible = False
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
lngRow = TempRecordset.RecordCount + 1 '行数(Excel中要多一行标题)
lngCol = TempRecordset.Fields.count '列数
''在第一行中写入字段名
j = 1
For i = 1 To lngCol
temp = TempRecordset.Fields(i - 1).name
xlSheet.Cells(1, j) = temp
j = j + 1
Next
'写入数据
TempRecordset.MoveFirst
Do While Not TempRecordset.EOF
For i = 2 To lngRow
k = 1
For j = 1 To lngCol
FieldsName = TempRecordset.Fields(j - 1).name
temp = TempRecordset(j - 1)
xlSheet.Cells(i, k) = temp
k = k + 1
Next
TempRecordset.MoveNext
Next
Loop
'退出Excel应用程序
'nReuslt = MsgBox("要保存吗?", vbInformation + vbYesNo, "保存提示")
xlApp.Quit
Set xlApp = Nothing
End Sub
With CRPT1
.ReportFileName = App.Path & "\AAA.rpt"
.PrintFileType = crptExcel50
.PrintFileName = "C:\AAA.XLS"
.Destination = crptToFile
.Action = 1
End With
Dim Irowcount, Icolcount As Integer
Dim Fieldlen1 As Integer
'存字段长度值
Dim Fieldlen()
'Dim xlApp As Excel.Application
'Dim xlBook As Excel.Workbook
'Dim xlSheet As Excel.Worksheet Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1) SSPanel2.Visible = True
probar.Value = 0 'On Error GoTo excle
With Rs_Temp
.MoveLast If .RecordCount < 1 Then
MsgBox ("没有记录!")
Exit Sub
End If '记录总数
Irowcount = .RecordCount
'字段总数
Icolcount = .Fields.Count ReDim Fieldlen(Icolcount)
.MoveFirst For Irow = 1 To Irowcount + 1
For Icol = 1 To Icolcount
Select Case Irow
'在Excel中的第一行加标题
Case 1
xlSheet.Cells(Irow, Icol).Value = .Fields(Icol - 1).Name
'将数组FIELDLEN()存为第一条记录的字段长
Case 2
If IsNull(.Fields(Icol - 1)) = True Then
Fieldlen(Icol) = LenB(.Fields(Icol - 1).Name)
'如果字段值为NULL,则将数组Filelen(Icol)的值设为标题名的宽度
Else
Fieldlen(Icol) = LenB(.Fields(Icol - 1))
End If xlSheet.Columns(Icol).ColumnWidth = Fieldlen(Icol)
'Excel列宽等于字段长
xlSheet.Cells(Irow, Icol).Value = .Fields(Icol - 1)
'向Excel的CellS中写入字段值
Case Else
If IsNull(.Fields(Icol - 1)) Then
Fieldlen(Icol) = LenB(.Fields(Icol - 1).Name)
Else
Fieldlen1 = LenB(.Fields(Icol - 1))
End If If Fieldlen(Icol) < Fieldlen1 Then
xlSheet.Columns(Icol).ColumnWidth = IIf(Fieldlen1 > 255, 255, Fieldlen1)
'表格列宽等于较长字段长
Fieldlen(Icol) = IIf(Fieldlen1 > 255, 255, Fieldlen1)
'数组Fieldlen(Icol)中存放最大字段长度值
Else
xlSheet.Columns(Icol).ColumnWidth = Fieldlen(Icol)
End If xlSheet.Cells(Irow, Icol).Value = .Fields(Icol - 1)
End Select
Next If Irow > 2 Then
If Not .EOF Then .MoveNext
End If
If Not .EOF Then
If Irow < Irowcount Then
probar.Value = probar.Value + 1
End If
End If
Next '网格线
With xlSheet
.Range(.Cells(1, 1), .Cells(1, Icol - 1)).Font.Name = "黑体"
'设标题为黑体字
.Range(.Cells(1, 1), .Cells(1, Icol - 1)).Font.Bold = True
'标题字体加粗
.Range(.Cells(1, 1), .Cells(Irow, Icol - 1)).Borders.LineStyle = xlContinuous
'设表格边框样式
End With '*!* 页眉、填报单位、报表时间、单位
With xlSheet.PageSetup
.LeftHeader = "" & Chr(10) & "&""楷体_GB2312,常规""&10公司名称:" ' & Gsmc
.CenterHeader = "&""楷体_GB2312,常规""业务数据综合查询表&""宋体,常规""" & Chr(10) & "&""楷体_GB2312,常规""&10日 期:"
.RightHeader = "" & Chr(10) & "&""楷体_GB2312,常规""&10单位:"
.LeftFooter = "&""楷体_GB2312,常规""&10制表人:"
.CenterFooter = "&""楷体_GB2312,常规""&10制表日期:"
.RightFooter = "&""楷体_GB2312,常规""&10第&P页 共&N页"
End With '显示表格
Dim ExclFileName As String
ExclFileName = App.path & "\业务数据综合查询表.xls"
If Dir(ExclFileName) <> "" Then
Kill ExclFileName
End If
xlSheet.SaveAs (ExclFileName)
SSPanel2.Visible = False
xlApp.Application.Visible = True
'交还控制给Excel
'xlSheet.PrintPreview
'xlApp.Quit
End With
'excle:
' MsgBox ("您没有安装 Excle2000,请先安装 Excel2000 !")