如何从Excel访问单个工作表中导出2个查询 [英] How do I export 2 queries to a single worksheet in excel from access

问题描述

  DoCmd.TransferSpreadsheet acExport， acSpreadsheetTypeExcel9，qryName，test1.xls，True 
  

现在我有另一个查询我想将此查询中的数据添加到同一个工作表。我如何做到这一点？

解决方案

对于我的第一个查询，我使用

  DoCmd.TransferSpreadsheet acExport，acSpreadsheetTypeExcel12Xml，qryNameFirst，test1.xlsx，True，MyWorksheetName
  

对于第二个查询，我将其放在记录集中

  Dim rstName As Recordset 
设置rstName = CurrentDb.OpenRecordset（qryNameSecond）
  

然后我只是复制这个记录集使用CopyFromRecordset到工作表。

Dim objApp As Object，objMyWorkbook As Object，objMySheet As Object，objMyRange As Object

设置objApp = CreateObject（Excel.Application）
设置objMyWorkbook = objApp.Workbooks.Open（test1.xlsx）
设置objMySheet = objMyWorkbook.Worksheets（MyWorksheetName）
设置objMyRange = objMySheet.Cells（objApp.ActiveSheet.UsedRange.Rows.Count + 2，1）

带objMyRange
rstName.MoveFirst'回滚到fir st记录
.Clear
.CopyFromRecordset rstName
结束


objApp.ActiveSheet.UsedRange.Rows.Count将返回上一次使用的行号。我添加了+2，因为我想在两个查询之间留出一个空行。

添加我做了一个表演。我用500.000条记录测试了这个方法。该表包含500k行，第一个查询包含250k行，第二个查询（与OpenRecordSet）包含250k行。生成excel文件大约需要10秒钟，并在E6600（2,40 Ghz），4GB RAM机器上显示数据，具有access / excel 2010。

编辑：

另一种完成相同功能的方式是使用TransferSpreadsheet 2次。

  DoCmd.TransferSpreadsheet acExport，acSpreadsheetTypeExcel12Xml，qryNameFirst，test1.xlsx，True，MyWorksheetName
 DoCmd.TransferSpreadsheet acExport，acSpreadsheetTypeExcel12Xml，qryNameSecond，test1.xlsx，True MyWorksheetName2
  

这将在工作簿中创建2个工作表，然后将一个工作表的数据复制到其他。我认为性能将是一样但不确定，我会坚持OpenRecordSet。

I'm using TransferSpreadsheet to export a query from access to excel and it works fine.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryName", "test1.xls", True

Now I've got another query and I want to add the data from this query to the same worksheet. How can I do this?

解决方案

For my first query I use

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryNameFirst", "test1.xlsx", True, "MyWorksheetName"

For the second query I put it in a recordset

Dim rstName As Recordset
Set rstName = CurrentDb.OpenRecordset("qryNameSecond")

Then I just copy this recordset to the worksheet with CopyFromRecordset.

Dim objApp As Object, objMyWorkbook As Object, objMySheet As Object, objMyRange As Object

Set objApp = CreateObject("Excel.Application")
Set objMyWorkbook = objApp.Workbooks.Open("test1.xlsx")
Set objMySheet = objMyWorkbook.Worksheets("MyWorksheetName")
Set objMyRange = objMySheet.Cells(objApp.ActiveSheet.UsedRange.Rows.Count + 2, 1)

With objMyRange
 rstName.MoveFirst 'Rewind to the first record
 .Clear
 .CopyFromRecordset rstName
End With

objApp.ActiveSheet.UsedRange.Rows.Count will return the last used row number. I added + 2 because I want an empty row in between the two queries.

To add I did a performancetest. I tested this method with 500.000 records. The table containing 500k rows, the first query containing 250k rows, the second query (with the OpenRecordSet) containing 250k rows. It took about 10 seconds to generate the excel file sheet and display the data on a E6600 (2,40 Ghz), 4GB ram machine with access/excel 2010.

EDIT:

Another way to accomplish the same would be with using TransferSpreadsheet 2 times.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryNameFirst", "test1.xlsx", True, "MyWorksheetName"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryNameSecond", "test1.xlsx", True, "MyWorksheetName2"

This will create 2 Sheets in the workbook, then just copy the data of one worksheet to the other. I think the performance will be the same but not sure, I will stick with the OpenRecordSet.

这篇关于如何从Excel访问单个工作表中导出2个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！