将数据在[Excel]和[任何数据源]之间导入导出_可临时保存查询结果

由于SQL查询结果也是一个表，所以：可以逐行（记录）将查询结果写入一个准备好的工作表中。

下面介绍3种方法，一次将查询结果放到指定位置

 

用CopyFromRecordset方法

Range.CopyFromRecordset方法调用格式：

              expression.CopyFromRecordset(Data, MaxRows, MaxColumns)

示例:

Sub 使用CopyFromRecordset方法指定存储位置()
    '在输出表的左上角目标单元格中用cells对象调用copyFromRecordset方法
    ActiveWorkbook.Save
    
    '首先进行查询
    connstr = "driver={microsoft excel driver (*.xls)};dbq=" & ActiveWorkbook.FullName & ";"
    Set conn = CreateObject("adodb.connection")
    conn.Open connstr
    
    cmdstr = "select a.班级,a.姓名 from [sheet1$] a order by a.班级"
    
    Set rs = CreateObject("adodb.recordset")
    
    rs.Open cmdstr, conn, 3, 1, 1
    
    Sheets.Add
    With ActiveSheet
        .Cells(1, 1) = "班级"
        .Cells(1, 2) = "姓名"
        
        .Cells(2, 1).CopyFromRecordset rs
        '注意缺点:
        '第一 日期类型字段显示时会有问题
         '第二 处理非Excel数据库的二进制类型数据会失灵
         '第三 不会复制标题行,
    End With
    conn.Close
    
End Sub

 

 

CopyFromRecordset方法的缺点:

1. 处理[日期类型]字段时,显示的是[数字形式],此时必须手动调整单元格格式。
2. 如果不是Excel的数据库，遇到二进制数据（如图形类型）此方法也会失灵，解决办法是使用自定义方法
3. 此方法不会显示字段名称（即标题行），使用之前，需要手动建立标题行；但同样，使用此方法也不需要有标题行。

 

用Select查询的into子句——指定存储位置

 

这是一种“查询后在写入”的动作

示例：

Sub 使用select查询时增加into子句()
    'select_into方法指定存储位置,需要使用command对象,因为要写入操作
    Dim conn As ADODB.Connection
    Dim cmd As ADODB.Command
    
    ActiveWorkbook.Save
    
    connstr = "driver={microsoft excel driver (*.xls)};dbq=" & ActiveWorkbook.FullName & ";readonly=false"
    Set conn = CreateObject("adodb.connection")
    conn.Open connstr
    
    
    'cmdstr = "select * into [Excel 8.0;DATABASE=c:\result.xls;].[sheet_result$] from [sheet5$]"
    '不能运行:错误提示表[sheet4$]不是有效的名称
    
    '第一种正确写法
    '*  cmdstr = "select * into [Excel 8.0;DATABASE=c:\result.xls;].[sheet_result] from [sheet5$]"
    '成功!!!
    '可以使用 SELECT INTO 语句将 Jet 可以读取的任何数据源中的数据复制到任何数据目标，
    '随时创建新的表（在 Excel 中为新工作表）。
    '【特别注意】工作表名称用作目标时，不要使用美元符号语法，例如 [Sheet1$]。
    '【特别注意】目标工作簿可以存在，也可以不存在；但是，【目标工作表必须尚未存在。】
    '【其中】[Excel 8.0;]是"类型参数";注意后面的分号不可删除

    '第二种正确写法
    'cmdstr = "select * into [sheet1] in '' [excel 8.0;database=c:\r1.xls] from [sheet1$]"
    '说明:其中的两个空单引号''是用来指定类型参数的(Excel 8.0;)部分
    '当使用此种语法是,类型参数包含在括号中(所以这里是空单引号)
    
    '第三种正确写法-是第二种写法的替代语法,将类型参数excel 8.0放在后面
    cmdstr = "select * into [sheet4] in 'c:\r1.xls' 'excel 8.0;' from [sheet5$]"
    '注意:此种语法中,类型参数(excel 8.0;)被单独列在目标文件路径之后
    '而且不需要用括号了(但要求有单引号)
    
    
    '【注意】select into 的新工作簿是没有任何工作表的，所以你也可以指定新工作表是sheet1,sheet2,sheet3等
    
    
    '错误的
    'cmdstr = "select * into openrowset('microsoft.Jet.OLEDB.4.0','excel 8.0;HDR=yes;database=c:\result.xls;','select * from [sheet1$]) from [sheet5$]"
    
    
    Set cmd = CreateObject("adodb.command")
    Set cmd.activeconnection = conn
    cmd.CommandText = cmdstr
    cmd.Execute
    
    conn.Close
    
End Sub

 

 

注意事项：

• 由于这是“查询后写入”操作，所有不能用ADODB.Recordset，得使用ADODB.Command
• 目标工作簿不必事先存在；当目标工作表必须不存在。
• 此种方法可以跟任何的数据源中的数据进行【数据交换】（只要使用正确的ConnectionStrings），随时创建新的表。
• 将工作表作为目标，要求不要使用美元符号语法$。
• into子句有三种写法:
• 用.（点访问符）的select...into语法，如：into [Excel 8.0;DATABASE=c:\result.xls;].[sheet_result]
• select...into...in语法，如：into [sheet1] in '' [excel 8.0;database=c:\r1.xls]
• select...into...in的替代语法，如：into [sheet4] in 'c:\r1.xls' 'excel 8.0;'

 

使用Insert语句的Into子句，将查询结果附加到一个存储位置

 

可以使用 INSERT INTO ...IN 语句将任何 Jet 可以读取的数据源中的数据附加到任何数据目标中

目标工作簿和目标工作表都必须存在。

由于您现在引用的是现有工作表，当您将工作表名称用作目标时，必须使用标准的美元符号语法，

列标题必须已经存在；也就是说，该语句只能用于附加到现有的表。

示例：

'用insert语句的into子句 为多个查询结果指定相同的存储位置(附加)
Sub 附加查询结果到同一位置()
    '要求,输出位置必须事先存在
    
    '建立输出位置
    '由于 使用insert...into方法,必须使输出文件事先存在;所以要先就建工作簿
    
    Application.DisplayAlerts = False
    Workbooks.Add '增加新工作簿,要用"工作簿集合"对象
    '设置标题行
    With ActiveWorkbook.ActiveSheet
        .Cells(1, 1).Value = "班级"
        .Cells(1, 2).Value = "姓名"
        .Cells(1, 3).Value = "考号"
        .Cells(1, 4).Value = "语文"
        .Cells(1, 5).Value = "代数"
        .Cells(1, 6).Value = "英语"
        
    End With
    '保存
    ActiveWorkbook.SaveAs Filename:="c:\r2.xls", FileFormat:=xlExcel8
    '这里注意要指定表格式为xlExcel8.0 ,否则不能插入(错误提示:文件格式有问题)
    
    
    ActiveWorkbook.Close
    Application.DisplayAlerts = True

    
    ActiveWorkbook.Save
    
    connstr = "driver={microsoft excel driver (*.xls)};dbq=" & ActiveWorkbook.FullName & ";readonly=false"
    Set conn = CreateObject("adodb.connection")
    conn.Open connstr
    
    
    Set cmd = CreateObject("adodb.command")
    Set cmd.activeconnection = conn
    
    '【注意】into子句可以使用以下三种写法
    
    cmdstr = "insert into [excel 8.0;database=c:\r2.xls].[sheet1$] " & _
        "select a.班级,a.姓名,a.考号,a.语文,a.代数,a.英语 from [sheet1$] a where a.班级='101'"
    cmd.CommandText = cmdstr
    cmd.Execute
    
    
    cmdstr = "insert into [sheet1$] in '' [Excel 8.0;database=c:\r2.xls] " & _
        "select a.班级,a.姓名,a.考号,a.语文,a.代数,a.英语 from [sheet1$] a where a.班级='102'"
    cmd.CommandText = cmdstr
    cmd.Execute
    
    cmdstr = "insert into [sheet1$] in 'c:\r2.xls' 'Excel 8.0;' " & _
        "select a.班级,a.姓名,a.考号,a.语文,a.代数,a.英语 from [sheet1$] a where a.班级='105'"
    cmd.CommandText = cmdstr
    cmd.Execute
    
    '如果出错提示"目标表格式问题",应该在SaveAs 中设置fileFormat格式为xlExcel8
    
    conn.Close
    
End Sub

 

 

注意事项：

1. 目标工作簿和目标工作表都必须存在。
2. 列标题必须已经存在；也就是说，该语句只能用于附加到现有的表。
3. 列标题必须与Insert语句中{Select子句的字段列表}一致，否则提示“字段类型错误”。
4. 由于您现在引用的是现有工作表，当您将工作表名称用作目标时，必须使用标准的美元符号语法，
5. 可以附加任何的数据源到Excel，只要connectionString正确
6. Into...in语法支持3种方式，同上
7. 【特别注意】此种方式对于目标工作簿的格式有要求（必须使xlExcel8)格式，否则用insert...into附加是出错“工作表格式错误”。

 

附注：

对于Excel，常见的数据库提供程序包括：

1. Provider=Microsoft.Jet.OLEDB.4.0
2. Provider=Microsoft.ACE.OLEDB.12.0(ACE 12.0是最新的Access数据库访问引擎)
3. Driver={Microsoft Excel Driver (*.xls)};(这是Microsoft Excel ODBC Driver)
4. .NET Framework Data Provider for OLE DB等等