Access導出Execl
VB+Access做的系統，
為方便查閱與打印，
需導出為execl表格，
help。
環境：Access表是Access 2000；
[解决办法]
窗体代码
Private Sub outcstinf_Click() '导出资料为XLS Dim i As Integer '行 Dim j As Integer '列 Call openXLT("Custom.xlt")'Custom.xlt是一个与要导出的数据表格式相同的excel模板文件 Call OpenConn sql = "select * from 要导出的表 ORDER BY 用于排序的字段(一般是编号) ASC" rs.Open sql, cn, 1, 1 If rs.RecordCount < 1 Then MsgBox "数据库中没有记录！": Exit Sub Else i = 2'从xls的第二行开始写入(模板中第一行已作为字段名保留) Do While Not rs.EOF For j = 0 To rs.Fields.Count - 1 '循环数据表的0至最后一列 xlsheet.Cells(i, j + 1) = rs.Fields(j) '写入(数据表字段索引是从0开始,而xls的列示从1开始, 因此要j+1) Next rs.MoveNext i = i + 1 '跳到i+1行继续写入 Loop End If Call CloseConn Call closeXLT sql = ""End Sub
[解决办法]
ado连接和关闭数据库的代码
'打开库Public Sub OpenConn() Set cn = New ADODB.Connection Set rs = New ADODB.Recordset cn.CursorLocation = adUseClient cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\数据库所在文件夹的名称\数据库名称;Jet OLEDB:DataBase Password=数据库密码;Persist Security Info=False;"End Sub'关闭库Public Sub CloseConn() If rs.State <> sckClosed Then rs.Close Set rs = Nothing If cn.State <> sckClosed Then cn.Close Set cn = NothingEnd Sub
[解决办法]
'在vb中，将ACESS数据导入Excel中Private Sub Command1_Click() On Error GoTo myErr Dim Excel_app As Object, AccessFile As String Dim iExcelFile As String, iExcelPath As String, iSQL As String iExcelFile = "tmp.xls" AccessFile = "F:\tk\tk.mdb" iSQL = "select * from tbUser" iExcelPath = IIf(Len(App.Path) > 3, App.Path & "\" & iExcelFile, App.Path & iExcelFile) Set Excel_app = CreateObject("Excel.Application") 'Excel_app.Visible = True Excel_app.WorkBooks.Add Excel_app.Sheets("sheet1").Select Screen.MousePointer = vbHourglass With Excel_app.ActiveSheet.QueryTables.Add(Connection:=Array( _ "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=" & _ AccessFile), Destination:=Excel_app.Range("A1")) .CommandType = 3 .CommandText = Array(iSQL) .Refresh End With Excel_app.DisplayAlerts = False Excel_app.ActiveWorkBook.SaveAs FileName:=iExcelPath Excel_app.DisplayAlerts = True Excel_app.Quit Set Excel_app = Nothing Screen.MousePointer = vbDefault MsgBox "导出了成功", , "导出成功"Exit SubmyErr: If Err.Number = 429 Then Screen.MousePointer = vbDefault MsgBox "请先安装EXCEL!", , "导出错误" Exit Sub End If Excel_app.DisplayAlerts = False Excel_app.Quit Excel_app.DisplayAlerts = True Set Excel_app = Nothing Me.MousePointer = vbDefault MsgBox " 导出数据到 Excel 时出错! ", , "导出错误" End Sub
[解决办法]
给你一个简单的
Private Sub sub_ExpToExcel() Dim xlapp As Excel.Application Dim xlbook As Excel.Workbook Dim xlsheet As Excel.Worksheet Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset cn.CursorLocation = adUseClient cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db1.mdb;Mode=ReadWrite;Persist Security Info=False;Jet OLEDB:Database Password=" rs.Open "select * from mytable", cn, adOpenStatic, adLockOptimistic rs.RecordCount If rs.RecordCount > 0 Then Set xlapp = CreateObject("Excel.Application") Set xlbook = xlapp.Workbooks.Add Set xlsheet = xlbook.Worksheets(1) xlsheet.Range("a1").CopyFromRecordset rs xlsheet.SaveAs strFileName End If rs.Close Set rs = Nothing cn.Close Set cn = NothingEnd Sub
[解决办法]
探讨通过VB向EXCEL传输数据的方法
一、引言
VB向Excel传输数据的几种常用方法进行总结，并分析每种方法的优缺点及适用环境
二、概述
通常我们向Excel传输数据使用自动化。自动化提供了最好的便利性并且能在运行时对单元格进行格式化和其他各项设置。对于自动化可以使用几种不同的途径来传输数据：
l 一个单元格一个单元格的传输数据；
l 传输数组中的数据到一组单元格；
l 使用CopyFromRecordset方法传输ADO recordset到一组单元格；
l 通过在Excel worksheet中定义一个Querytable，这个querytable通过ODBC或者OLEDB连接到数据源；
l 先将数据拷贝到剪切板然后将剪贴板内容粘贴到Excel worksheet中；
还有几种方法向Excel中传输数据，比如运行服务端应用程序，可以不通过客户端进行批量数据处理。以下几种方法是不通过自动化进行数据传输的方法：
l 传输数据到有tab符或者逗号分割的文本文件中，Execl可以分析这个文件将它放到worksheet中；
l 通过ADO（ActiveX Data Object）将数据传输到worksheet中；
l 通过DDE（Dynamic Data Exchange）传输数据到worksheet中。
三、几种常用方法的详细说明。
1、 使用自动化一个单元格一个单元格传输数据
可以使用自动化，一次可以将数据传输到worksheet一个单元格中，举例如下：
dim oExcel As object
Dim oBook as object
dim oSheet as object
'在EXCEL中创建一个新的worksheet
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.workbooks.Add
'在新的workbook中向第一个worksheet的单元格添加数据
Set oSheet = oBook.Worksheets(1)
oSheet.Range("A1").Value ="张三"
oSheet.Range("B1").Value="男"
oSheet.Range("A1:B1").font.Bold=true
oSheet.Range("A2").Value="李四"
oSheet.Range("B2").value="女"
'保存workbook并退出Excel
oBook.SaveAs "C:\book1.xls"
oExcel.quit
如果要传输的数据量不是很大，一个单元一个单元传输数据是比较好的方法。可以方便的将数据放到workbook任何地方并且还可以在运行时格式化单元格。但是，当传输到workbook中数据量很大则不建议使用这种方法，因为在运行结果中的每一个range对象都需要创建接口，所以这种方法速度较慢。此外，微软的windows95和windows98接口有64k的限制，当接口需求超过64k时，自动化服务器（Excel）可能就会停止响应或者提示内存不足错误。所以如果需要向Excel中传输的数据不是很大时建议使用这种方式。
2、 使用自动化传输一个数组数据到wooksheet的range
可以一次将数组中的数据传输到weeksheet中包括多个单元格组成的range中。举例如下：
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
'在Excel中创建一个新的wookbook
Set oExcel = CreateObject("excel.Application")
Set oBook=oExcel.WorkBooks.Add
'创建一个3列、100行的数组
Dim DataArray(1 to 100,1 to 3 ) as variant
Dim r as integer
For r = 1 to 100
DataArray(r,1) = "ORD" & Format(r,"0000")
DAtaArray(r,2)= Rnd() * 1000
DataArray(r,3) = DataArray(r,2) * 7
Next
'在worksheet的第一行加上标题
Set oSheet = oBook.Worksheets(1)
oSheet,Range("A1:C1").valve = Array("定单号","数量","税金")
'将数组中的数据传输到worksheet中，数据的开始位置是A2
oSheet.Range("A2").Resize(100,3).valve=DataArray
'保存workbook，退出Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit
如果通过数组而不是一个单元一个单元的传输大量的数据，则会发现性能会显著提高，将数据传输到300个worksheet的单元格中的语句如下：
osheet.range("A2").Resize(100,3)Value=DataArray
这条语句需要两个接口（由Range方法生成的Range对象需要一个接口，由resize方法生成的range对象也需要一个接口）。如果要是通过一个单元格一个单元格的传输数据则需要为range对象生成300个接口。不论什么情况，作为开发者要尽量使用批量数据传输以减少接口数量，从而提高应用系统的性能。
3、使用自动化传输ADO的recordset到wooksheet的range
Excel2000及以后版本引入了CopyFromRecordset方法，从而允许开发者将ADO的recordset传输到worksheet的range中。下面的代码演示了如何使用CopyFromRecordset将示例数据库Northwind 中orders表的数据传输到Excel中：
'生成一个包含Orders表所有记录的 Recordset
Dim sNWind As String
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
sNWind = "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sNWind & ";"
conn.CursorLocation = adUseClient
Set rs = conn.Execute("Orders", , adCmdTable)
'在Excel中创建新的workbook
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
'向 Excel中传输数据
oSheet.Range("A1").CopyFromRecordset rs
'保存并退出Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit
'关闭连接
rs.Close
conn.Close
4、使用剪切板
windows的剪切板也可以作为向worksheet传输数据的一种机制。可以拷贝被TAB符分割的列、回车符分割的行的字符串到worksheet的单元格中。VB通过clipboard对象传输数据到Excel的代码如下：
'拷贝字符串到剪切板
Dim sData As String
sData = "FirstName" & vbTab & "LastName" & vbTab & "Birthdate" & vbCr _
& "Bill" & vbTab & "Brown" & vbTab & "2/5/85" & vbCr _
& "Joe" & vbTab & "Thomas" & vbTab & "1/1/91"
Clipboard.Clear
Clipboard.SetText sData
' 在 Excel中创建一个workbook
Dim oExcel As Object
Dim oBook As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
'粘贴数据
oBook.Worksheets(1).Range("A1").Select
oBook.Worksheets(1).Paste
'保存并退出Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit
5、生成一个带有分割符的文本文件
Excel可以打开内容被TAB符或逗号分割的文件，并且能正确的分解数据到相应的单元格中。可以利用这一点来传输大量数据，如果可能的话再使用少量的自动化。这种方式对SERVER/CLIENT应用程序是一个很好的选择，因为文本文件在服务端生成，然后在客户端使用自动化一次打开。
下面代码演示如何使用ADO recordset生成被逗号分割的文本文件。
'生成一个包含Orders表所有记录的 Recordset
Dim sNWind As String
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim sData As String
sNWind = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sNWind & ";"
conn.CursorLocation = adUseClient
Set rs = conn.Execute("Orders", , adCmdTable)
'将 recordset保存成以 tab分割符分割的文本文件
sData = rs.GetString(adClipString, , vbTab, vbCr, vbNullString)
Open "C:\Test.txt" For Output As #1
Print #1, sData
Close #1
'关闭连接
rs.Close
conn.Close
'在Excel中打开一个新的文本文件
Shell "C:\Program Files\Microsoft Office\Office\Excel.exe " & _
Chr(34) & "C:\Test.txt" & Chr(34), vbMaximizedFocus
如果文本文件具有.csv扩展名，Excel直接打开文件而不会出现导入向导，并且是假设文件是被逗号分割的。与此类似，如果文件具有.txt扩展名，Excel自动以TAB符为文件的分割符来分解文件。
在前一个例子，Excel通过shell语句启动，命名文件使用带参数的命令行语句。没有使用自动化，然而，如果愿意的话，可以少量的使用自动化打开文件和存储文件。举例如下：
'创建一个新的Excel实例
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
'打开文本文件
Set oBook = oExcel.Workbooks.Open("C:\Test.txt")
'保存文件并退出
oBook.SaveAs "C:\Book1.xls", xlWorkbookNormal
oExcel.Quit
6、通过ADO向worksheet中传输数据
使用 microsoft Jet OLE DB Provider可以向已经存在的Excel workbook中的表添加数据，这里所谓的表就是已经定义了名字的range。这个定义了名字的range的第一行必须包含标题（或者叫字段名），接下来各行包含的是记录。以下演示了通过如何在workbook中创建名为MyTalbe的表：
⑴、创建一个新的workbook
⑵、在sheet1的单元格A1:B1增加如下内容
A1：FirstName B1：LastName
⑶、格式化B1为右对齐；
⑷、选中A1：B1
⑸、在插入菜单选择Names然后选择Define，输入名称MyTable然后点ok。
⑹、将新的workbook存为c:\book1.xls，退出Excel
使用ADO向MyTable增加记录的代码如下：
' 创建一个连接Book1.xls的connection对象
Dim conn As New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Book1.xls;Extended Properties=Excel 8.0;"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
" values ('Bill', 'Brown')"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
" values ('Joe', 'Thomas')"
conn.Close
果使用这种方式向表中添加记录，workbook中的格式是自动维护的，在上面的例子中，新增加记录的B列自动被格式化为右对齐。每一条增加的行自动借用上一行的格式。
注意，如果一条记录被增加到worksheet的单元格中，则在该单元格中的数据被覆盖掉，换句话说就是新增加的记录不是插入一行，而是覆盖原先的行。
四、小结
本文通过对VB向Excel中传输数据的几种常用方法分析和总结，提出若干建议。借鉴这些建议一定会给开发者的开发带来便利。